Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Night shift hours dilema
Hi, I am using this formula, but everytime I have no hours in my star
and finish cells, or if its 00:00, it gives me a 7.00, how can I mak this not do that. But instead make it a 0.00 rather, as I dont want m total of all the nightshift hours to show an amount if its not suppos to exist. =(IF(G14<TIME(7,0,0),TIME(7,0,0)-G14,0))+(IF(H14TIME(19,0,0),H14-TIME(19,0,0),0)) Thank -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Night shift hours dilema
Tested for zero and for empty in either or both cells
as well as start 23:05 finish 12:05 (13 hours total) format the result as time [h]:mm which is a format you can use for totals over 23hrs 59 minutes. The formula is only good up to 23hrs 59 minutes. =B2-A2+(A2B2) adds 1 day (24 hours) if start time is greater than end time. More on my webpage http://www.mvps.org/dmcritchie/excel/datetime.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "sonar " wrote in message ... Hi, I am using this formula, but everytime I have no hours in my start and finish cells, or if its 00:00, it gives me a 7.00, how can I make this not do that. But instead make it a 0.00 rather, as I dont want my total of all the nightshift hours to show an amount if its not suppost to exist. =(IF(G14<TIME(7,0,0),TIME(7,0,0)-G14,0))+(IF(H14TIME(19,0,0),H14-TIME(19,0,0),0)) Thanks --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Night shift hours dilema
=if(or(g14=0,h14=0),"",(IF(G14<TIME(7,0,0),TIME(7, 0,0)-G14,0))+(IF(H14TIME(19,0,0),H14-TIME(19,0,0),0))) sonar < wrote: Hi, I am using this formula, but everytime I have no hours in my start and finish cells, or if its 00:00, it gives me a 7.00, how can I make this not do that. But instead make it a 0.00 rather, as I dont want my total of all the nightshift hours to show an amount if its not suppost to exist. =(IF(G14<TIME(7,0,0),TIME(7,0,0)-G14,0))+(IF(H14TIME(19,0,0),H14-TIME(19,0,0),0)) Thanks --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Night shift hours dilema
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Night shift hours dilema
I apologize for not looking at the formula first presented.
(It really helps if you state what you are trying to do). Since you are trying to determine the time spent on a job in out of core hours, I would suggest looking at Daniel Maher's solution on my date and time page http://www.mvps.org/dmcritchie/excel/datetime.htm#core which will handle start and ending times times going through midnight representing up to 23hrs 59 minutes. So for your question and testing that there is an entry in G14 (ending time), the formula for out of core hours outside of 7:00-19:00 would be: H14: out of core time =IF(TRIM(G14)="","",IF(G14=F14,MAX(0,MIN(G14,"19: 00")-MAX(F14,"7:00")),MAX(0,"19:00"-MAX(F14,"7:00"))+MAX(0,MIN(G14,"19:00")-"7:00" ))) H15: in core time =IF(TRIM(G14)="","", (G14<F14)+G14-F14 - H14 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "JWolf" wrote in message . .. =if(or(g14=0,h14=0),"",(IF(G14<TIME(7,0,0),TIME(7, 0,0)-G14,0))+(IF(H14TIME(19,0,0),H14-TIME(19,0,0),0))) sonar < wrote: Hi, I am using this formula, but everytime I have no hours in my start and finish cells, or if its 00:00, it gives me a 7.00, how can I make this not do that. But instead make it a 0.00 rather, as I dont want my total of all the nightshift hours to show an amount if its not suppost to exist. =(IF(G14<TIME(7,0,0),TIME(7,0,0)-G14,0))+(IF(H14TIME(19,0,0),H14-TIME(19,0,0),0)) Thanks --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Night shift hours dilema
H14: should be in core time
I14: should be out of core time |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Figuring Late night Hours | Excel Discussion (Misc queries) | |||
Total time vs hours per shift | Excel Worksheet Functions | |||
calculate finishing time if night shift aren't working | Excel Worksheet Functions | |||
Help: Trying to get hours for 11pm-7am shift | Excel Worksheet Functions | |||
Hours worked on job/per shift | Excel Programming |