negative time recalculation

[bartvdg]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]input[/TD]
[TD="align: center"]input[/TD]
[TD="align: center"]formula (arrived on site - driving time)[/TD]
[/TR]
[TR]
[TD="align: center"]DRIVING TIME[/TD]
[TD="align: center"]ARRIVED ON SITE[/TD]
[TD="align: center"]STARTED DRIVING[/TD]
[/TR]
[TR]
[TD="align: center"]1:00[/TD]
[TD="align: center"]15:00[/TD]
[TD="align: center"]14:00[/TD]
[/TR]
[TR]
[TD="align: center"]3:00[/TD]
[TD="align: center"]19:30[/TD]
[TD="align: center"]16:30[/TD]
[/TR]
[TR]
[TD="align: center"]18:00[/TD]
[TD="align: center"]3:00[/TD]
[TD="align: center"]gives negative number ########
- should be 9:00[/TD]
[/TR]
</tbody>[/TABLE]

Who can help me solving above?
I calculate the 'started driving time" by subtracting arrival time from driving time.
When negative i don't know how to calculate for correct time?

 

[Yongle]

Date is an integer formatted as a date where
1 = 1 Jan 1900
2 = 2 Jan 1900
43591 = 6 May 2019

Time is a decimal between 0 and 1
0.25 = 6 am
.5 = 12 noon
.75 = 6 pm
0.99653 = 11.55 pm
etc

Time differences
A combination of date and time is required to deal with any situation that may arise
43591.25 = 6pm on 6 May 2109 etc
43592.75 = 6am on 7 May 2109 etc
The difference is .5 = 12 hours

Possible simple workaround
If the total time NEVER exceeds 24 hours, a simple workaround is to amend the formula to add 1 to the number if negative

<a1)*1< html=""></a1)*1<>

 

[Yongle]

I intended to post the worksheet to illustrate
Here it is ...

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Drive[/td][td]Arrive[/td][td]Driving time[/td][td] formula [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

01:00​

[/td][td]

15:00​

[/td][td]

14:00​

[/td][td] =(B2-A2)+1*(B2 < A2) [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

03:00​

[/td][td]

19:30​

[/td][td]

16:30​

[/td][td] =(B3-A3)+1*(B3 < A3) [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

18:00​

[/td][td]

03:00​

[/td][td]

09:00​

[/td][td] =(B4-A4)+1*(B4 < A4) [/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[AhoyNC]

Another option (again assuming difference between times will not be greater than 24 hrs.).
Copy formula down as needed.

Excel Workbook
	A	B	C	D
1	Drive	Arrive	Driving time	formula
2	1:00	15:00	14:00	14:00
3	3:00	19:30	16:30	16:30
4	18:00	3:00	9:00	9:00
Sheet