Many people use =NOW(), but it is frustrating when you try to calculate how many days away a certain event is occurring. Episode 986 shows a method for solving this problem.
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:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're gonna analyze this. Well, let's fire up a pivot table.
Let's see of you can solve this problem.
Hey, all right welcome back to MrExcel netcast.
I'm Bill Jelen.
Here we are episode 986.
Someone sent me in a note said well, what are you going to do for a thousand.
And you know I, this is a tough one for me because while it is a magic number 1000 and it's probably going to cause the thing to sort wrong and itunes and all kinds of y2k disaster things.
It's not really the thousand podcast because I started at episode 101.
So, it's only the 900th podcast it doesn't seem like I don't know, if you have any ideas, what you think we should do for, for number 1000 which really isn't a thousand, It's just a number.
Drop me a note bill@mrexcel.com.
Wanna talk about when you're tracking deadlines.
So, you have some event that's doing the future.
In this case, I'm going to say 4/15/2009. Of course I'm recording this a few days in advance.
So, when I use the =NOW( Function.
NOW as a function that I know a lot of us probably use.
Now, pretty cool because it actually comes in and says hey, it's April 2nd at 9:56 a.m. in the morning.
When you use NOWof course the time shows up, and that may not be what you want to do.
So, you can get rid of that by using Format Cells, I press control+1, and basically, we'll get rid of the our portion.
So, now that's good, but then when we calculate days away, so days away is C3 minus B3. It's gonna keep coming up with strange there's like 12.58 and this afternoon well this would round to 13 it's going to change to 12.
So, it's really kind of arbitrary that after you know you come back from lunch that the days away, is going to be down one.
So, my solution here rather than just format this or use round or round up or something like that rather than use the NOW function.
I want you to consider using a different function.
This function is called TODAY, TODAY is very similar to NOW.
Except it leaves off the time portion.
So, I choose =TODAY(), you see that we don't actually have any decimals if I press F2, F9, it just rounds it off to today at midnight or you know, 12:01 as the day started and so then the number of days away will work, we're 13 days away from the 15th and it won't change until 12 days away until we get in tomorrow and today recalculates as, April 3rd.
There you have it.
Wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast form MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're gonna analyze this. Well, let's fire up a pivot table.
Let's see of you can solve this problem.
Hey, all right welcome back to MrExcel netcast.
I'm Bill Jelen.
Here we are episode 986.
Someone sent me in a note said well, what are you going to do for a thousand.
And you know I, this is a tough one for me because while it is a magic number 1000 and it's probably going to cause the thing to sort wrong and itunes and all kinds of y2k disaster things.
It's not really the thousand podcast because I started at episode 101.
So, it's only the 900th podcast it doesn't seem like I don't know, if you have any ideas, what you think we should do for, for number 1000 which really isn't a thousand, It's just a number.
Drop me a note bill@mrexcel.com.
Wanna talk about when you're tracking deadlines.
So, you have some event that's doing the future.
In this case, I'm going to say 4/15/2009. Of course I'm recording this a few days in advance.
So, when I use the =NOW( Function.
NOW as a function that I know a lot of us probably use.
Now, pretty cool because it actually comes in and says hey, it's April 2nd at 9:56 a.m. in the morning.
When you use NOWof course the time shows up, and that may not be what you want to do.
So, you can get rid of that by using Format Cells, I press control+1, and basically, we'll get rid of the our portion.
So, now that's good, but then when we calculate days away, so days away is C3 minus B3. It's gonna keep coming up with strange there's like 12.58 and this afternoon well this would round to 13 it's going to change to 12.
So, it's really kind of arbitrary that after you know you come back from lunch that the days away, is going to be down one.
So, my solution here rather than just format this or use round or round up or something like that rather than use the NOW function.
I want you to consider using a different function.
This function is called TODAY, TODAY is very similar to NOW.
Except it leaves off the time portion.
So, I choose =TODAY(), you see that we don't actually have any decimals if I press F2, F9, it just rounds it off to today at midnight or you know, 12:01 as the day started and so then the number of days away will work, we're 13 days away from the 15th and it won't change until 12 days away until we get in tomorrow and today recalculates as, April 3rd.
There you have it.
Wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast form MrExcel.
