Date and Time functions

In this tutorial I will show you the different things you can do with Excel’s Date & Time function.

How to use the Date & Time Function in Excel

Dates and times are a special type of cell in Excel. For convenience, when you type a string into a cell that uses the “/” or “-” characters and that is a valid date, automatically Excel will recognize it’s a date you entered. Also, if you want to enter a time you can do that as well by using the “:” symbol. Lastly, it’s also possible to enter a Date and Time in a single cell.

Entering Date and Time into Excel
Entering Date and Time into Excel

By default Dates are in US Format, however this also depends on your Windows regional settings. The US date format looks like this:

(Month/Day/Year)

YEAR, MONTH and DAY Function in Excel

If you have a date entry in your Excel workbook you can use the YEAR, MONTH and DAY function for Excel to show you what year, month or day that date is.

To do this simply use one of the three functions and click on the cell with the date entry. For example below you can see I used the YEAR(A1) function to get the year of the date in cell A1.

Example of an Excel YEAR Function
Example of an Excel YEAR Function

DATE Function

Oftentimes you want to add some days to a specific date. This is easily doable by using + or – operators. To add or subtract a number of days to a date use this very simple formula shown below.

Adding or subtracting days from a date in Excel
Adding or subtracting days from a date in Excel

It is also possible to add or subtract a specific number of years, months and/or days using the DATE function. Simply insert the number of years, months and days you wish to add or subtract into the function.

Adding or subtracing a number of years, months and days in Excel
Adding or subtracing a number of years, months and days in Excel

Note that in the example above I added 1 year, 4 months and 16 days to the original date entry.

Current Date & Time

If you want to get the date and time of today, you can use the NOW Function. If you only want the current date (without the time) use the TODAY function.

Use the NOW function to get the current date and time
Use the NOW function to get the current date and time

And with this little trick you can also get the only the current time:

=NOW()-TODAY()

HOUR, MINUTE and SECOND Function

If you have a date entry in your Excel workbook you can use the HOUR, MINUTE and SECOND functions for Excel to show you what hour, minute or second it is.

In the following example I use the HOUR(A1) function to get the hour of the date and time in cell A1.

Use the HOUR function to have Excel show you what hour it is
Use the HOUR function to have Excel show you what hour it is

TIME Function in Excel

To add or subtract a number of hours, minutes and/or seconds use this very simple function shown below.

Use the TIME function to add hours, minutes, and seconds to a time entry
Use the TIME function to add hours, minutes, and seconds to a time entry

Note that in the example above I added 2 hours, 10 minutes and 45 seconds to the original time entry.

For doubts or questions, feel free to contact me in the comment section below and I will answer you as quickly as possible.

  • Gareth Rees

    another useful function when working with time. if you want to show a time that is more than 24 hours (e.g. 36 hours excel will default to 01/01/1900 12:00:00 to show it as 36:00 go to format cell and enter a custom format as [hh]:mm