Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Time Calculation
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Time Calculation
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. -- Biff Microsoft Excel MVP "John Calder" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Time Calculation
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 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") following is both 7am and 7pm is 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") -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Time Calculation
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. -- Regards, OssieMac "OssieMac" wrote: 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 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") following is both 7am and 7pm is 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") -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Time Calculation
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 -- Biff Microsoft Excel MVP "OssieMac" wrote in message ... 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 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") following is both 7am and 7pm is 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") -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Time Calculation
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 "OssieMac" wrote: 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 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") following is both 7am and 7pm is 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") -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
~Time Calculation | Excel Worksheet Functions | |||
Stop time - start time calculation | Excel Worksheet Functions | |||
Time calculation (Subraction of Idle Time) | Excel Discussion (Misc queries) | |||
Ignoring Time in a Date Time Calculation | Excel Worksheet Functions | |||
time calculation with military time | Excel Worksheet Functions |