Formula for minutes early and late before and after midnight.

[Jingles_88]

Hi,


I have been working on a spreadsheet which displays the minutes a vehicle if early or late on site. My problem however is that some of the arrivals happen after midnight (00:00); and the formulas i have been using work for wither before or after, not both.

Scheduled On Site	On Site	On Site Variance
23:37
23:30
01:20
02:05

<tbody>
</tbody>

I have used the below formula that i found in another forum:
=IF(B2<A2,(B2+1)-(A2),(B2-A2))


The only issue is when the vehicle is early and before midnight the time is incorrect. If i remove the +1 in (B2+1), that issue is solved, but it no longer calculates passed midnight times.


I was wondering if there's a formula that is able to do both. Also, is there a way in which a value doesn't appear in the "On Site Variance" column until a time is entered into the corresponding cell in the "On Site" column.


Any help is greatly welcomed, i have stuck on this for some time.


Many Thanks,


John.

 

[Jingles_88]

Re: [SOLVED] Formula for minutes early and late before and after midnight.

Thanks again Marcel, couldn't have done it without you.

John.