Recording positive and negative time formula

[Leicester City Fox]

Hi There

I have two columns stating time Column A, Planed Time Column B Actual Arrived time.

For Example, planned time: 08:35, Arrived time 08:36 = (Difference) 00:01

But when it a negative time ie earlier than planned time it will not work this out.

Also I notice the actual arrival Time has the date showing 25/03/2019 with time example 25/03/2019 17:35:00 but it only show the time so I don’t know if this why my formula for positive and negative time difference don’t work or do I need to delete the date and if so how if it is not actual showing in the display cell?

If anybody can help with formula that will give both positive (+) and negative (-) time difference are be grateful.

Many Thanks

Leicester Fox

 

[mole999]

think you need to change the workbook date setting to 1904 from 1900

 

[CalcSux78]

You could change to the 1904 Date system.

See: https://excelribbon.tips.net/T006239_Displaying_Negative_Times.html

 

[Marcelo Branco]

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Planned​

[/td][td]

Arrived​

[/td][td]

Result (minutes)​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

12/06/2019 08:35​

[/td][td]

12/06/2019 10:34​

[/td][td]

+119​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

12/06/2019 08:35​

[/td][td]

12/06/2019 08:34​

[/td][td]

-1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

11/06/2019 23:40​

[/td][td]

12/06/2019 00:10​

[/td][td]

+30​

[/td][/tr]
[/table]

Formula in C2 copied down
=(INT(B2)-INT(A2))*1440+(HOUR(B2)-HOUR(A2))*60+MINUTE(B2)-MINUTE(A2)

Format column C
Custom
+0;-0

Hope this heps

M.

 

[Leicester City Fox]

Hi Marcelo


Thank you for the formula it worked a treat ��


Many Thanks You’re a Star


The Leicester Fox

 

[Leicester City Fox]

Hi there
Thank for your reply unfortunately it didn’t resolve theproblem.
But Marcelo formula work ��
Thank You
Leicester Fox

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.