Date and Time Functions in Excel: A Complete Guide

Dates and Times are very frequently used in Excel. You may be keeping an eye on a deadline, or calculating number of days between two dates, or age of a person etc. Hence, it is important to know how to handle dates and times in Excel. Without using the right date and time functions in Excel, handling those can get quite tricky. .

In this article, we will discuss in detail all the important date and time related functions. We will also give practical examples and practice questions.

——————————————————————————————————————————————————–

Understanding Dates and Times in Excel

It is important to understand two things –

1. Dates are actually stored as serial numbers in Excel. For e.g. – 1-Jan-1900 gets stored as 1, 2-Jan-1900 as 2, and so on.
2. Time is stored as a fractional value of a day. For e.g., 12:00 PM means 50% of the day is over so it gets stored as 0.5. Similarly, 6:00 AM gets stored as 0.25.

———————————————————————————————————————————————————-

Essential Date Functions in Excel

1. =TODAY()

This function returns the current date. It has following use cases –

• We can use this function to calculate the age of a person by doing =TODAY() – birth date.
• Analysts write it in formulas where the dates need to update automatically.

Example:

=TODAY() – DATE(1990,1,1)

Returns the number of days since 1st Jan 1990.

2. =NOW()

It returns the current date as well as time. We can use it when we need both date and time in your sheet to update in real-time.

3. =DATE(year, month, day)

This is an extremely useful formula. We often write dates manually in Excel. But many a times that creates confusion. For e.g., 03/04/2025 could be 3 April 2025 or 4 May 2025. Hence, we should always write dates using this formula.

Let’s say we want to write 4 August 2025 in a cell. Then write it like  this –

=DATE(2025, 8, 4)

Now this gets stored as a valid date. We can apply various date formatting like long date, short date on it and also apply various date and time related Excel formulas on it.

4. EDATE(start_date, months)

We need to provide start date and howmany months we want to add to it. It will give the desired result. For e.g. –

=EDATE(“01-Jan-2024”, 6) will return 01-Jul-2024.

5. **EOMONTH(start\_date, months)

This formula returns the adds the specified number of months to the given date and returns the end date of resulting month. For example, let’s look at the below function –

=EOMONTH(“15-Jan-2024”, 1) will return 29-Feb-2024.

6. DATEDIF(start_date, end_date, unit)

This calculates the difference between two dates. If the dates are formatted properly, we can do this via simply subtracting the two dates using the minus operator. For e.g. –

=DATEDIF(“01-Jan-2000”, “01-Jan-2025”, “y”) returns 25 (years).

We can also calculate other units as well from this formula. Instead of “y”, we need to write –

“m” – months
“d” – days
“ym” – months ignoring years
“yd” – days ignoring years
“md” – days ignoring years and months

7. YEAR(), MONTH(), DAY()

We can extract year, month or day from a date using these formulas –

=YEAR(“04-Aug-2025”) → 2025
=MONTH(“04-Aug-2025”) → 8
=DAY(“04-Aug-2025”) → 4

8. WEEKDAY(date, [return_type])

This one can return the day of the week as a number. For e.g. –

=WEEKDAY(“04-Aug-2025”) will give us 2 (Monday as Sunday is the first day of the week).

9. WORKDAY(start_date, days, [holidays])

This formula returns adds the mentioned number of days to a date but skips the weekends. For example, let’s have a look at the below function –

=WORKDAY(“01-Aug-2025”, 5) will thus add 5 days to 1 Aug 2025 without considering the weekends. Result is 08-Aug-2025.

10. NETWORKDAYS(start_date, end_date, [holidays])

This one is obviously very useful in certain cases. It counts working days between two dates. For example –

=NETWORKDAYS(“01-Aug-2025”, “10-Aug-2025”) will returns 6 (excluding weekends).

———————————————————————————————————————————————————-

Time Functions in Excel

1. TIME(hour, minute, second)

We can record time evidently in a proper time format using this function. For e.g.-

=TIME(14, 30, 0) will return returns 2:30 PM.

2. HOUR(), MINUTE(), SECOND()

We can specifically extract parts of a time value using this one.

=HOUR(“14:45:10”) → 14
=MINUTE(“14:45:10”) → 45
=SECOND(“14:45:10”) → 10

3. TEXT() for Date and Time Formatting

It can display date / time in a custom format as per our need. For e.g. –

=TEXT(NOW(), “dd-mmm-yyyy hh:mm AM/PM”) will hence return 04-Aug-2025 04:30 PM.

You can also format:

“dddd” – Full weekday name
“mmm” – Month abbreviation
“yy” – Two-digit year

———————————————————————————————————————————————————-

Practice Questions on date and time functions in Excel

Finally, try these simple questions to test your knowledge. Let us know in comments if you get stuck anywhere –

1. Use TODAY() to calculate your age in years.
2. Find the date that is 100 working days from today using WORKDAY().
3. Use =DATEDIF() to calculate how many months are between 15-Jan-2021 and 10-Aug-2025.
4. Extract the month and weekday from 04-Aug-2025.
5. Format NOW() into dd-mm-yyyy hh:mm:ss using TEXT().

Also check out our other articles on –

How to Create Dependent Drop Down List in Excel

Excel Power Pivot for Beginners: Create Data Models Easily

Advanced Filter vs AutoFilter in Excel: What’s the Difference?

Microsoft’s Official Guide on Date and Time Functions in Excel