Join Dennis Taylor for an in-depth discussion in this video Using the TODAY and NOW functions for dynamic date/time entry, part of Excel 2010: Working with Dates and Times.
- View Offline
You sometimes will need to put in the current date or the current time in such a way that the entry will constantly change and update as you open and close the file, in other words you always wanted to be accurate. The method for entering a Dynamic date is to use the function called today. today, no reason to capitalize it, left parenthesis and Enter. Now it's accurate at the moment of the time of this recording. However, if this file is saved and opened tomorrow, the next day, the next day, on each occasion the date will be different.
It does answer that question a lot of people have sometimes when they say, "why is it with certain files when I open them and might not even make a change and when I close the file I get a prompt that says Do you want to save your changes?" It's probably because you've had a function like this inserted somewhere in the workbook. Because this does update, even if it's the same day, it will change the entry. If you need to put in the current time, you can use the function called =now(. Now initially this enters both date and time, and you possibly could leave it that way.
If you would like to turn this into a display, so that it's just the time, you might press Ctrl+1 or right-click Format Cells and go find a favorite format out there for you. Possibly under Time it might be either the 24 hour style or the AM/PM style, one of those, if you want to use this to display time. This entry too will change, but if you simply stare at this or watch it for a while nothing will happen, but as you go about your work within a worksheet this will get updated, and occasionally you look back and recognize it will have changed.
The way some people use these functions is that within a given workbook, for example here say on this worksheet, maybe every time they print this, they have been updating it recently with some totals. They want to make sure that every time they print this that there is a time-stamp on it or a date-stamp that indicates when this action took place. So what one might do right here in Cell A3, simply type =now(, Enter, and that's displayed. So if we were to print this right now that would indicate the date and time that we are actually printing this.
So there are different ways to use this feature. If we go back to this other worksheet here, possibly as I am talking right now maybe the time has passed the minute, you certainly not going to worry about this, but if I put in some date over here and press Enter, maybe that time will change, maybe not. It depends on other things that I might be doing, but it will change and over time you don't worry about that too much, but there it is, a change there. So as you work with your data these two functions, the TODAY function and the NOW function, have a role to play.
And there are also some interesting things you can do with these functions when you are working with certain kinds of data, for example, birth dates, anniversary dates. You can use the TODAY function to calculate time differences that will be accurate to the day over a long period of time.
- Understanding how Excel records and stores dates and times
- Looking at standard date/time entry options and acceptable alternatives
- Using the TODAY and NOW functions
- Customizing date formats
- Exploring keyboard shortcuts
- Formatting time for hours over 24
- Calculating differences across dates and times
- Rounding calculations
- Working with holidays
- Validating with dates
- Converting formatted dates to usable data