# Formula help ( midnight time )

#### Peter1973

##### Well-known Member
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.

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.

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

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)

Replies
1
Views
256
Replies
0
Views
199
Replies
4
Views
279
Replies
7
Views
200
Replies
10
Views
353

1,196,181
Messages
6,013,909
Members
441,794
Latest member
Deepakjaat31

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back