Time Format to Text Output - A Tough One !

Hi

I run Excel 2K

I have a series of times that I download from the mainframe. (these are in a 
date format)

EXAMPLE
23/09/2009  6:07:00 AM
22/09/2009  9:22:00 PM
22/09/2009  7:40:00 PM

etc etc

I am in need of a formula that looks at these times, and based on their 
values, displays a particular piece of text. (in this case "DAY", "AFT", 
"NIGHT")

Example

Any time between the following:
7:20:00 AM to 3:19:00 PM should display the word DAY

Any time between the following:
3:20:00 PM to 11:19:00PM should display the word AFT

Any time between the following:
11:20:00 PM to 7:19:00 AM should display the word NIGHT

Assume the original time is in cell B8

Thanks

John
0
JohnCalder (178)
9/22/2009 11:00:01 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
703 Views

Similar Articles

[PageSpeed] 42

Hi

Further to my previous post I have tried the following formula.
It almost works, it displays the Day and the Aft ok but where the Night 
should be shows only a blank cell.

=IF(AND(TEXT(B8-INT(B8),"hh:mm:ss")>="07:20:00",TEXT(B8-INT(B8),"hh:mm:ss")<"15:20:00"),"Day",IF(AND(TEXT(B8-INT(B8),"hh:mm:ss")>="15:20:00",TEXT(B8-INT(B8),"hh:mm:ss")<"23:20:00"),"Aft",IF(AND(TEXT(B8-INT(B8),"hh:mm:ss")>="23:20:00",TEXT(B8-INT(B8),"hh:mm:ss")<"07:20:00"),"Night","")))


I hope this helps

Thanks

John




"John Calder" wrote:

> Hi
> 
> I run Excel 2K
> 
> I have a series of times that I download from the mainframe. (these are in a 
> date format)
> 
> EXAMPLE
> 23/09/2009  6:07:00 AM
> 22/09/2009  9:22:00 PM
> 22/09/2009  7:40:00 PM
> 
> etc etc
> 
> I am in need of a formula that looks at these times, and based on their 
> values, displays a particular piece of text. (in this case "DAY", "AFT", 
> "NIGHT")
> 
> Example
> 
> Any time between the following:
> 7:20:00 AM to 3:19:00 PM should display the word DAY
> 
> Any time between the following:
> 3:20:00 PM to 11:19:00PM should display the word AFT
> 
> Any time between the following:
> 11:20:00 PM to 7:19:00 AM should display the word NIGHT
> 
> Assume the original time is in cell B8
> 
> Thanks
> 
> John
0
JohnCalder (178)
9/23/2009 12:11:01 AM
"John Calder" <JohnCalder@discussions.microsoft.com> wrote:
>> I am in need of a formula that looks at these times, and based on their
>> values, displays a particular piece of text. (in this case "DAY", "AFT",
>> "NIGHT")

How is this different from the thread you started (and I thought I finished 
;->) on 8/17/2009 at 5:37 PM entitled "Time Formula"?

See
http://www.google.com/url?url=http://groups.google.com/g/c1c7ff04/t/8aa9667d4799b2b9/d/89bb027e352a9a78%3Fq%3Dgroup:microsoft.public.excel.newusers%2Binsubject:time%2Binsubject:formula%2389bb027e352a9a78&ei=BHS5SrGFIMnGlAfngbFo&sa=t&ct=res&cd=1&source=groups&usg=AFQjCNHp13R384qWcjicuzaM1oH3dlXmhA .

Was there something with the very different and more compact solution that I 
offered?


----- original message -----

"John Calder" <JohnCalder@discussions.microsoft.com> wrote in message 
news:6C83F435-B646-4F84-A541-485240B12CA8@microsoft.com...
> Hi
>
> Further to my previous post I have tried the following formula.
> It almost works, it displays the Day and the Aft ok but where the Night
> should be shows only a blank cell.
>
> =IF(AND(TEXT(B8-INT(B8),"hh:mm:ss")>="07:20:00",TEXT(B8-INT(B8),"hh:mm:ss")<"15:20:00"),"Day",IF(AND(TEXT(B8-INT(B8),"hh:mm:ss")>="15:20:00",TEXT(B8-INT(B8),"hh:mm:ss")<"23:20:00"),"Aft",IF(AND(TEXT(B8-INT(B8),"hh:mm:ss")>="23:20:00",TEXT(B8-INT(B8),"hh:mm:ss")<"07:20:00"),"Night","")))
>
>
> I hope this helps
>
> Thanks
>
> John
>
>
>
>
> "John Calder" wrote:
>
>> Hi
>>
>> I run Excel 2K
>>
>> I have a series of times that I download from the mainframe. (these are 
>> in a
>> date format)
>>
>> EXAMPLE
>> 23/09/2009  6:07:00 AM
>> 22/09/2009  9:22:00 PM
>> 22/09/2009  7:40:00 PM
>>
>> etc etc
>>
>> I am in need of a formula that looks at these times, and based on their
>> values, displays a particular piece of text. (in this case "DAY", "AFT",
>> "NIGHT")
>>
>> Example
>>
>> Any time between the following:
>> 7:20:00 AM to 3:19:00 PM should display the word DAY
>>
>> Any time between the following:
>> 3:20:00 PM to 11:19:00PM should display the word AFT
>>
>> Any time between the following:
>> 11:20:00 PM to 7:19:00 AM should display the word NIGHT
>>
>> Assume the original time is in cell B8
>>
>> Thanks
>>
>> John 

0
joeu2004 (766)
9/23/2009 1:10:40 AM
Try this.

=IF(A1="","",IF(AND(--TEXT(A1,"HH:MM:SS")>=TIME(7,20,0),(--TEXT(A1,"HH:MM:SS")<=TIME(15,19,59))),"DAY",IF(AND(--TEXT(A1,"HH:MM:SS")>=TIME(15,20,0),(--TEXT(A1,"HH:MM:SS")<=TIME(23,19,59))),"AFT","NIGHT")))

change the cell reference A1 to your desired cell.

If this post helps, Click Yes!

--------------------
(MS-Exl-Learner)
--------------------



"John Calder" wrote:

> Hi
> 
> I run Excel 2K
> 
> I have a series of times that I download from the mainframe. (these are in a 
> date format)
> 
> EXAMPLE
> 23/09/2009  6:07:00 AM
> 22/09/2009  9:22:00 PM
> 22/09/2009  7:40:00 PM
> 
> etc etc
> 
> I am in need of a formula that looks at these times, and based on their 
> values, displays a particular piece of text. (in this case "DAY", "AFT", 
> "NIGHT")
> 
> Example
> 
> Any time between the following:
> 7:20:00 AM to 3:19:00 PM should display the word DAY
> 
> Any time between the following:
> 3:20:00 PM to 11:19:00PM should display the word AFT
> 
> Any time between the following:
> 11:20:00 PM to 7:19:00 AM should display the word NIGHT
> 
> Assume the original time is in cell B8
> 
> Thanks
> 
> John
0
9/23/2009 7:28:01 AM
Joe

Thanks for your repsonse. The difference is that the earlier post was for a 
formula that looked at a 2 X 12 hr shift operation and the one I need now is 
for a 3 X 8 hr operation.

I hadnt worked out how to ammend the earlier one to suit the later one so I 
posted it again with the new criteria.

As a result from this groups help I now have it working.

Thanks you very much, it was much appreciated.

John



"JoeU2004" wrote:

> "John Calder" <JohnCalder@discussions.microsoft.com> wrote:
> >> I am in need of a formula that looks at these times, and based on their
> >> values, displays a particular piece of text. (in this case "DAY", "AFT",
> >> "NIGHT")
> 
> How is this different from the thread you started (and I thought I finished 
> ;->) on 8/17/2009 at 5:37 PM entitled "Time Formula"?
> 
> See
> http://www.google.com/url?url=http://groups.google.com/g/c1c7ff04/t/8aa9667d4799b2b9/d/89bb027e352a9a78%3Fq%3Dgroup:microsoft.public.excel.newusers%2Binsubject:time%2Binsubject:formula%2389bb027e352a9a78&ei=BHS5SrGFIMnGlAfngbFo&sa=t&ct=res&cd=1&source=groups&usg=AFQjCNHp13R384qWcjicuzaM1oH3dlXmhA .
> 
> Was there something with the very different and more compact solution that I 
> offered?
> 
> 
> ----- original message -----
> 
> "John Calder" <JohnCalder@discussions.microsoft.com> wrote in message 
> news:6C83F435-B646-4F84-A541-485240B12CA8@microsoft.com...
> > Hi
> >
> > Further to my previous post I have tried the following formula.
> > It almost works, it displays the Day and the Aft ok but where the Night
> > should be shows only a blank cell.
> >
> > =IF(AND(TEXT(B8-INT(B8),"hh:mm:ss")>="07:20:00",TEXT(B8-INT(B8),"hh:mm:ss")<"15:20:00"),"Day",IF(AND(TEXT(B8-INT(B8),"hh:mm:ss")>="15:20:00",TEXT(B8-INT(B8),"hh:mm:ss")<"23:20:00"),"Aft",IF(AND(TEXT(B8-INT(B8),"hh:mm:ss")>="23:20:00",TEXT(B8-INT(B8),"hh:mm:ss")<"07:20:00"),"Night","")))
> >
> >
> > I hope this helps
> >
> > Thanks
> >
> > John
> >
> >
> >
> >
> > "John Calder" wrote:
> >
> >> Hi
> >>
> >> I run Excel 2K
> >>
> >> I have a series of times that I download from the mainframe. (these are 
> >> in a
> >> date format)
> >>
> >> EXAMPLE
> >> 23/09/2009  6:07:00 AM
> >> 22/09/2009  9:22:00 PM
> >> 22/09/2009  7:40:00 PM
> >>
> >> etc etc
> >>
> >> I am in need of a formula that looks at these times, and based on their
> >> values, displays a particular piece of text. (in this case "DAY", "AFT",
> >> "NIGHT")
> >>
> >> Example
> >>
> >> Any time between the following:
> >> 7:20:00 AM to 3:19:00 PM should display the word DAY
> >>
> >> Any time between the following:
> >> 3:20:00 PM to 11:19:00PM should display the word AFT
> >>
> >> Any time between the following:
> >> 11:20:00 PM to 7:19:00 AM should display the word NIGHT
> >>
> >> Assume the original time is in cell B8
> >>
> >> Thanks
> >>
> >> John 
> 
> 
0
JohnCalder (178)
9/23/2009 11:22:02 PM
Reply:

Similar Artilces:

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

Two mailboxes for one user account after mailbox moved to a new server
After moving a user's mailbox from a Exchange 2003 to a new Exchange 2003 server, Outlook has listed two mailboxes with same name in the "All Mail Folders"area. The Outlook profile only has the user mailbox and no other mailboxes added to it. The user can send and recieve email just fine. The mailboxes seem to be clones of each other. New mail show up in the mailboxes at the sametime. How can I get Outlook to only show one mailbox? Any help would be appreciated, thanks, ...

office 2003 w/ windows 7 requires reacceptance ot T&C's each time
office 2003 w/ windows 7 requires reacceptance of T&C's each time i open work or excell or any office program. How do I get rid of this? Boot into Safe mode in Windows 7,open any Office app and then accept the terms -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "mouser830" <mouser830@discussions.microsoft.com> wrote in message news:E7B7602D-F5D8-4559-98EF-A2CCC1E2D6FD@microsoft.com... > office 2003 w/ windows 7 requires reacceptance of T&C's each time i op...

How can I wrap text across merged cells?
I'm using Excel 2000. I have a set of merged cells A5- E5. I have several lines of text in the cells and I want them to wrap across the merged cells and it is not working. All I get is the first line of text showing and the rest is cut off. It works if the cells are not merged, but I really need to do it in my merged cells. Is there a way to this other than manually resizing the height of the row? Instead of merging cells, have you tried the "Center Across Selection" option ? The appearence is just about identical to what you would get using merged cells, although I d...

How many Server-side rules can you put on one mailbox?
I am using Exchange Server 2003 and want to setup 52 rules on one of the mailboxes, is this possible? the hardcoded limit on space for rules is 32K...i doubt you'll be able to get 52 rules on one mailbox... -- Susan Conkey [MVP] "jmareel" <jmareel@discussions.microsoft.com> wrote in message news:6ADDDCD7-948C-415F-B194-1944C9936BC2@microsoft.com... > I am using Exchange Server 2003 and want to setup 52 rules on one of the > mailboxes, is this possible? http://support.microsoft.com/default.aspx?scid=kb;en-us;147298 -- Bharat Suneja MVP - Exchange www.zenpris...

Custom cell formatting
I need to create a custom format for a series of cells that will begin like this. I can't figure out what the code character is fora volitile potentially alpha character. Can anyone help me?? -Monica, Dallas 000000 000001 000002 .... 000009 00000A 00000B 000010 000011 ...... Monica something like this might work for you but you'll need to put all the leading digits in for the entry with the alpha character Regards Trevor "MDavison" <davison@fr.com> wrote in message news:#SD0tUzTEHA.1652@TK2MSFTNGP09.phx.gbl... > I need to create a custom format for a series ...

preview text disappears when email is opened
When someone responds to an my email, I can see what they have typed - until I open the message - then only my original message is viewable. The preview pane shows new message text when email arrives, hoever when the email is opened the preview text disappears - and I just see the earlier part of the message which I had sent. If I mark it as unread it does not return to the previous state. This seems to happen on email responses that people have received and answered from a webmail service not that they downloaded and opened in outlook on their PC OL'03 XP pro sp2 -- Respectfully,...

Auto formatting features: How do I align page numbers in publicati
I've got a problem with my publication. I can't align even page numbers to the left without automatic moving the odd numbers to the left as well? Can someone help me? Cissy99 wrote: > I've got a problem with my publication. I can't align even page numbers to > the left without automatic moving the odd numbers to the left as well? Can > someone help me? You need to create a two-page master rather than a one-page master. What version of Publisher are you running? -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

Keyboard shortcut for current date and time
Hi, Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me Ctrl+Shift+; inserts the current time with the date serial as 0 and not the current date's date serial. Presently I am adding the two (ie current date and time) to get the current date and time. Is there a keyboard shortcut that does this? Thanks in advance. Regards, Raj CTRL+; then SPACE then CTRL+SHIFT+; -or- =Now() -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware "Raj" <rspai9@gmail.com> wrote in message Hi, Ctrl+ ; inserts current dat...

how to turn off automatic format in Excel?
Hi Excel automatically change the first character in a cell to be uppercase. I just want lowercase. How can I turn off this function? Thanks Ngoc Hi Go to Tools / Autocorrect and uncheck Capitalize first letter of sentence. Andy. "ngoc" <linh@chello.no> wrote in message news:BOKNb.271$O41.819@amstwist00... > Hi > Excel automatically change the first character in a cell to be > uppercase. I just want lowercase. How can I turn off this function? > Thanks > Ngoc > ...

Formatting
I've always used MS Word as my email editor in Outlook. Is this possible in Live Mail. Nope. I'm afraid that's only possible using Outlook. In WLM you have to use the built in editor. Is there anything in particular that's lacking from the built in editor that you're looking for? Colin Brown WL MVP "jrchambe" <jrchambe@discussions.microsoft.com> wrote in message news:5EC21892-D39F-4219-AB0F-47BC14E1CD36@microsoft.com... > I've always used MS Word as my email editor in Outlook. Is this possible > in > Live Mail. "...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

Formatting Cells in Excel 97
Hi Guys, Sorry if I sound real stupid but is there anyway that we can control the column formatting in Excel 97 like let's say column A = GENERAL(6), column B = GENERAL(4), column C = TEXT(18) etc? Appreciate any form of advice, thanks!! I replied in the programming group. Are you seeking a programming answer? It's usually best to only post to one group, and include the remark "Please tell me if I should ask this in another group." On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <daryl.ho@tnt.com> wrote: >Hi Guys, > >Sorry if I sound real stupid b...

Only one line in Money invoice
I have been using Money 2007 Home & Business to create and print invoices. This program has run with no problems for some time. However, just recently, it will only allow one item to print on the invoice. I can enter more than one item, and the invoice entry will show the additional items, but when printed only one item will show up. I have tried backing up my Money file to a spare disk, deleting the program and the associated mny and mbf files from the computer, reinstalling the program and reloading the backup. The same fault immediately occurs. Strangely, I have now installed the sam...

time #11
I have set up a spreadsheet with time based on what our company truck drivers work. We work under a DOT rule of 70 hours / 8 days. That means that the driver can not work more than 70 hours in an 8 day period. The sheets I have (one for each month) goes back 7 days plus the current day and calculated how many hours a day the driver works and subtracts it from 70, leaving how many hours they can work the next day. If the calculation gets to 0 or above, the cell changes color to alert me that the driver's 70 hours are up and he/she has to have 34 hours off before he/she can be on duty ...

How do I combine two worksheets into one graph
I have two worksheets on an Excel spreadsheet. I would like to take both worksheets and place in one graph. Any suggestions? Make a chart with data from one sheet. Then copy the data on the other sheet, select the chart, and use Paste Special from the Edit menu to add the copied data as New Series. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ McPowerUser wrote: > I have two worksheets on an Excel spreadsheet. I would like to take both > worksheets and place in one graph. Any suggestions? ...

format a CD
Hi, how can I format a cd+rw re writeable disc aga ...

Reference: many to one
I have a form in which users enter information...I keep all controls locked until the user hits a command button to unlock particular bound controls. This works really well, but becomes cumbersome to code when new controls are added; e.g., adding the new control to the several event procedures attached to the form. Can anyone tell me how to refer to a group of controls once, e.g., give them a name; and then only have to refer to that name in the future? alex Alex I'm having trouble visualizing your situation. It sounds like you are describing a form that is being regularly modified....

Copying Publisher from One computer to another
I want to copy Publisher from my old computer to my new one, but if I can't seem to locate all the files I need to make it work on the new computer. What should I do? You can't, you have to install it from the CD. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Jasmin" <Jasmin@discussions.microsoft.com> wrote in message news:7EB558F1-525D-47BD-A54B-BC62C410D79E@microsoft.com... |I want to copy Publisher from my old computer to my new one, but if I can't | seem to locate all the files I need to make it work on the new computer. What | shoul...

Hyper link one column to another
I would like to have my columns hyper link one another. For example: Click on B2 would take you to N2, and vice-versa. Click on C2 would take you to O2... and so on through column j linked to V. Is this possible without having to make the link for each cell? This is a timesheet template and the columns b though J are the hours and N through V are the text comments for those hours. When I copy the template to a new sheet, there will be no data in any column. I would like to be able to enter an amount or formula (=end-start) for time spent and then be able to click on that cell and hyperlin...

why I see times new roman?
I have several html email messages composed in outlook express 6, I'm sure I highlited all text and set it to Arial 10. Then I drag the file to desktop, move it to vista windows mail draggin into inbox or other folder. The result is some parts of text shows arial 10 but some others Times new roman 10 or 12 what is annoying, because I use all arial 10, so I have to manually forward the email, highligh it again and set arial 10 and then, yes, I can see it properly. I checked the compose default font and everything is fine. Why is that? Is there any work around , helo...

Times Subtraction Whith Access
Hi I need to know how to subtract between two times variants and to get a time result (With Access). Like this: VarTime=[time1]-[time2] 10:05:40=18:05:50-08:00:10 Thanks Access really isn't intended to do things like that, but ?Format(#18:05:50# - #08:00:10#, "hh:nn:ss") 10:05:40 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) <bennyzamir@gmail.com> wrote in message news:1175704084.566444.282820@b75g2000hsg.googlegroups.com... > Hi > > I need to know how to subtract between two times variants and to get a > time res...

Provide space in text
Where text and numbers are at different locations in different cells what formula could be used to suit this type of information. I want to be able to put a space between the main body of text on the left and give a space between the text and the start of any numbers are in the cell. Example 1: A1 = Abelia Edward Goucher2 litre Should be: Abelia Edward Goucher 2 litre Example 2: A2 = Acer campestre Nanum180stem 6-8 Should be: Acer campestre Nanum 180stem 6-8 Thankyou if can be of help. Pat If you double clcik in the cell with the data then you can move th blink line to were you wan...

Halfway through my publisher document the text starts scrambling
I am in the middle of a Publisher document that I have to get out ASAP and the words are scrmbling all of a sudden; for instance instead of the it prints out eht. HELP!! Might try an updated printer driver. What version Publisher are you using? What printer? What version Windows? Canon has issues with Publisher 98 and Windows 98 How to troubleshoot text printing (inkjet printers) in Publisher 2000 http://support.microsoft.com/default.aspx?scid=kb;en-us;198258&Product=pub PUB2000: Troubleshooting Text Printing (Laser Printers) http://support.microsoft.com/default.aspx?scid=kb;en-us;198...

Repeat statements "x" number of times...
Hi All, I have the following code at the end of a long macro: Columns("D:D").Find(What:="total").Select ActiveCell.Offset(0, 4).Range("A1").Select ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])" ActiveCell.NumberFormat = "0.00" ActiveCell.Font.Bold = True With ActiveCell.Interior .ColorIndex = 6 End With What I am doing is when the word "total" appears, it will go to the cell 4 columns to the right, and then do a formula and format the cell. Question: How can I get this to repeat itself for eac...