Most Popular Functions -#12 =NOW()- and -#13 =TODAY()-
Over the summer of 2012 MrExcel started a series called "Most Popular [Excel] Functions"... but book writing and updating editions took the lead and our 'Most Popular Functions' series came to a pause.
Today, Bill 'MrExcel' Jelen resumes the Most Popular [Excel] Functions series with the " =NOW " and " =TODAY " Functions. Follow along with Episode #1624 to see the differences between the two -- as well as a brief on " =INT(NOW( " -- and where each can be applied effectively!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Over the summer of 2012 MrExcel started a series called "Most Popular [Excel] Functions"... but book writing and updating editions took the lead and our 'Most Popular Functions' series came to a pause.
Today, Bill 'MrExcel' Jelen resumes the Most Popular [Excel] Functions series with the " =NOW " and " =TODAY " Functions. Follow along with Episode #1624 to see the differences between the two -- as well as a brief on " =INT(NOW( " -- and where each can be applied effectively!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1624.
Most Popular Functions, number 12 NOW and number 13 TODAY.
Hey! Welcome back to the MrExcel netcast.
When we started this over the summer, the series of the most popular Excel functions.
Well, we got the first couple episodes done 1553 with SUM, 1556 with AVERAGE and then I started writing the book.
So, we're coming back here to kind of work in this.
Today, we're going to do number at 12 and 13.
12 is the NOW function and 13 is TODAY.
Two very related functions, so let's say that we have a list of projects and a list of due dates and I need to calculate how many days away those projects, those due dates are?
Okay! Well, today is 12/27/2012.
So, I could add a little column here that, I would come in and update every single day and then of course the days until due.
It's just a simple little calculation of 1227, actually the due date January 2nd minus 1227.
That's going to give us an answer 6, copy that down and those are late, those were due, yesterday.
I better get on it.
Okay! So, you know, in theory, you'd come in every day, update this cell and you could calculate the number of days due.
However, there is a better way to go in the function that most people seem to use is equal NOW.
Now, let's see returns the current date and time formatted as a date and time.
All right! So, you see that it's 622 a.m.
on December 27th.
So, how can we use NOW, to simplify this.
Well, one way is...
We'll just get rid of all these calculations.
Get rid of that column, days until due, is equal the due date minus NOW.
All right! Now, see they weren't smart enough to format that correctly.
So, we have to go in and format as a number.
Right there, that's the problem.
So, it's five point seven three days until this is due, that's not really 5.73 days.
It's 6 days and the fact that there's .73 days left until midnight, tonight.
That really doesn't help me, so I want that to be 6.
and one solution is to decrease the decimals, all right!
Which is going to round everything and that's going to work perfectly.
Until I get back from lunch, when I leave for lunch at 11:59.
That's going to say 6, when I get back from lunch, actually anytime afternoon.
This is going to change to 5, and if something's due tomorrow, if something's due on the 28th here.
Let's just change this.
This will say one day until it's due.
Until after lunch, then I'll say no hey, it's due, today.
It's zero days due.
So, you know I, well I've used this method in the past.
I'm not a big fan of it.
So, we have equal NOW, which gives us date and time.
You could always use equal INT.
NOW, which gives us midnight, this morning at 12/27.
But instead of that, you can do equal TODAY.
TODAY returns to the current date format, it as a date.
So, TODAY and NOW are very similar, the difference is NOW is giving us the the date and time.
TODAY is just giving us the date 12/27.
So, coming back up here to fix our formula, instead of subtracting NOW, subtract TODAY.
Copy that down and no matter how many decimals we show, now.
It's always going to say, you know hey, this is, things due tomorrow or one day away.
So there you go, the difference between NOW and TODAY.
Hey! I want to thank you for stopping by, we’ll see you next time for another netcast, from MrExcel.
Learn Excel from MrExcel podcast, episode 1624.
Most Popular Functions, number 12 NOW and number 13 TODAY.
Hey! Welcome back to the MrExcel netcast.
When we started this over the summer, the series of the most popular Excel functions.
Well, we got the first couple episodes done 1553 with SUM, 1556 with AVERAGE and then I started writing the book.
So, we're coming back here to kind of work in this.
Today, we're going to do number at 12 and 13.
12 is the NOW function and 13 is TODAY.
Two very related functions, so let's say that we have a list of projects and a list of due dates and I need to calculate how many days away those projects, those due dates are?
Okay! Well, today is 12/27/2012.
So, I could add a little column here that, I would come in and update every single day and then of course the days until due.
It's just a simple little calculation of 1227, actually the due date January 2nd minus 1227.
That's going to give us an answer 6, copy that down and those are late, those were due, yesterday.
I better get on it.
Okay! So, you know, in theory, you'd come in every day, update this cell and you could calculate the number of days due.
However, there is a better way to go in the function that most people seem to use is equal NOW.
Now, let's see returns the current date and time formatted as a date and time.
All right! So, you see that it's 622 a.m.
on December 27th.
So, how can we use NOW, to simplify this.
Well, one way is...
We'll just get rid of all these calculations.
Get rid of that column, days until due, is equal the due date minus NOW.
All right! Now, see they weren't smart enough to format that correctly.
So, we have to go in and format as a number.
Right there, that's the problem.
So, it's five point seven three days until this is due, that's not really 5.73 days.
It's 6 days and the fact that there's .73 days left until midnight, tonight.
That really doesn't help me, so I want that to be 6.
and one solution is to decrease the decimals, all right!
Which is going to round everything and that's going to work perfectly.
Until I get back from lunch, when I leave for lunch at 11:59.
That's going to say 6, when I get back from lunch, actually anytime afternoon.
This is going to change to 5, and if something's due tomorrow, if something's due on the 28th here.
Let's just change this.
This will say one day until it's due.
Until after lunch, then I'll say no hey, it's due, today.
It's zero days due.
So, you know I, well I've used this method in the past.
I'm not a big fan of it.
So, we have equal NOW, which gives us date and time.
You could always use equal INT.
NOW, which gives us midnight, this morning at 12/27.
But instead of that, you can do equal TODAY.
TODAY returns to the current date format, it as a date.
So, TODAY and NOW are very similar, the difference is NOW is giving us the the date and time.
TODAY is just giving us the date 12/27.
So, coming back up here to fix our formula, instead of subtracting NOW, subtract TODAY.
Copy that down and no matter how many decimals we show, now.
It's always going to say, you know hey, this is, things due tomorrow or one day away.
So there you go, the difference between NOW and TODAY.
Hey! I want to thank you for stopping by, we’ll see you next time for another netcast, from MrExcel.
