Inserting Current Dates and Times with Functions

Inserting Current Date & Time with Functions

Functions, as you’ve learned or can learn in our other videos, are preset formulas that give you the ability to type a function name and plug in either cell addresses or numeric values or both, and get a result. There are hundreds of functions for calculations, moving and combining text, looking up values in databases, and also, functions for inserting the current date and time, as well as calculating the number of days between two dates.

Let’s start with the simplest one, which you can use whenever you want a cell to display the current date. Just type

=TODAY() (the equal sign, the word TODAY, and an empty set of parentheses, no space between them)

This function will update each time you open the worksheet, so if you place that in a cell right now and then open the same worksheet tomorrow, the date will update.

I’ll place this formula in cells F27, 28, and 29, and we’ll use the dates later on to calculate the days left to complete three projects.

Moving on to a way to ask Excel to display the current date and time, we’ll use the NOW function.

When I type (equal sign, NOW, and then an empty set of parentheses (no space between them),

=NOW()

The current time appears as shown here, right after the current date. I’ve placed this in cell C1, so each time this worksheet is opened, the current date and time appear onscreen.

You can also do calculations with Excel’s date-related functions.

Here, we’ll use the DAYS function to calculate the number of days between two dates, by typing:

=DAYS( (equal sign, the word DAYS, and then an opening parenthesis)

I can then complete the function by either clicking on a cell containing the end date, typing a comma, and then clicking on the cell containing the start date – OR, typing the two dates, separated by a comma.

For this demonstration, I’ll use two cells containing two different dates:

Clicking in cell G27, I’ll type:

=DAYS( (the equal sign, the word DAYS, and an opening parenthesis)

And then I’ll click in cell E27, type a comma, and click in cell F27, which contains the TODAY function so that each day, the DAYS function we’re creating now will update to show how many days are left until the project end date.

When I type the closing parentheses and press Enter, it tells us how many days exist between today and the project end.

NOTE – you may notice a similar function, called DAYS360 – but it’s based on 360 days in a year, broken out into 12 months of 30 days each, so the results are inaccurate.

ANYWAY, calculations using times are also possible. To calculate the number of hours between two points in time, I will use a simple formula – the end time minus the start time, and add 12.

So in cell J7, I’ll calculate the hours worked by the employee in that row, typing

= and an opening parenthesis, and then click in cell I7, type a minus sign, and then click in cell H7.

After typing a closing parenthesis, I’ll type a plus sign and the number 12 and press Enter.

As this person started at 10 am and left at 6:00 pm, they worked 8 hours.

So now you know how to enter automatic dates and times, calculate the days between two dates and the number of hours and minutes between two times. There should be lots of ways for you to use these new skills!