Time Formula

Hi

I run Excel 2K

I download data from a mainframe. This data has a date & time format in it
(dd/mm/yyyy hh:mm:ss)

I work in a place that has a 3 shift cycle - day shift, afternoon shift,
night shift.

Day shift starts at 7:20am and ends at 15:29pm
Afternoon shift starts at 15:30pm and ends at 23:19pm
Night shift starts at 23:20pm amd ends at 7:19am

I need a formula that looks at the cell with the date/time in it and
displays the word DAY (for the time frame of day shift), AFTERNOON (for the
timeframe of afternoon shift) and NIGHT (for the timeframe of night shift)

In my previous job I used a formula for a different shift pattern which was
12 hour one which worked really well however the shift pattern that I need is
for is an 8 hour one, and I dont know how to edit the formula I used for the
12 hour pattern.

This is the formula I used for the 12 hour shift pattern:

=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")"19:00:00"),"Day","Night")


I hope I have managed to explain this ok.

Thanks

John

Assuming your dates/times are true Excel dates/times.

One way...

Create a lookup table like this:

...........J..........K
1....0:00.....Night
2....7:20.....Day
3..15:30.....Afternoon
4..23:20.....Night

Then...

A1 = some dd/mm/yyyy hh:mm:ss

=LOOKUP(MOD(A1,1),J$1:K$4)

--
Biff
Microsoft Excel MVP


"John Calder" wrote in message
...
Hi

I run Excel 2K

I download data from a mainframe. This data has a date & time format in it
(dd/mm/yyyy hh:mm:ss)

I work in a place that has a 3 shift cycle - day shift, afternoon shift,
night shift.

Day shift starts at 7:20am and ends at 15:29pm
Afternoon shift starts at 15:30pm and ends at 23:19pm
Night shift starts at 23:20pm amd ends at 7:19am

I need a formula that looks at the cell with the date/time in it and
displays the word DAY (for the time frame of day shift), AFTERNOON (for
the
timeframe of afternoon shift) and NIGHT (for the timeframe of night shift)

In my previous job I used a formula for a different shift pattern which
was
12 hour one which worked really well however the shift pattern that I need
is
for is an 8 hour one, and I dont know how to edit the formula I used for
the
12 hour pattern.

This is the formula I used for the 12 hour shift pattern:

=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")"19:00:00"),"Day","Night")


I hope I have managed to explain this ok.

Thanks

John

Hi

I quoted the wrong time format (24hr clock) I have edited the original
question and updated it to the correct format ( 12hr clock)

Cheers

"John Calder" wrote:

Hi

I run Excel 2K

I download data from a mainframe. This data has a date & time format in it
(dd/mm/yyyy hh:mm:ss)

I work in a place that has a 3 shift cycle - day shift, afternoon shift,
night shift.

Day shift starts at 7:20am and ends at 3:29pm
Afternoon shift starts at 3:30pm and ends at 11:19pm
Night shift starts at 11:20pm amd ends at 7:19am

I need a formula that looks at the cell with the date/time in it and
displays the word DAY (for the time frame of day shift), AFTERNOON (for the
timeframe of afternoon shift) and NIGHT (for the timeframe of night shift)

In my previous job I used a formula for a different shift pattern which was
12 hour one which worked really well however the shift pattern that I need is
for is an 8 hour one, and I dont know how to edit the formula I used for the
12 hour pattern. This wa based on a 24 hr clock time format.

This is the formula I used for the 12 hour shift pattern:

=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")"19:00:00"),"Day","Night")


I hope I have managed to explain this ok.

Thanks

John

"John Calder" wrote:
I need a formula that looks at the cell with the date/time in it and
displays the word DAY (for the time frame of day shift), AFTERNOON (for
the
timeframe of afternoon shift) and NIGHT (for the timeframe of night shift)

Try:

=LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
{"00:00:01","07:20:00","15:30:00","23:20:00"},
{"NIGHT","DAY","AFTERNOON","NIGHT"})


I quoted the wrong time format (24hr clock) I have edited the original
question and updated it to the correct format ( 12hr clock)

The format of the cell (A1) does not matter. The internal form (date/time
serial number) remains the same. The TEXT() expression above converts the
serial number to 24-hour time, which is needed for the proper lookup
comparison.


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

"John Calder" wrote in message
...
Hi

I quoted the wrong time format (24hr clock) I have edited the original
question and updated it to the correct format ( 12hr clock)

Cheers

"John Calder" wrote:

Hi

I run Excel 2K

I download data from a mainframe. This data has a date & time format in
it
(dd/mm/yyyy hh:mm:ss)

I work in a place that has a 3 shift cycle - day shift, afternoon shift,
night shift.

Day shift starts at 7:20am and ends at 3:29pm
Afternoon shift starts at 3:30pm and ends at 11:19pm
Night shift starts at 11:20pm amd ends at 7:19am

I need a formula that looks at the cell with the date/time in it and
displays the word DAY (for the time frame of day shift), AFTERNOON (for
the
timeframe of afternoon shift) and NIGHT (for the timeframe of night
shift)

In my previous job I used a formula for a different shift pattern which
was
12 hour one which worked really well however the shift pattern that I
need is
for is an 8 hour one, and I dont know how to edit the formula I used for
the
12 hour pattern. This wa based on a 24 hr clock time format.

This is the formula I used for the 12 hour shift pattern:

=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")"19:00:00"),"Day","Night")


I hope I have managed to explain this ok.

Thanks

John

I forgot to mention....

I wrote:
=LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
{"00:00:01","07:20:00","15:30:00","23:20:00"},
{"NIGHT","DAY","AFTERNOON","NIGHT"})

A1-INT(A1), as you wrote before, is another way to write MOD(A1,1). For
some numbers, the form A1-INT(A1) is more reliable. But MOD(A1,1) seems to
work for all date/times from 1/1/1900 12:00:01 AM through 12/31/2999
12:59:59 PM. Good enough?


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

"JoeU2004" wrote in message
...
"John Calder" wrote:
I need a formula that looks at the cell with the date/time in it and
displays the word DAY (for the time frame of day shift), AFTERNOON (for
the
timeframe of afternoon shift) and NIGHT (for the timeframe of night
shift)

Try:

=LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
{"00:00:01","07:20:00","15:30:00","23:20:00"},
{"NIGHT","DAY","AFTERNOON","NIGHT"})


I quoted the wrong time format (24hr clock) I have edited the original
question and updated it to the correct format ( 12hr clock)

The format of the cell (A1) does not matter. The internal form (date/time
serial number) remains the same. The TEXT() expression above converts the
serial number to 24-hour time, which is needed for the proper lookup
comparison.


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

"John Calder" wrote in message
...
Hi

I quoted the wrong time format (24hr clock) I have edited the original
question and updated it to the correct format ( 12hr clock)

Cheers

"John Calder" wrote:

Hi

I run Excel 2K

I download data from a mainframe. This data has a date & time format in
it
(dd/mm/yyyy hh:mm:ss)

I work in a place that has a 3 shift cycle - day shift, afternoon shift,
night shift.

Day shift starts at 7:20am and ends at 3:29pm
Afternoon shift starts at 3:30pm and ends at 11:19pm
Night shift starts at 11:20pm amd ends at 7:19am

I need a formula that looks at the cell with the date/time in it and
displays the word DAY (for the time frame of day shift), AFTERNOON (for
the
timeframe of afternoon shift) and NIGHT (for the timeframe of night
shift)

In my previous job I used a formula for a different shift pattern which
was
12 hour one which worked really well however the shift pattern that I
need is
for is an 8 hour one, and I dont know how to edit the formula I used for
the
12 hour pattern. This wa based on a 24 hr clock time format.

This is the formula I used for the 12 hour shift pattern:

=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")"19:00:00"),"Day","Night")


I hope I have managed to explain this ok.

Thanks

John

Critical typo....

I wrote:
=LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
{"00:00:01","07:20:00","15:30:00","23:20:00"},
{"NIGHT","DAY","AFTERNOON","NIGHT"})

=LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
{"00:00:00","07:20:00","15:30:00","23:20:00"},
{"NIGHT","DAY","AFTERNOON","NIGHT"})

"00:00:01" should be "00:00:00".


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

"JoeU2004" wrote in message
...
"John Calder" wrote:
I need a formula that looks at the cell with the date/time in it and
displays the word DAY (for the time frame of day shift), AFTERNOON (for
the
timeframe of afternoon shift) and NIGHT (for the timeframe of night
shift)

Try:

=LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
{"00:00:01","07:20:00","15:30:00","23:20:00"},
{"NIGHT","DAY","AFTERNOON","NIGHT"})


I quoted the wrong time format (24hr clock) I have edited the original
question and updated it to the correct format ( 12hr clock)

The format of the cell (A1) does not matter. The internal form (date/time
serial number) remains the same. The TEXT() expression above converts the
serial number to 24-hour time, which is needed for the proper lookup
comparison.


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

"John Calder" wrote in message
...
Hi

I quoted the wrong time format (24hr clock) I have edited the original
question and updated it to the correct format ( 12hr clock)

Cheers

"John Calder" wrote:

Hi

I run Excel 2K

I download data from a mainframe. This data has a date & time format in
it
(dd/mm/yyyy hh:mm:ss)

I work in a place that has a 3 shift cycle - day shift, afternoon shift,
night shift.

Day shift starts at 7:20am and ends at 3:29pm
Afternoon shift starts at 3:30pm and ends at 11:19pm
Night shift starts at 11:20pm amd ends at 7:19am

I need a formula that looks at the cell with the date/time in it and
displays the word DAY (for the time frame of day shift), AFTERNOON (for
the
timeframe of afternoon shift) and NIGHT (for the timeframe of night
shift)

In my previous job I used a formula for a different shift pattern which
was
12 hour one which worked really well however the shift pattern that I
need is
for is an 8 hour one, and I dont know how to edit the formula I used for
the
12 hour pattern. This wa based on a 24 hr clock time format.

This is the formula I used for the 12 hour shift pattern:

=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")"19:00:00"),"Day","Night")


I hope I have managed to explain this ok.

Thanks

John

Joe

Thanks a lot, looks great, I will let you know if I encounter any problems
with it....well done !!!!

"JoeU2004" wrote:

Critical typo....

I wrote:
=LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
{"00:00:01","07:20:00","15:30:00","23:20:00"},
{"NIGHT","DAY","AFTERNOON","NIGHT"})

=LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
{"00:00:00","07:20:00","15:30:00","23:20:00"},
{"NIGHT","DAY","AFTERNOON","NIGHT"})

"00:00:01" should be "00:00:00".


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

"JoeU2004" wrote in message
...
"John Calder" wrote:
I need a formula that looks at the cell with the date/time in it and
displays the word DAY (for the time frame of day shift), AFTERNOON (for
the
timeframe of afternoon shift) and NIGHT (for the timeframe of night
shift)

Try:

=LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
{"00:00:01","07:20:00","15:30:00","23:20:00"},
{"NIGHT","DAY","AFTERNOON","NIGHT"})


I quoted the wrong time format (24hr clock) I have edited the original
question and updated it to the correct format ( 12hr clock)

The format of the cell (A1) does not matter. The internal form (date/time
serial number) remains the same. The TEXT() expression above converts the
serial number to 24-hour time, which is needed for the proper lookup
comparison.


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

"John Calder" wrote in message
...
Hi

I quoted the wrong time format (24hr clock) I have edited the original
question and updated it to the correct format ( 12hr clock)

Cheers

"John Calder" wrote:

Hi

I run Excel 2K

I download data from a mainframe. This data has a date & time format in
it
(dd/mm/yyyy hh:mm:ss)

I work in a place that has a 3 shift cycle - day shift, afternoon shift,
night shift.

Day shift starts at 7:20am and ends at 3:29pm
Afternoon shift starts at 3:30pm and ends at 11:19pm
Night shift starts at 11:20pm amd ends at 7:19am

I need a formula that looks at the cell with the date/time in it and
displays the word DAY (for the time frame of day shift), AFTERNOON (for
the
timeframe of afternoon shift) and NIGHT (for the timeframe of night
shift)

In my previous job I used a formula for a different shift pattern which
was
12 hour one which worked really well however the shift pattern that I
need is
for is an 8 hour one, and I dont know how to edit the formula I used for
the
12 hour pattern. This wa based on a 24 hr clock time format.

This is the formula I used for the 12 hour shift pattern:

=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")"19:00:00"),"Day","Night")


I hope I have managed to explain this ok.

Thanks

John

"T. Valko" wrote:
=LOOKUP(MOD(A1,1),J$1:K$4)

Test when A1 contains 1/1/2009 7:20 AM and 1/1/2009 11:20 PM.

I think you'll find that they return the wrong shift, namely: NIGHT and
AFTERNOON.

The reason is floating point arithmetic aberrations. MOD(A1,1) yields
0.305555555554747,1158206462860107421875 and
0.972222222218988,46328258514404296875 in those case, whereas 7:20 and 23:20
(without dates) -- the equivalent of TIME(7,20,0) and TIME(23,20,0) -- are
0.305555555555555,52471602709374565165489912033081 0546875 and
0.972222222222222,20988641083749826066195964813232 421875.

The success or failure LOOKUP(MOD(A1,1),...) will depend on the date as well
as the time. For example, it works for 1/1/2009 3:30 PM, but only by
coincidence. In that case, MOD(A1,1) yields
0.645833333335758,6525380611419677734375, and 15:30 is
0.645833333333333,37034076748750521801412105560302 734375.

As usual, the solution is to "round" the time values. In this
case, --TEXT(MOD(A1,1),"hh:mm:ss") would exactly match the internal
representation of any time in the form "hh:mm:ss".

But for the OP's problem, we do not need --TEXT(). We can look up the
TEXT() result.


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

"T. Valko" wrote in message
...
Assuming your dates/times are true Excel dates/times.

One way...

Create a lookup table like this:

..........J..........K
1....0:00.....Night
2....7:20.....Day
3..15:30.....Afternoon
4..23:20.....Night

Then...

A1 = some dd/mm/yyyy hh:mm:ss

=LOOKUP(MOD(A1,1),J$1:K$4)

--
Biff
Microsoft Excel MVP


"John Calder" wrote in message
...
Hi

I run Excel 2K

I download data from a mainframe. This data has a date & time format in
it
(dd/mm/yyyy hh:mm:ss)

I work in a place that has a 3 shift cycle - day shift, afternoon shift,
night shift.

Day shift starts at 7:20am and ends at 15:29pm
Afternoon shift starts at 15:30pm and ends at 23:19pm
Night shift starts at 23:20pm amd ends at 7:19am

I need a formula that looks at the cell with the date/time in it and
displays the word DAY (for the time frame of day shift), AFTERNOON (for
the
timeframe of afternoon shift) and NIGHT (for the timeframe of night
shift)

In my previous job I used a formula for a different shift pattern which
was
12 hour one which worked really well however the shift pattern that I
need is
for is an 8 hour one, and I dont know how to edit the formula I used for
the
12 hour pattern.

This is the formula I used for the 12 hour shift pattern:

=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")"19:00:00"),"Day","Night")


I hope I have managed to explain this ok.

Thanks

John

I think you'll find that they return the wrong shift,
namely: NIGHT and AFTERNOON.

Yep, my fault for not testing with a date. I figured the date was irrelevant
so I tested with *times only*.

The reason is floating point arithmetic aberrations.

When it comes to rounding time values to account for this behavior I'm not
real sure about how many decimal places to set the rounding. Got a good
"rule of thumb" recommendation for that?

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"T. Valko" wrote:
=LOOKUP(MOD(A1,1),J$1:K$4)

Test when A1 contains 1/1/2009 7:20 AM and 1/1/2009 11:20 PM.

I think you'll find that they return the wrong shift, namely: NIGHT and
AFTERNOON.

The reason is floating point arithmetic aberrations. MOD(A1,1) yields
0.305555555554747,1158206462860107421875 and
0.972222222218988,46328258514404296875 in those case, whereas 7:20 and
23:20 (without dates) -- the equivalent of TIME(7,20,0) and
TIME(23,20,0) -- are
0.305555555555555,52471602709374565165489912033081 0546875 and
0.972222222222222,20988641083749826066195964813232 421875.

The success or failure LOOKUP(MOD(A1,1),...) will depend on the date as
well as the time. For example, it works for 1/1/2009 3:30 PM, but only by
coincidence. In that case, MOD(A1,1) yields
0.645833333335758,6525380611419677734375, and 15:30 is
0.645833333333333,37034076748750521801412105560302 734375.

As usual, the solution is to "round" the time values. In this
case, --TEXT(MOD(A1,1),"hh:mm:ss") would exactly match the internal
representation of any time in the form "hh:mm:ss".

But for the OP's problem, we do not need --TEXT(). We can look up the
TEXT() result.


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

"T. Valko" wrote in message
...
Assuming your dates/times are true Excel dates/times.

One way...

Create a lookup table like this:

..........J..........K
1....0:00.....Night
2....7:20.....Day
3..15:30.....Afternoon
4..23:20.....Night

Then...

A1 = some dd/mm/yyyy hh:mm:ss

=LOOKUP(MOD(A1,1),J$1:K$4)

--
Biff
Microsoft Excel MVP


"John Calder" wrote in message
...
Hi

I run Excel 2K

I download data from a mainframe. This data has a date & time format in
it
(dd/mm/yyyy hh:mm:ss)

I work in a place that has a 3 shift cycle - day shift, afternoon shift,
night shift.

Day shift starts at 7:20am and ends at 15:29pm
Afternoon shift starts at 15:30pm and ends at 23:19pm
Night shift starts at 23:20pm amd ends at 7:19am

I need a formula that looks at the cell with the date/time in it and
displays the word DAY (for the time frame of day shift), AFTERNOON (for
the
timeframe of afternoon shift) and NIGHT (for the timeframe of night
shift)

In my previous job I used a formula for a different shift pattern which
was
12 hour one which worked really well however the shift pattern that I
need is
for is an 8 hour one, and I dont know how to edit the formula I used for
the
12 hour pattern.

This is the formula I used for the 12 hour shift pattern:

=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")"19:00:00"),"Day","Night")


I hope I have managed to explain this ok.

Thanks

John

"T. Valko" wrote:
When it comes to rounding time values to account
for this behavior I'm not real sure about how many
decimal places to set the rounding. Got a good
"rule of thumb" recommendation for that?

Exactly! That's why I put "round" in quotes and suggested using --TEXT()
with an appropriate time format.

There are arithmetic methods. For example, ROUND(...*1440,0)/1440 would
seem to round to a minute; and ROUND(...*86400,0)/86400 would seem to round
to a second.

And those do seem to work well in the few cases that I've tried, even with a
date component. When I say "work", I mean that they result in the same
internal binary value as if the date/time were entered manually.

But I'm relunctant to use that approach because it uses floating point
division. It probably does "work", as I defined the term above. But I
would need to give it more thought.


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

"T. Valko" wrote in message
...
I think you'll find that they return the wrong shift,
namely: NIGHT and AFTERNOON.

Yep, my fault for not testing with a date. I figured the date was
irrelevant so I tested with *times only*.

The reason is floating point arithmetic aberrations.

When it comes to rounding time values to account for this behavior I'm not
real sure about how many decimal places to set the rounding. Got a good
"rule of thumb" recommendation for that?

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"T. Valko" wrote:
=LOOKUP(MOD(A1,1),J$1:K$4)

Test when A1 contains 1/1/2009 7:20 AM and 1/1/2009 11:20 PM.

I think you'll find that they return the wrong shift, namely: NIGHT and
AFTERNOON.

The reason is floating point arithmetic aberrations. MOD(A1,1) yields
0.305555555554747,1158206462860107421875 and
0.972222222218988,46328258514404296875 in those case, whereas 7:20 and
23:20 (without dates) -- the equivalent of TIME(7,20,0) and
TIME(23,20,0) -- are
0.305555555555555,52471602709374565165489912033081 0546875 and
0.972222222222222,20988641083749826066195964813232 421875.

The success or failure LOOKUP(MOD(A1,1),...) will depend on the date as
well as the time. For example, it works for 1/1/2009 3:30 PM, but only
by coincidence. In that case, MOD(A1,1) yields
0.645833333335758,6525380611419677734375, and 15:30 is
0.645833333333333,37034076748750521801412105560302 734375.

As usual, the solution is to "round" the time values. In this
case, --TEXT(MOD(A1,1),"hh:mm:ss") would exactly match the internal
representation of any time in the form "hh:mm:ss".

But for the OP's problem, we do not need --TEXT(). We can look up the
TEXT() result.


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

"T. Valko" wrote in message
...
Assuming your dates/times are true Excel dates/times.

One way...

Create a lookup table like this:

..........J..........K
1....0:00.....Night
2....7:20.....Day
3..15:30.....Afternoon
4..23:20.....Night

Then...

A1 = some dd/mm/yyyy hh:mm:ss

=LOOKUP(MOD(A1,1),J$1:K$4)

--
Biff
Microsoft Excel MVP


"John Calder" wrote in message
...
Hi

I run Excel 2K

I download data from a mainframe. This data has a date & time format in
it
(dd/mm/yyyy hh:mm:ss)

I work in a place that has a 3 shift cycle - day shift, afternoon
shift,
night shift.

Day shift starts at 7:20am and ends at 15:29pm
Afternoon shift starts at 15:30pm and ends at 23:19pm
Night shift starts at 23:20pm amd ends at 7:19am

I need a formula that looks at the cell with the date/time in it and
displays the word DAY (for the time frame of day shift), AFTERNOON (for
the
timeframe of afternoon shift) and NIGHT (for the timeframe of night
shift)

In my previous job I used a formula for a different shift pattern which
was
12 hour one which worked really well however the shift pattern that I
need is
for is an 8 hour one, and I dont know how to edit the formula I used
for the
12 hour pattern.

This is the formula I used for the 12 hour shift pattern:

=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")"19:00:00"),"Day","Night")


I hope I have managed to explain this ok.

Thanks

John