Calculating with times : time ahead or behind schedule

%3CLINGO-SUB%20id%3D%22lingo-sub-1379385%22%20slang%3D%22en-US%22%3ECalculating%20with%20times%20%3A%20time%20ahead%20or%20behind%20schedule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1379385%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20calculate%20how%20much%20time%20someone%20is%20ahead%20or%20behind%20schedule%2C%20but%20am%20getting%20%23%23%23%23%23%23%20when%20the%20outcome%20is%20a%20negative%20value.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAhead%20of%20schedule%20should%20give%20me%20an%20output%20of%20hh%3Amm%20(positive)%20en%20behind%20schedule%20should%20give%20me%20an%20output%20of%20-hh%3Amm%20(negative).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eeg.%3C%2FP%3E%3CP%3Esomone%20is%20due%20to%20arrive%20at%2010%3A30%20and%20arrives%20at%2010%3A24%20%3B%20so%20the%20result%20is%26nbsp%3B%2000%3A06%20(ahead%20of%20schedule)%3C%2FP%3E%3CP%3Esomone%20is%20due%20to%20arrive%20at%2010%3A30%20and%20arrives%20at%2010%3A40%3B%20so%20he%20-00%3A10%20(behind%20schedule)%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1379385%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1379431%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20with%20times%20%3A%20time%20ahead%20or%20behind%20schedule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1379431%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F485773%22%20target%3D%22_blank%22%3E%40patrick_hh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20doesn't%20show%20negative%20time%20in%20time%20format%20-%20time%20is%20always%20positive.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20are%20workarounds%20to%20show%20difference%20of%20times%20in%20time%20format%20if%20it%20is%20negative.%20We%20may%20display%20it%20as%20text%20using%20formula%20as%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexcelribbon.tips.net%2FT006239_Displaying_Negative_Times.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcelribbon.tips.net%2FT006239_Displaying_Negative_Times.html%3C%2FA%3E%26nbsp%3Bor%20like.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20way%20is%20to%20display%20it%20as%20number%20with%20digital%20part.%20For%20example%2C%20if%20show%20in%20minutes%2C%20time%20difference%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D(B1-A1)*24*60%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eapplying%20to%20the%20result%20number%20format.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I'm trying to calculate how much time someone is ahead or behind schedule, but am getting ###### when the outcome is a negative value. 

 

Ahead of schedule should give me an output of hh:mm (positive) en behind schedule should give me an output of -hh:mm (negative).

 

eg.

somone is due to arrive at 10:30 and arrives at 10:24 ; so the result is  00:06 (ahead of schedule)

somone is due to arrive at 10:30 and arrives at 10:40; so he -00:10 (behind schedule) 

2 Replies
Highlighted

@patrick_hh 

Excel doesn't show negative time in time format - time is always positive.

 

There are workarounds to show difference of times in time format if it is negative. We may display it as text using formula as here https://excelribbon.tips.net/T006239_Displaying_Negative_Times.html or like.

 

Another way is to display it as number with digital part. For example, if show in minutes, time difference could be

=(B1-A1)*24*60

applying to the result number format.

Highlighted

@patrick_hh : excel does not allow negative time.
You need to use conditional formatting to reflect cells in 'Red'/ 'Green' color to show ahead to behind schedule.
Or you can have additional column to reflect status, like below image:

 

bhushan_z_1-1589196135012.png