In excel, when I work with hour, and I need for instance to know the duration of an issue, excel may show a negative time but instead of that, it shows something like “######”.
There are different ways to resolve and based on what I need to do, one is better than the other. Let’s see the different options.
Option 1
Active the function “use 1904 date system”, to do that, go to “file -> options -> advanced” then search for “when calculating this workbook”. If you have many excel files open, make sure to select the right one before selecting this option (it will only apply for this file).
The only thing for this option is that I can get an error if I have other pivottables or tables because it will recalculate the whole file and some may not work properly. To remediate, if you know which one, just recreate it, if not, recreate all of them. The other solution, create a new file and start from 0.
Option 2
Use this formula:
=TEXT(MAX(A2:B2)-MIN(A2:B2),"-[h]:mm")
But it will always show a negative value.
Option 3
Use this formula:
=IF(B2-A2<0,"-"&TEXT(ABS(B2-A2),"[h]:mm"),B2-A2)
Then format the cell with “[h]:mm”. Comparing to the option 2, this one allows to show a positive and negative hour.
NOTE
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...