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/