Night shift hours dilema

[sonar]

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(H14>TIME(19,0,0),H14-TIME(19,0,0),0))

Thank

 

[David McRitchie]

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+(A2>B2)

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

 

[JWolf]

=if(or(g14=0,h14=0),"",(IF(G14<TIME(7,0,0),TIME(7,0,0)-G14,0))+(IF(H14>TIME(19,0,0),H14-TIME(19,0,0),0)))

 

[sonar]

Thanks for helping. The last one work

 

[David McRitchie]

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

 

[David McRitchie]

H14: should be in core time
I14: should be out of core time