Bill from Washington asks how to track vacation time, specifically times greater than 24 hours and times less than 0 hours. This requires two different tricks. Episode 983 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Where's it Wednesday?
We have to choose that icon.
It used to be right there.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's questions sent in by Bill.
Bill asked questions about times.
He's tracking vacation time and it ran into two situations.
Two Situations...
Now, this might turn into where is it Wednesday, when I try and go solve the second problem.
He has a Vacation balance here.
This is how much he earned and how much he used and this is all working great.
However, it's going to run into a problem as we start earning more.
So, here he had 23 hours of vacation he earned to.
And then all of a sudden he has nothing.
What's up with this.
Well in a traditional time format, it shows only the hours in access of a full day.
Think about if you would enter the function equal now, it's going to be 39722 and you know maybe point 6.
When we use time, it throws out the integer number of days and uses just the time portion, and that's what's happening here.
But luckily, there's a great format built in.
So, I press [ CTRL+1 ] to get to format cells.
Go to time and you can use this one here, the 37 hours.
That's their code word for saying hey, we're going to show you more hours than 24.
So, that will force it to work now.
For those of you, who are you know, I don't know.
The traditional way to do this is to go to custom and put square brackets around the h.
That's the way I've always taught it.
You know, but when you get right down to it they've added it in here to the time.
So, we might as well use the easy one And we're good to go.
So, that solves bills first problem.
Now, here's the next problem.
He runs into a situation where he uses vacation time and at his company, you're allowed to borrow ahead up to some certain amount.
So I'm going to put in 40 hours here.
He takes a whole week's worth of vacation and now the calculation doesn't work at all.
And why is that.
Well, because quite simply Excel does not allow for negative time.
It's against the rules but a great solution to this problem, and it's buried here We're going to go to the Excel options and then to Advanced and we're going to look the advanced tab.
Here it is horrible with all of these different categories.
Now it used to be on 13 different tabs in Excel 2003.
Now, they took many of those tabs and put them here on the advanced and so you end up scrolling, scrolling, scrolling.
When calculating this workbook, check this box called used 1904 date system.
What is the 1904 date system.
It's there for the Macintosh.
The Macintosh doesn't know dates before January first 1904.
So, we use the 1904 date system.
And what that forces Excel to do is take every date and time and adjust it by three years.
Which all of a Sudden now allows you to have up to three years of negative time .
Now I'm sure the Bill is not going to use three years of vacation.
They'll fire them long before he gets to that.
So this is a great trick for one wait Just have comp time or vacation time and you need to allow it to go a little bit negative.
Go to the 1904 date system, and everything's cool.
Now, one caution had there been any dates here.
They would be as just as well.
So, you have to be very careful that you go to the 1904 date system before you start entering any dates in the spreadsheet.
Or check them all out.
Want to thank Bill for sending that question And I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Where's it Wednesday.
It used to be right there
We have to choose that icon.
It used to be right there.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's questions sent in by Bill.
Bill asked questions about times.
He's tracking vacation time and it ran into two situations.
Two Situations...
Now, this might turn into where is it Wednesday, when I try and go solve the second problem.
He has a Vacation balance here.
This is how much he earned and how much he used and this is all working great.
However, it's going to run into a problem as we start earning more.
So, here he had 23 hours of vacation he earned to.
And then all of a sudden he has nothing.
What's up with this.
Well in a traditional time format, it shows only the hours in access of a full day.
Think about if you would enter the function equal now, it's going to be 39722 and you know maybe point 6.
When we use time, it throws out the integer number of days and uses just the time portion, and that's what's happening here.
But luckily, there's a great format built in.
So, I press [ CTRL+1 ] to get to format cells.
Go to time and you can use this one here, the 37 hours.
That's their code word for saying hey, we're going to show you more hours than 24.
So, that will force it to work now.
For those of you, who are you know, I don't know.
The traditional way to do this is to go to custom and put square brackets around the h.
That's the way I've always taught it.
You know, but when you get right down to it they've added it in here to the time.
So, we might as well use the easy one And we're good to go.
So, that solves bills first problem.
Now, here's the next problem.
He runs into a situation where he uses vacation time and at his company, you're allowed to borrow ahead up to some certain amount.
So I'm going to put in 40 hours here.
He takes a whole week's worth of vacation and now the calculation doesn't work at all.
And why is that.
Well, because quite simply Excel does not allow for negative time.
It's against the rules but a great solution to this problem, and it's buried here We're going to go to the Excel options and then to Advanced and we're going to look the advanced tab.
Here it is horrible with all of these different categories.
Now it used to be on 13 different tabs in Excel 2003.
Now, they took many of those tabs and put them here on the advanced and so you end up scrolling, scrolling, scrolling.
When calculating this workbook, check this box called used 1904 date system.
What is the 1904 date system.
It's there for the Macintosh.
The Macintosh doesn't know dates before January first 1904.
So, we use the 1904 date system.
And what that forces Excel to do is take every date and time and adjust it by three years.
Which all of a Sudden now allows you to have up to three years of negative time .
Now I'm sure the Bill is not going to use three years of vacation.
They'll fire them long before he gets to that.
So this is a great trick for one wait Just have comp time or vacation time and you need to allow it to go a little bit negative.
Go to the 1904 date system, and everything's cool.
Now, one caution had there been any dates here.
They would be as just as well.
So, you have to be very careful that you go to the 1904 date system before you start entering any dates in the spreadsheet.
Or check them all out.
Want to thank Bill for sending that question And I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Where's it Wednesday.
It used to be right there
