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
716 Views

Similar Articles

[PageSpeed] 27

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:

Color box unavailable on Patterns tab in "format data series"
I have a user who is working with some charts and can not edit the color box (is grayed out) on the patterns tab in “format data series”. We feel it is a user setting or something because a different user can pull in the same spreadsheet and edit the color of a graph bar just fine… Has anyone encountered this? Any suggestions? ...

How do Load at Run Time DLL's work?
In the example of dll's loaded at runtime they use LoadLibrary and GetProcAddress. The LoadLibrary works for me, but the GetProcAddress doesn't. Well it did for a short time yesterday for no apparant reason, then it quit and wont work today. Anyway, when it was working, the parameter wasn't passed correctly to the dll function. So how does the compiler know what the parameter list is for the dll function? I don't have an h file included or anything to tell the calling program what the parameters can be. Here some some code from the dll In MEP.h class CMEPApp : public CWinApp...

how do I copy several lines of word text into one excel cell?
In a word doc I need to copy several lines of text and paste them into one excel cell. I can't seem to find any help telling me how to do this (each line pastes into a different cell) You can either double click the cell or press F2 key to get into edit mode for the cell, then the text pasted would all go into the cell. -Simon "jhh" wrote: > In a word doc I need to copy several lines of text and paste them into one > excel cell. I can't seem to find any help telling me how to do this (each > line pastes into a different cell) thank you SO much Simon CC - ...

text box filter by form
For some reason that I just can't explain, a text box on a form associated with a single primary key field behaves unexpectedly when I do a filter-by-form. Instead of listing all of the records from the source table as choices in combo box mode, I only see "Is Null" and "Is Not Null" as choices. I have an older form from another database that behaves as I hoped this one would, yet there seems to be no property differences between the two text boxes. Can anybody explain what I'm missing please? Having "Is Null" and "Is Not Null" as filt...

Combining two fields into one in a query for a report
The database I am working on is to keep track of parts that are either in their storage area (on a shelf) or on an assembly line. I have two fields, one called Shelf and every part has a shelf assigned to it. The other field is Line and if a part in on an assembly line, there is a number indicating that line, if the part is on it's shelf, the word STOCK appears in the line field. (Has to be this way for the rest of the database to work, so this is not up for question) Now, I need to creat a report to show where all parts are. If they are on a line the result needs to show the l...

Text to Columns 05-28-10
I have a column with the following data: A RUT 212874790014 Each cell has the word RUT, and the number changes, altough it always has 12 digits. I need to have the word in one cell and the number in another, so I use the text to column option. But when I finish doing this the result is: A B RUT 21,2875E+11 How can I avoid this, so as to keep displaying the whole original number in the cell? Thanks in advance. Regards, Emece.- After performing the text to columns, format the number column as 'number' zero decimal points. "Emece&quo...

text recognition difference b/w commercial and non-commercial?
I have OneNotes 2007 installed at work on Windows 7 and text recognition works. I have it installed at home (non-commercial) on Windows 7 and I do not get text recognition. Both are SP2. But they look different. Does text recognition not work in the non-commercial version? This is the only thing that make OneNote worthwhile as a tool for me. MarkC wrote: >I have OneNotes 2007 installed at work on Windows 7 and text > recognition works. I have it installed at home (non-commercial) on > Windows 7 and I do not get text recognition. Both are SP2. But they > look d...

Using Access 2003 Run-Time Version
I am trying to use the Access 2003 run-time and almost everything seems to be working just fine. I have noticed however that I seemed to have lost the ability to right click on a report and bring up the options of print/email.... When I right click using the run-time versionm nothing displays (as if the right button is disabled). When I right click using the regular mdb (full access 2003) all the options appear and I am able to print/email/etc at this time. My main question is is there any way to get the right click options back in a run-time version. Thanks That's correct: the run...

Tabs not visible for one user
I have a worksheet with 12 tabs that is stored on a shared drive, this is accessed by several users. One user is not able to see the tabs across the bottom. She is at a different location than all other users, so we can't physically see this on her PC. All other users can see the tabs when accessing on the shared drive, and when opening an emailed copy. We have had her maximize, etc. and still no tabs. Any suggestions on what I can tell her. I might be missing something simple. I have tried to recreate the problem on a local copy, but can not. Thanks Have you checked the obvious...

Combine multiple workbooks into one workbook
I would like to combine a number of workbooks inside a folder into one workbook and maybe have the worksheet name be the name of the file or something. The core issue is combining them. I have looked and I see how to reverse it but not this exact thing, please help. ...

converting to UNICODE, _TCHAR and TCHAR, writing text files
Hi, i'm converting a MFC application to support unicode. I have some (probably noob) questions: - What is the difference between the types _TCHAR and TCHAR ? I see some UNICODE applications use _TCHAR and others TCHAR. - I have to convert the way textfiles are written. These text-files are send to machines using parallel port, so they have to stay the same as before (when my application had no _UNICODE preprocessor definition). The commands that are used to write these files are fputs, fopen, etc. When changing it to support wide characters are the textfiles still the same? example (_U...

Setting Value Of One Cell Equal To Value Of Selected Cell
Without using VBA - If cell A1=red, A2=white and A3=blue, can I set C1 to be the value of the selected cell so that if A1 is selected, C1 will equal red, if A2 is selected, C1 will equal white, etc.? Thanks, Sheldon Potolsky Sheldon, In a word, no. No worksheet thing comes to mind that works as a function of the active cell. You might want to use some radio buttons or a list box (Forms Toobar or Control Toolbox) instead of cell selection. They'll let you click to make such choices, and you can use formulas that will change a cell value as you wish. -- Regards from Virginia Beach, ...

Macro to remove contents of cell and move all other contents up one row
I've very lillte or no experience of macros and got some very usefu help from a kind user of this forum a couple of days back and I'm bac looking for more help. Its a bit long winded but here goes. I need a macro which checks the contents of a couple of ranges o cells, starting with the first range of cells lets say it checks cell A4,B4,C4,D4 down to cells A20,B20,C20,D20. Now if for example cell A7,B7,C7 and D7 have values (note the formulas used in these cell means that if A7 has a value then B7,C7 and D7 will also have a valu if A7 is blank then B7,C7, and D7 will be blank) and no...

Excel Cell Format for Numberic Values
When I export data having 20 numeric characters, Excel will put in place a scientifc equation. The numbers are rounded off after 15 characters. Cannot get Excel to display all 20 numeric numbers. Any ideas on how to get Excel to read all 20 numbers in a cell? Hi not possible. Excel only supports 15 significant digits -- Regards Frank Kabel Frankfurt, Germany "Al" <Al@discussions.microsoft.com> schrieb im Newsbeitrag news:4324528D-BBAB-493C-A1B0-EC108BCB6571@microsoft.com... > When I export data having 20 numeric characters, Excel will put in place a > scientifc e...

In excel can you select certain cells which contain the same text
If I have several cells with the same text in them can I filter these out and select them. I know you can do this for formula etc but can it be done for text? try data>filter>autofilter -- Don Guillett SalesAid Software donaldb@281.com "ade" <ade@discussions.microsoft.com> wrote in message news:937AD9E1-668B-4E32-B194-29146DF0A60A@microsoft.com... > If I have several cells with the same text in them can I filter these out and > select them. I know you can do this for formula etc but can it be done for > text? ...

one column market word but i want this word i want right column
i want urangt ans you will need to give slighty more information if you would like some help. vaibhav wrote: >i want urangt ans -- Matt Lynn Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/201005/1 ...

automatically change text case on entry
I use the following code to automatically change the case of a range of cells to uppercase in Excel, which works fine. However I need some other cells in the same sheet to automatically change to Proper Case. Is there a way of adapting the following code to enable upper and proper case? Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Range("H1,M5,O11,F7")) Is Nothing Then With Target .Value = UCase(.Value) End With End If ws_exit: Application.En...

RPC/HTTP works BUT not from everywhere This is a strange one
Hello everyone. I am in a bit of a bind here and am under about a 12 hour timeline window. I have Exchange 2003 and Outlook 2003 Clients using RPC/HTTP in a singe server configuration. Remote staff and satelite offices are all using this and it works GREAT. HOWEVER, my office in Winnipeg, Man. CAN, does not work at all. In fact if I use a laptop from outside the office network and connect wirelessly through anyones Internet connection it does NOT work. If I take the same laptop back to BC with me or the CONGO for that matter it works. Very frustrating...anyone seen this before or ...

Date/Time Output Discrepancy
I've used two ways to get the {!Case.Created On} field but the outcomes were different. In 'E-mail To' action of Workflow Rule: the outcome was '07/21/2006 10:50 AM (GMT+08:00) Beijing, Chongqing, Hong Kong SAR, Urumqi' In the Email Template: the outcome was '07/21/2006 10:50 AM' How can I get a consistent format, and can it be presented in the other formats, e.g. UTC? Workflow by default will give you a fully formated date time string. To get the required result. you will have to create a small .net assembly that takes in the datatime parameter and retu...

Subtract time spent for lunch
I have a sheet for my time at work. I want to subtract time for lunch. Col B is "In" to work Col C is "Out" finished work. Everything is on 24 hr clock, no am or pm. I would like a col D "Lunch" in minutes or fractions of hour. B is 0800 arrive at work C is 1700 leave for the day. D is 0.50 for lunch E is 8.5 hours worked Again I want everything to be on 24 hour clock. I copied this formula from a template. =(C8-B8+(C8<B8))*24 The templates I found all had out/in for lunch. I just want one col with the time spent at lunch. M If you're going to use 0...

copy format and formulas of one report to a second report
I need to copy a report to create a second report, and am looking to know how to globally change formulas to a different table/query. The fields in the two tables are identical, the data results are different. Example of a Current formula: =Sum(IIf([Customer Sat Results database]![The service or information you received was appropriate and help]="Good",1,0)) New report formula would be: =Sum(IIf([Internet Customer Sat Results database Query1]![The service or information you received was appropriate and help]="Good",1,0)) there are about 96 formulas t...

conditional formating #11
Here is what I want it to happen: There is a number in cell A1 and a text in A2. When I change the number in A1 I want the "text" change in A2. How can I do this with conditional formatting? I know how to change the "format" of A2, but what I want is a change of the text in A2. um... why not just use an "IF" statement in A2...? On May 12, 10:55=A0am, "minimus" <mini...@live.co.uk> wrote: > Here is what I want it to happen: > > There is a number in cell A1 and a text in A2. When I change the number i= n > A1 I want the "tex...

email AWAYS from one account even I tell it to use another email
I'm using Outlook 2003 fully patched, ok I haven't patched the French grammer checker and the infopath (neither of which I use) on Win XP Home SP2 fully patched. Anyhow, all my emails going out are showing up in the sent box with an incorrect email address. When I Create a New Message, one of the first things I do is select which email account the email message is to go out from. I'm selecting my pop3 email account. (The check mark is beside the pop3 account!) I type in my message, press the send button, and the email shows up in my sent box with a hotmail account on ...

Converting text to uppercase
This is Excel 2000. Cell C2 in a worksheet called 'NOTES' contains '34 West Street, somewhere-in-England' =UPPER ( NOTES!C2 ) is supposed to produce ' 34 WEST STREET, SOMEWHERE-IN-ENGLAND' In fact it gives: #NAME? What is going wrong? Have you spelt UPPER( correctly? There is no space before the open bracket. Hope this helps. Pete On Apr 24, 3:53=A0pm, Alan Secker <a...@asandco.co.uk> wrote: > This is Excel 2000. Cell C2 in a worksheet called 'NOTES' > > contains '34 West Street, somewhere-in-England' > > =3DUPPER ( NOTES!...

Track Changes in 2007 deleting large portions of text
We have a document where 3 team members are making updates in revision marks. We are not updating at the same time. For some reason, huge chunks of text are getting deleted that were never actually deleted by any of us. It has happened to all 3 of us after we worked in the document. It is now the third or fourth time that I have had to go back and reject the changes for the sections deleted, and yes it does show my name or one of the other uses in the name of the person who made the changes. I can go in, reject all of the deleted sections, save the document, get out and get b...