Hello Hello,

With the start time in F7 and the finish time in G7 both entered as XL times
ie 04:30 etc. then:

=IF(OR(F7="",G7=""),"",24*MAX(MIN(G7,TIME(18,30,0) )-MAX(F7,TIME(5,30,0)),0))

will give the number of hours betwen 05:30 and 18:30

and shamelessly stealing Harlan Grove's formula:

=IF(OR(F7="",G7=""),"",24*((G7<=F7)*(1-TIME(5,30,0)+TIME(18,30,0))+MIN(TIME(
5,30,0),G7)-MIN(TIME(5,30,0),F7)+MAX(TIME(18,30,0),G7)-MAX(TIME(18,30,0),F7)
))

will give the number of hours outside of those hours. Both hours will
return the hours as a number not a time, (with the cell formated as
General). If you want it to remain as an XL time theb remove the 24* from
both formulas.


--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"hello" wrote in message
...
Hello,
I posted a question yesterday, but i cannot find my question- or any
answer,
today.
So here we go again-
I have created a breakdown table for my companies roster. I have a simple
formula in the spreadsheet that tells me how many hours of a shift was on
the
day rate (0530-1830) (24hr time), and how much is on the night rate
(1830-0530) ( security guards). for example I need the cell that has the
formula =SUM(G7-1830) (G7 being a time between 0530 and 1830), to display
zero, until i type a figure into G7.
Please help!
P.S- I'm not fluent with Excel so laymans terms would be appreciated.
thank you!!!!!