Time Calculation

[John Calder]

Hi

I use Excel 2K

In cell F13 i have:-

27/06/2009 7:00:00 PM

In cell G13 I would like a formula that says that if G13 is greater than or
eqaul to 27/06/2009 7:00:00 PM and less than 27/06/2009 7:00:00 AM then
"NIGHT"

or

If G13 is greater than or equal to 27/06/2009 7:00:00 AM and greater than
less than 27/06/2009 7:00:00 PM then "DAY"


We run the business on a 24 hour clock

Example

DAY SHIFT = 700 hrs to 1900 hrs
NIGHT SHIFT 1900 hrs to 700 hrs

I need a cell that displays if it is NIGHT or DAY

Hope I have explained myself

Thanks

John

 

[T. Valko]

You need to refine your time boundaries:

DAY SHIFT = 700 hrs to 1900 hrs
NIGHT SHIFT 1900 hrs to 700 hrs
27/06/2009 7:00:00 PM

Based on your shift times, 27/06/2009 7:00:00 PM could be both shifts. It
could be either the end of day shift or the beginning of night shift.

 

[OssieMac]

Hello John,

Like Biff I was also confused by whether you want 7PM and 7AM in the night
or day shifts.

The interesting part is that it is difficult to compare actual times because
you get a difference in the actual numeric values due to the decimal place
inaccuracy so I have converted the times to text values on the 24hr clock
basis. The formula should work for any date.

the following formula includes 7am as day and 7pm as night
=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")

the following is 7am night and 7pm day
=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>"07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<="19:00:00"),"Day","Night")

the following is both 7am and 7pm nigh
=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>"07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<="19:00:00"),"Day","Night")

following is both 7am and 7pm is da
=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<="19:00:00"),"Day","Night")

 

[OssieMac]

Hello again John,

On re-reading your request I realize that you said that you have 27/06/2009
7:00:00 PM in G13 and then you say you want the formula in G13. Not sure now
if that was a typo on your part or if that is what you wanted.

Anyway the formulas I gave you assume that 27/06/2009 7:00:00 PM is in F12
and the formulas must be another cell (not the same cell) so I hope they do
what you want.

 

[T. Valko]

They need to define the shifts something like this:

7:00 AM to 6:59 PM = day shift
7:00 PM to 6:59 AM = night shift

 

[John Calder]

There is no doubt about it, you guys are worth your weight in gold ! There
is no way I would have come up with something like that !

I,m sorry I didn't explain myself better.

The night shiift starts at 7:00pm and the dayshift starts at 7:00am

However you gave me every way possible, which is going to be invaluable to
me in the future.

Thanks Again !!

John