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

Time Format to Text Output - A Tough One !
 

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

Time Format to Text Output - A Tough One !
 

"John Calder" 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:/...aM 1oH3dlXmhA .

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


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

"John Calder" wrote in message
...
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

Time Format to Text Output - A Tough One !
 

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

Time Format to Text Output - A Tough One !
 

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" 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:/...aM 1oH3dlXmhA .

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


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

"John Calder" wrote in message
...
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