Formula help ( midnight time )

[Peter1973]

I am using the following formula :
=(AND('NP Breakdown'!$F4-'NP Per Hour'!$F5>"23:00"+0,'NP Breakdown'!$F4-'NP Per Hour'!$F5<"23:59"+0)*'NP Breakdown'!$H4)

( Thanks Hotpepper for this )

However when the time in F4 is greater than 00:00 it does not recognise is, I have no idea how to fix this.

Any help appreciated.

 

[Andrew Poulsom]

If the end time is less than the start time because it has crossed midnight without adding a day, you need to add a day in your formula. For example, if A1 contains 23:00 and A2 contains 02:00, this formula:

=A2+(A2<A1)-A1

returns 03:00.

 

[Peter1973]

Think I understand ? But I cannot have dates in due to the way the other cells are formatted it thrws everything out of sinc, how would this fit into the above formula.

Thanks

 

[Andrew Poulsom]

My formula got mangled by HTML:

=A2+(A2 < A1)-A1

I don't know what your formula is doing, but maybe you need:

=(AND('NP Breakdown'!$F4+('NP Breakdown'!$F4 < 'NP Per Hour'!$F5)-'NP Per Hour'!$F5>"23:00"+0,'NP Breakdown'!$F4+('NP Breakdown'!$F4 < 'NP Per Hour'!$F5)-'NP Per Hour'!$F5<"23:59"+0)*'NP Breakdown'!$H4)