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

Similar Articles

[PageSpeed] 35

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:

Visio formatting issues
I am working with visio flowcharts in visio professional 2k3 that were created in older versions of visio (possibly as far back as visio 97). When I oepn the flowchart to Edit, it changes the formatting of the fonts withing the flowchart shape symbols. I hate to have to re-create all of these flowcharts to keep my formatting correct, so any information would be greatly appreciated. Will also research this issue in other avenues while waiting a response. On Thu, 7 Feb 2008 14:12:03 -0800, Weberch <Weberch@discussions.microsoft.com> wrote: >I am working with visio flowcharts in v...

moving rules from one system to another
I have a lot of different rules, and alot of rules that I have exported during different times from different systems and supporting different PST files. I would like to import them into a new PST file. What is the: BEST way of doing this? The EASIEST way of doing this. Where can I find more info about importing rules? I have found that even if the folder exists, if the PST is not the same name nad in the same location, that some time (I have not figured out when, becuase sometimes it seems to work) it leaves the folder to be dragged to empty. Are there any 3rd party utils that will look at...

CDateTimeCtrl formatting
I have a CDateTimeCtrl on my dialog. It is valid to leave this blank (no entry) so I want to be able to allow this. I have added the FORMAT event handler for formatting it to the empty string if the underlying COleDateTime is 0.0, or the short date. I also added the FORMATQUERY handler to try to allow no input. This is causing problems when I try to select a date. I set the string and max length for the string to "", " / / ", etc to match the FORMAT, but when I do the GetWindowText(csEffDate), it always comes back as "0xee0xee..." Can the CDateTimeC...

Time zone
When I noticed the clock on my pc was wrong, I changed the time zone from Pacific to Eastern which is my correct U.S. time zone. After doing this, all of the items inserted as "all day" appointments in my monthly calendar are displayed with an image of a clock on either side of the appointment. The option to display clocks is turned off in the settings. Why is this happening and how can I prevent it from happening. Right now, I've had to put back the Pacific time zone and adjust to the pc not telling the right time. Is there any other solution? Thanks! ddlaz No ...

Automatically entering specific text.
I want to enter text into one cell automatically, if conditions in another cell are met. Example: If A2>0, then B2 should contain "x". Is this possible? thanks. In cell B2 put =IF(A2>0, "x", "") In article <3F7D98B6.9459D1CF@hmm.com>, atom <hmm@hmm.com> wrote: > I want to enter text into one cell automatically, if conditions in > another cell are met. > > Example: > If A2>0, then B2 should contain "x". > > Is this possible? > > thanks. > Wow. I know that was pretty simple, but I've be...

How to open 3 new windows for 3 links on one bookmark?
Does anyone have any suggestions on how to create a bookmark? so when I click this bookmark, which will open 3 new windows for 3 different links. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Hi Eric, Favorites>Add current tabs to Favorites. scriptlet: (untested, not recommended, results may depend on your Blockup poper and security zone settings) javascript:window.open(url1);window.open(url2);window.open(url3);void(0); There is an Addon from enhanceie.com that allows you to save a tabs list to the Desktop. Regards. "Eric&qu...

Send/Receive continues 1000s of times
When Outlook does its regular send/receive it ends up sending and receiving thousands of times. The task pane shows an ever increasing number of tasks that it says have been completed successfully. When all it actually is doing is rechecking the mail accounts I have, over and over again. Literally thousands of times. This interferes with anything that I want to do in Outlook while this is going on. It seems to have started after I downloaded either the latest version of itunes or the latest version of Adobe (or some other application like that). Help! And thanks. Per Roady - MVP, th...

One time code
In either an SDI or MDI application, if I want to include some functionality ( such as serial port configuration and control thread) that can only be run once per application instance, where should that code go? I looked at the App class but it doesn't handle user messages ( at least by default ). Should the main code and worker thread go in the MainFrm class in the OnCreate function? Help? Ron H. ----------------- www.Newsgroup-Binaries.com - *Completion*Retention*Speed* Access your favorite newsgroups from home or on the road ----------------- If it has to do with the "wi...

cpu time
Hi, Can you tell me about good tool for vs-6 that can tell me what functions in my application take the most cpu time? Thanks! In article <u$YZa3o9EHA.2680@TK2MSFTNGP09.phx.gbl>, dbg@012.net.il says... > Hi, > Can you tell me about good tool for vs-6 that can tell me what functions in > my application take the most cpu time? VS 6 includes a perfectly good profiler, which does exactly this. If you're working inside the environment, go to the link tab in the project settings, and click the "enable profiling" check box, then do a 'rebuild all' to gene...

Month Function on UK dates formats (dd/mm/yyy)
Hi, (Excel 2007) I'm in the US, created 2 tabs, 1 with US dates (formatted as DATE) and the other tab with UK dates (also formatted as DATE). File sent to UK to enter dates (dd/mm/yyyy) =MONTH works find pulling out months from US dates, but fails on some UK dates. FAILS on =month(13/1/2010) #VALUE! but ok with =month(12/1/2010). I suspect it is failing because it's "out of date range". My goal is to pull out the month from all UK dates. THANK for your help! Gene It may be for than just a formatting issue. For example in A1 enter: 1/13/2010 (US format...

Time Out a MsgBox
I have a file that runs a macro when it is open. This file may be opened manually or automatically by a macro from another file. If the file is opened manually I would like a MsgBox to prompt the user to end the macro before it gets into the body of the code. If the file is opened automatically I would like the MsgBox to automatically respond after a time delay to allow the macro to continue running the bulk of the code. Is there anyway to force a response to a MsgBox after a preset amount of time with no response from the user? Hi, How about this instead which closes...

Calculating time and pay in excel
Hi, i'm trying to make a spreadsheet that can calculate pay per minute depending on an average length of time worked. Its driving me crazy and any help would be greatly appreciated! Coloumn A is length of time, at the bottom is a total time, next to that is an average length of time worked and this is then multiplied by the amount appropriate for that average. What would be the best way to work all of this out accurately? Many thanks for any help you can give Mark -- wobbleman ------------------------------------------------------------------------ wobbleman's Profile: http://www.e...

run time error
When I attempt to import a text file (.txt) in to Access, an error occurs. Run-time error �3163�: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data When I click the Debug button this pops up: .Fields("TranType").Value = rs("MoveType").Value A different error occurs when save it to my desktop, and this is what happens.Run-time error �2185�: You can�t reference a property or method for a control unless the control has the focus. When I click the Debug button this pops up: MsgBox "Import File Not Found:&quo...

Start Time & End Time
I have a table of data for my Scheduled cutting list that is exported from another program into an Excel Spreadsheet and from this table I would like to be able to find the start and end time of the production runs automatically in a separate table. The table is set up like this Planned order Time Product Description Volume 12345 08:00:00 Product 1 3500 23435 11:32:00 Product 2 5680 14567 13:24:00 Product 3 10000 and so on. I can find the start time by us...

Am I behind the times?
I have just installed Exchange sp2 on my SBS 2008 boxes and all went well. One weird thing at the end of one of the installs it said Web Apps (I think) couldn't restore default configuration, but everything seems to be working just fine. So then I went to look for updates and I see a few that I must have missed. I use WSUS on the network. One is Exchange SP2 update rollup 3. Go for it or no? Second, Windows Server 2008 SP2. Yikes, when did that come out for SBS? I wonder why it never popped up before since it looks like it's been out for quite some time. Nonetheless, s...

Vlookup multiple items under one ID
Is there a way to use one function to lookup two values under the same unique ID. EX: Account Value 55 20 55 50 I was hoping to find a way to write a vlookup for this that would pull in the 20, and then also be able to pull in the 50 as well in the same function. Is this possible? Use data>filter>autofilter or look in the help index for MATCH to find the first and last match -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "klh84" <klh84@discussions.microsoft.com...

Transferring freight more than one time on a sales order
We frequently have partial shipments of sales orders and we leave the sales order open until everything has shipped instead of creating backorders. The system will allow you to have several tracking numbers for each order but you can only transfer the freight once. I would like to make the suggestion to be able to transfer freight more than one time. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, ...

Incorrect Time Stamps
Exchange seems to be stamping the wrong time on emails and events sporadically. All the times on servers are correct. Any thoughts? -- Craig Tuttle - Level I Exchange Tech UCNSB.org The time stamp can also be handled by the client. Which exact time field is being stamped wrong? Have you verified that the client has the correct time (i.e. Daylight savings time set correctly)? -- Ben Winzenz Exchange MVP MessageOne "Craig Tuttle" <CraigTuttle@discussions.microsoft.com> wrote in message news:02D51926-77DC-43EB-A5B5-7A00F4530323@microsoft.com... > Exchange seems to b...

empty text box
I am brand new to Access (a Paradox user) and am bewildered by the Text Box issue. I have a report in which I want to add a page footer that states the report is confidential. When I try to go to print view, it asks me to enter a parameter value. this is so frustrating, please help. Thanks! That would not be the problem. It is asking for parameters for the query that is the report's record source. -- Dave Hargis, Microsoft Access MVP "peabody" wrote: > I am brand new to Access (a Paradox user) and am bewildered by the Text Box > issue. I have a report in whi...

Text Box #2
Besides some other strange problems with Publisher 2007, now whenever I use the text box, there's a round circle with 4 small lines sticking out of the circle. I can't delete the 'symbol' without deleting the whole text box! Used to work correctly before I had to re-install. Used to just have a clean empty text box. Now there's this round symbol in it. Help! Are you using the trial version? Do a detect and repair from the help menu. Can you do a print screen and send me the result? mary-sauer at columbus.rr.com -- Mary Sauer MSFT MVP http://office.microsoft.com...

Placein the value of a combo Box column into a text box
This should be easy! Here is the code... Private Sub Combo18_AfterUpdate() Me![SchoolID] = Me!Combo18.Column(1) Me![Counselor Name] = Me!Combo18.Column(2) End Sub The problem is that it will not accept the first line: Me![SchoolID] = Me!Combo18.Column(1) If I move it around it is perfectly willing to place the counselor's name into the correct text box, but for the schoolID it says I have to save the record first. Anyone have any idea why??? I've checked and rechecked spelling, properties, etc! Thanks for any help you can give. On Thu, 3 Jan 2008 07:52:02 -0800, Betsy <B...

How chart time scale x-axis with another date serie
Hello all. I have this problem, I will need to create this chart that I don't know how to. Serie 1 is below which needs to be time scaled Date Count 1/1/2006 12 1/2/2006 3 1/3/2006 5 2/1/2006 18 2/3/2006 4 2/18/2006 9 4/1/2006 18 7/4/2006 38 Serie 2 - needs to plot the three dates in serie 1's time scaled X-Axis with symbols. 1/31/2006 2/18/2006 4/2/2006 I will look something like you have a line of count on a time-scaled chart, with 3 dates point in the x-axis. Please ...

Activity default due time
I need to stop the default coming up as 12:00am and set it to something like 9am so people get reminders at reasonable times. How can this be done? thanks Adrian Adrian, If you don't mind writing a few lines of code, look at "Setting a default time in a date field" in Client Side Scripting - More JavaScript Code - Part 3 (http://www.stunnware.com/crm2/topic.aspx?id=JS12). -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup Dialog: http://www.stunnware.com/crm2/?area=customLookup The ...

Template format changes
How do I change the default formatting for "$", "%" and"," when I open a NEW file? When I enter a large number and select comma formatting, it gives me two decimals, and I want none. I would like one decimal with percent format. Older versions of Excel allowed you to save a normal.xlt file with changes. When I add a personal template, Excel won't load it without me selecting it each time. Tom Excel never had a Normal.xlt, it had a BOOK.XLT Open a new workbook. Customize as you wish. File>Save As Type: scroll down to Excel Template(*.XLT) and s...

Time / Negative Time
Hi, If, for instance, in Excel you write 17:00 - 16:30, it will produc 0:30. However, 16:30 - 17:00 will produce #####, understandibly because you cannot have - ? o'clock. By formatting this to a number can produce -0.5. What if I still want to represent the decimal, but out of 60, so that can still get -0.30. I have tried messing about with fractions and still can't get it. Any ideas? Thanks, -- Message posted from http://www.ExcelForum.com On Thu, 13 May 2004 17:33:58 -0500, Kiaat <<Kiaat.167uek@excelforum-nospam.com>> wrote: >Hi, > >If, for insta...