Excel Date and Time Functions | Excel Formula Guide

« Back Next »

Learn how to use Excel's date and time functions. Master date calculations, formatting, and functions like TODAY, NOW, and DATE for efficient data handling.

DATE functions

NOW function

The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.

TODAY

The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook.

For example, if you know that someone was born in 1963, you might use the following formula to find that person’s age as of this year’s birthday:

= YEAR(TODAY())-1963

Video Tutorial: TODAY and NOW functions in excel

Task: Extracting Day and Month from a Date in Excel

To extract the day and month from a date in Excel, you can use the following functions:

Day Function: This function extracts the day of the month from a date.
```
=DAY(date)
```
Replace date with the cell reference that contains the date (e.g., A1).
Month Function: This function extracts the month from a date.
```
=MONTH(date)
```
Similarly, replace date with the cell reference.

For example, if cell A1 contains the date 2025-02-06, you can use the formulas as follows:

=DAY(A1) will return 6.
=MONTH(A1) will return 2.

Exercise: Calculate Age

In cell A1, enter a birthdate (e.g., 01/15/1980).
In cell B1, use the TODAY function to calculate the age. Subtract the birthdate in A1 from the current date (TODAY) and divide by 365.25 (accounting for leap years). The formula in B1 should look like this:

=(TODAY() - A1) / 365.25

The result in cell B1 will be the age in years.

Exercise : Calculate Days Remaining

In cell A1, enter a future date (e.g., 12/31/2023).
In cell B1, use the TODAY function to calculate the number of days remaining until the date in A1. The formula in B1 should look like this:

=A1 - TODAY()

The result in cell B1 will be the number of days remaining until the future date.

Exercise: Write the formula to find the date of the first day of the previous month.

Type the following forumula in any cell:

=DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1)

This formula calculates the date of the first day of the previous month. The DATE() function takes three arguments: the year, the month, and the day.

In this case, we are using the YEAR() and MONTH() functions to get the current year and month, respectively. We then subtract 1 from the month to get the month of the previous month. Finally, we set the day to 1 to get the date of the first day of the previous month.

Exercise: Calculate Elapsed Time

You can use the NOW function to calculate the elapsed time between two events.

In cell A5, type “Start Time:”.
In cell B5, enter a start time (e.g., “9:00 AM”).
In cell A6, type “End Time:”.
In cell B6, use the following formula to calculate the elapsed time between the start and end times: