Use the dynamic TODAY and NOW functions for real-time up-to-date calculations

When you work with time sensitive data there will be situations where you need to rely upon functions that give us the current date and/or the current time. In this particular work sheet, we want to calculate the age based on a birth date, but we want this entry always to be correct. The same thing with the service years. How long has this person been here? If it's today that I'm talking about, I might get a certain number, but a year from now that number's going to be larger or three years from now. I want to use this file and always make sure that this data here is up to date, as well as the data that we're going to put in here.

A quick reminder, the two functions that we're talking about are called today and now. Like all functions, they're followed by parentheses, but if you're simply typing the function by itself and you probably wouldn't need to do this very often, but if you're typing =today, type the left parenthesis and that's it, press enter. At the time of this recording it's November 29th of 2016. It's companion is called now and at the time of this recording, and I don't need to type the right parenthesis here, the time is 11:59, on that same day of course.

Both of these features are dynamic. If this were 11:59 pm, I'm working near midnight, making some changes to the worksheet here, at one point I might look back here and that would say 11/30/2016 and the time will have changed also. Right now has it been within the minute, when my system clock has changed, I don't know, but if I type a number here and press enter that might say 12 o'clock and it did. But I'm not going to continually test that.

If I type in another number here and so on, that's not changing and it'll be another minute before it changes, roughly. So we're not going to worry about that. Of the two functions, you're much more likely to use the today function. Keep in mind it allows us to keep track of the current day. Any column that has an age in it, like this, in almost all cases you want a formula here to make sure that it's always current. Temporarily, here, I'll make the column wider. The function we're going to be using here, along with today, is an unusual function called datedif.

This is an unusual function because typically, when you type in a function name and then left parenthesis, you usually get a tool tip below this reminding you of the arguments, but not in this case. Nevertheless, it does work. Datedif begins with a starting date, in this case the birth date, comma and we want to compare the birth date with today's date. The idea here is anytime we open this file, we want this age calculation to be accurate. Here, where we use today, we must use both parentheses comma and now, although there are six possibilities here, the most common one we would use for age, by far, would be year.

Then we simply put in the letter y, upper or lower case, it makes no difference, but between double quotes. If this were a piece of equipment and instead of birth date that were installation date, we might want to know the age of the item in months. So, right parenthesis here, we will have the age of this person as of late November of 2016. I'll double click to copy this down the column. Now, if you were using this list or let's say I were using this three or four months from now, some of these entries will have changed.

This one here will have changed. We will have moved into another year and that's in December, so this will be 52 if we were looking at this in January of 2017. So these are changing all the time. Think out any situation where you've got age, you're going to want to have a formula there. I can double click and make this narrower. We want to do the same thing here with service years. I'm not going to retype it, I'll simply copy this using the right mouse button, I'll drag this cell over to here, let go of the right mouse button, copy here, and what are we seeing now.

For me, right now, it's late 2016, so this is correct. I'll double-click to copy this down the column, these other numbers start to make sense, but what's our calculation here, same as it was over there. We're comparing a starting date, in this case it's a hire date, with today's date. This case zero, you might expect that. By using different formats, by the way, you might display that as a blank. For example, click the comma button up here and then show no decimals, you might see it that way. It isn't necessarily better, it's just a question of choice.

Different ways to calculate these. Keep in mind, these will change. My list here is about 700 names, that's about how many days there are in a two year period. If I open this file every day, let's say on average then, two of these ages will have changed. I'm probably not going to view the data in that way, but we've got a good dynamic entry here that's always up to date. Right now, in my example here, it is November 29th, but supposed this person right here was born on the 16th of December, for the moment.

I'll make that be a 12, so that 50 is going to change, it's 49. What if that person were born December first? Now for me right now, that's two days away. That didn't change. What if this is November 30th? For me right now that's tomorrow. Is this going to change? No, it's not, it's still going to be 49. But if it is today's date and right now, for me, it's November 29th, this will change, it will become a 50. It's accurate to the day and it's a great tool for this kind of calculation.

Now, on a different sheet here, I've got some bills. Quite a big list here. Due date, I want to know if they're overdue or not. Keep in mind today's date, I'll put it here. This is at the date of recording, therefore what we see in column C, will be relevant and based on this date. In a real life situation, of course you wouldn't need to put today's date in here. How do you put in today's date quickly? You don't have to type =today, you can press control, semi colon, that puts in the current date. That's the current date for me, right now. That's just there as a reminder, it doesn't really need to be there.

But I want a formula here and I'm going to be opening and closing this file at different times, I want to know whether certain billing items here are overdue or not. So, the formula here, =if, and I want to be checking this due date here with today's date. Put in today, of course we can do this in a couple of different ways. I want to check to see if today's date is greater than this entry column. By saying today's date is greater than, we really mean is it later than today's date.

If that's true, put in yes or any other term you want here, within double quotes. When that's not true, let's simply show a blank, double quote, double quote and a right parenthesis. So every time, as we're looking at the data in column B, every time we look there, we're testing the date with today's date. If today's date is greater than the due date, here, it means this is overdue. So I'll complete the entry here with control enter, so the active cell does not move downward. We don't see anything there. I'll double-click and I think you can see what's happening here.

Remember, as I'm typing this, today's date, this value right here, is November 29th. Is that greater than this entry that's over in B3? Yes it is. So, this is the due date, that's overdue because we're at a later time. A real simple formula to use. Here too, as with the previous example, if I open and close this worksheet day after day after day, now some of these bills along the way might have been eliminated. Maybe they were paid, maybe they were dealt with, but the point is, at any given time we're making it check here.

If, for example, one of these due dates is tomorrow's date and for me, right now, that would be November 30th. Right now, that's not over due. Based on the logic that I used, when I open this file tomorrow, it will not be overdue. But if I open it on December first, yes that will be overdue. At different times you might want to consider is this overdue on the due date, probably not in most cases, so we wouldn't put in an equal sign there, I mean greater than or equal to, just a simple greater than will work here.

I've seen different ways of using this. I think coming up with a good example for how to use now here would be a bit strained. Do you have any situations where you work within the day and certain time has to be within a certain hour, that's where possibly you would use the function called n-o-w, since it also embeds the time of day. But I think in all these examples here, this one and the previous one, using the today function is what you're most likely to use. Keep in mind, this picks up the date from your system clock, that's what it's based on.

I've never heard of a case, in recent years, where the system clock has gone bad in any way. If your computer's working, your system clock is working. In this example right here and in the two previous examples we saw on the other worksheet, using the today function to calculate age here and to calculate service years here, makes perfect sense as we work with the datedif function along with the today function.