Jingles_88
New Member
- Joined
- Feb 7, 2016
- Messages
- 6
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.
<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.
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.