Excel does not deal well with negative times. What if you are tracking vacation time and some employees go into a negative balance? Excel cant display negative times. However, with the trick in Episode 561, you can coerce Excel into displaying negative times.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Today, I have a rather amazing trick in Excel.
You know, Excel handles Dates and Times and they started tracking Excel Dates, January 1st, 1900's.
So, we can handle everything after that and really we run into a weird problem, if we try and calculate times that end up negative.
The one example I have here is maybe, a company has Comp Time.
You're supposed to work eight hours but if you work more than eight hours in a certain day, then you can take that later in the week.
So, you know here we have someone who started out with a Comp Time Balance of two hours, they work ten hours on Tuesday.
So, now their Comp Time Balance is four hours.
They work another ten hours on Wednesday.
So now, their Comp Time Balance is up to six hours.
Thursday, they leave it.
You know, after four hours.
So now, their Comp Time Balance is down to 2.
Well, Friday they want to take another four hours off and maybe the company says that's okay you're allowed to go negative but when we copy this formula down, which is gonna come up to negative two hours.
Excel completely freaks out!
You're not allowed to have negative time and that's because basically, time is stored as a decimal portion of a day and when you go to negative time, you're actually going back into December 31st 1899, which is illegal to Excel.
Well, there's an amazing solution and I have to tell you if you're going to use the solution, you have to decide to use it before you start to create the workbook.
I'm going to show you a prompt...
Our dates over here and in 2004, I'm gonna go to Tools > Options and then we'll go to the calculation tab and use the 1904 date system.
Now, the 1904 date system is there for compatibility with people using a Macintosh.
But, when we click OK, amazingly, now, Excel is able to handle a negative time and what's going on is with the 1904 date system.
Basically, what happens is Excel takes the date and time that you show and adds four years to it and presents it that way. So, that way, a date of 1900 is showing up as 1904.
The beautiful thing about that is when we have our dates of basically, January 1st 1900, Excel is thinking that it's 1904 and so when we go a few hours before that we're going to a date in 1903, which is perfectly legal.
So, very interesting if you're maybe in human resources, tracking times and you need to allow times to go just a little bit negative.
You know, obviously, can't go more than four years in negative time but if you just need a little bit of negative time, you can turn on the 1904 date system and actually, have Excel do what generally is thought to be impossible with Excel.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen. Today, I have a rather amazing trick in Excel.
You know, Excel handles Dates and Times and they started tracking Excel Dates, January 1st, 1900's.
So, we can handle everything after that and really we run into a weird problem, if we try and calculate times that end up negative.
The one example I have here is maybe, a company has Comp Time.
You're supposed to work eight hours but if you work more than eight hours in a certain day, then you can take that later in the week.
So, you know here we have someone who started out with a Comp Time Balance of two hours, they work ten hours on Tuesday.
So, now their Comp Time Balance is four hours.
They work another ten hours on Wednesday.
So now, their Comp Time Balance is up to six hours.
Thursday, they leave it.
You know, after four hours.
So now, their Comp Time Balance is down to 2.
Well, Friday they want to take another four hours off and maybe the company says that's okay you're allowed to go negative but when we copy this formula down, which is gonna come up to negative two hours.
Excel completely freaks out!
You're not allowed to have negative time and that's because basically, time is stored as a decimal portion of a day and when you go to negative time, you're actually going back into December 31st 1899, which is illegal to Excel.
Well, there's an amazing solution and I have to tell you if you're going to use the solution, you have to decide to use it before you start to create the workbook.
I'm going to show you a prompt...
Our dates over here and in 2004, I'm gonna go to Tools > Options and then we'll go to the calculation tab and use the 1904 date system.
Now, the 1904 date system is there for compatibility with people using a Macintosh.
But, when we click OK, amazingly, now, Excel is able to handle a negative time and what's going on is with the 1904 date system.
Basically, what happens is Excel takes the date and time that you show and adds four years to it and presents it that way. So, that way, a date of 1900 is showing up as 1904.
The beautiful thing about that is when we have our dates of basically, January 1st 1900, Excel is thinking that it's 1904 and so when we go a few hours before that we're going to a date in 1903, which is perfectly legal.
So, very interesting if you're maybe in human resources, tracking times and you need to allow times to go just a little bit negative.
You know, obviously, can't go more than four years in negative time but if you just need a little bit of negative time, you can turn on the 1904 date system and actually, have Excel do what generally is thought to be impossible with Excel.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
