May 11 2020 03:56 AM
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)
May 11 2020 04:21 AM
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.
May 11 2020 04:22 AM
@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: