When you calculate the difference between two time cells in Microsoft Excel (2010 is what I'm using) displays ##### as a result if the difference is negative, because Excel can not display a negative time.
- One solution is to convert this to a number, which can be shown as a negative value. Internally Excel has the time stored as a number of days, so convert this to the unit you want to display. e.g. to show the difference between time b1 and a1 as a number of minutes
= (b1-a1)*60*24
- Doing so, you loose Excel time formatting
- Sometimes the second time (b1) is always later, but because it can be after midnight its value can be smaller. In thiscase, simply test if time b1 is smaller than a1, and add 1 day if this is so:
=IF(b1>=a1;b1-a1;b1-a1+1)
Posts
