Date and Time Functions in Excel: Complete Guide

Date and time functions in Excel are probably the most avoided topic in the whole program, and honestly, that makes sense. You open a formula, it returns a five-digit number instead of a date, and suddenly you close the laptop and make a cup of tea. But here is the thing: once you understand one simple truth about how Excel handles dates, everything else clicks into place almost instantly.

This guide walks you through that truth first, then builds up to the formulas that actually save you time — from calculating how long someone has worked at your company, to automatically flagging overdue invoices, to finding a project deadline 30 working days from now without counting a single calendar square.

By the end of this lesson, you will understand how Excel stores dates, which functions to use for which situations, and how to combine them to build practical tools that update themselves every single day.

How Excel Really Stores Dates (The Serial Number Secret)

Here is the thing that trips up almost everyone. When you type a date into Excel, Excel does not see it the way you do. You see January 1, 2024. Excel sees the number 45292.

Every single date in Excel is actually a serial number. The count started on January 1, 1900, which Excel calls day 1. Every day after that adds one more to the count. Think of it like a ticker — you and Excel are looking at the same ticker, but you see the label and Excel sees the number underneath.

Why January 1, 1900 Matters

This starting point explains all the weird stuff that happens with dates. If a cell suddenly shows a big number like 45292 instead of a date, your data is not broken. Excel is just showing you the serial number because the cell is formatted as a number instead of a date. A quick format change fixes it every time.

How This Affects Your Formulas and Formatting

Because dates are numbers, maths works on them perfectly. Subtract one date from another and you get days. Add 30 to a date and you get the date 30 days later. This is the logic behind almost every Excel date formula in existence. Once it clicks, the formulas stop being mysterious.

Essential Date and Time Functions in Excel: Quick Reference

Getting Today’s Date: The Excel TODAY and NOW Functions

Two functions, both written with empty brackets, both incredibly useful. They sound almost too simple, but these two are the engine behind most real-world date tracking in Excel.

TODAY() — Dynamic Date Stamps

Type =TODAY() into any cell and press Enter. Excel fills in today’s date. Close the file, reopen it tomorrow, and the date has updated. You do not have to touch anything. This is what makes TODAY() the backbone of deadline trackers, age calculators, and overdue payment flags.

NOW() — Adding the Current Time

=NOW() works exactly like TODAY() but adds the current time down to the minute. Use it when you want a full timestamp — logging when a record was last edited or when a file was submitted.

When to Use Each

If you only care about the date, use TODAY(). If you need both date and time in one cell, use NOW(). Both update automatically, so keep that in mind if fixed timestamps matter in your workbook.

Building and Breaking Apart Dates

Sometimes your data does not come in a tidy date format. Maybe the year is in one column, the month in another, and the day in a third. Or maybe you have full dates but you only need the month number for a report. Excel handles both directions easily.

The DATE() Function — Constructing a Date from Parts

=DATE(year, month, day) takes three separate numbers and assembles them into a proper Excel date. If your spreadsheet has the year in column A, the month in column B, and the day in column C, write =DATE(A2, B2, C2) and you get a fully usable date in one cell.

YEAR(), MONTH(), DAY() — Extracting Date Components

Going the other way, =YEAR(A2), =MONTH(A2), and =DAY(A2) each pull one part out of a full date. If A2 contains 15/03/2024, then =MONTH(A2) returns 3. This becomes useful when grouping data by month in reports and pivot tables.

Practical Example: Pulling Month from an Invoice Date

Say column A has invoice dates and you want to see which month each one belongs to. In column B, write =MONTH(A2) and drag the formula down. You now have a clean number from 1 to 12 for every row, ready to use in filters, groupings, or charts.

How to Calculate Days Between Dates in Excel

You have probably needed this before — how many days until the deadline? How long has this client been waiting? Excel gives you three ways to get the answer, and the right one depends on what you actually need.

Simple Subtraction — The Fastest Method

Because dates are serial numbers, you can just subtract them. If A2 is a start date and B2 is an end date, write =B2-A2. Excel returns the number of days between them. One small thing: format the result cell as a Number, not a Date, or Excel will try to turn your day-count into a date and display something confusing like 30/01/1900.

The DAYS() Function

=DAYS(end_date, start_date) produces the same result as subtraction but reads more clearly in a formula. Note the order: end date first, then start date.

DATEDIF — Excel’s Hidden Age and Tenure Calculator

Trust me on this one. DATEDIF is the most useful date function most people have never heard of. It does not appear in Excel’s autocomplete suggestions because Microsoft never officially documented it. But it works in every modern version of Excel, and nothing else does what it does.

Which Method Should You Use?

If you need a plain count of days, subtraction or DAYS() is fine and faster to write. If you need years, months, or a combination of both, DATEDIF is the one.

WORKDAY and NETWORKDAYS — Skip Weekends Like a Pro

You have probably seen someone calculate a project deadline by counting squares on a paper calendar, crossing out Saturdays and Sundays, squinting at bank holiday lists. That is exactly the kind of thing Excel exists to eliminate.

WORKDAY Function in Excel — Calculate Future Business Dates

=WORKDAY(start_date, days, holidays) returns the date that falls a given number of working days after the start date. Weekends are excluded automatically. Add a list of holiday dates as the optional third argument and those are excluded too.

NETWORKDAYS — Count Working Days Between Two Dates

=NETWORKDAYS(start_date, end_date, holidays) counts how many working days sit between two dates. Both the start and end dates are included in the count. Use this one when someone asks how many business days a project actually took.

Adding a Holiday List to Both Functions

Create a list of public or company holidays in a spare column. Select the range, click into the Name Box (top left, where the cell reference shows), type a name like Holidays, and press Enter. Now you can use that name inside WORKDAY or NETWORKDAYS instead of a raw range reference. It is cleaner and easier to update.

Real-World Example: 30-Business-Day Delivery Deadline

A project coordinator receives a signed contract on 01/05/2024. The team needs to deliver in 30 working days. UK bank holidays are stored in F2:F12. The formula is:

Shifting Dates Forward and Backward

EDATE() — Move a Date by Exact Months

=EDATE(start_date, months) shifts a date forward or backward by the number of months you enter. Use a positive number to go forward, a negative number to go backward. If a subscription starts on 15/01/2024 and renews every six months, write =EDATE(A2, 6) and get 15/07/2024 automatically.

EOMONTH() — Jump to End of Any Month

=EOMONTH(start_date, months) returns the last day of a month. =EOMONTH(A2, 0) gives you the last day of the same month as A2. =EOMONTH(A2, 1) gives you the last day of the following month. A favourite for finance teams managing month-end cut-offs.

Use Case: Subscription Renewal and Billing Cycle Automation

A business tracks software licences, each starting on a different date. In column B, =EDATE(A2, 12) calculates every renewal date automatically. No one has to remember. No dates slip through. The spreadsheet handles it.

Time Functions in Excel — Going Beyond Dates

Excel handles time using the same serial number logic as dates, just with decimal fractions. A full 24-hour day equals 1. So one hour equals 1 divided by 24, which is about 0.04167. Time values live between 0 and 1 as decimals. In practice, this means you can add and subtract time the same way you work with dates.

TIME(), HOUR(), MINUTE(), SECOND()

=TIME(hour, minute, second) builds a time value from three numbers, the same way DATE() builds a date. =HOUR(A2), =MINUTE(A2), and =SECOND(A2) pull individual pieces back out.

Combining Date and Time in One Cell

If A2 contains a date and B2 contains a time, =A2+B2 gives you a combined date-time value. Format the cell as dd/mm/yyyy hh:mm to display both pieces clearly in one cell.

TIMEVALUE() — Converting Text Times to Decimals

When you import data from another system, times often arrive as text strings like “14:30” rather than proper time values. =TIMEVALUE("14:30") converts the text into a proper decimal time value that you can use in formulas.

Common Date Formula Errors and How to Fix Them

#VALUE! — Type Mismatch with Dates

#VALUE! usually means Excel is trying to do date maths on a cell that looks like a date but is actually stored as text. Select the column, go to Data → Text to Columns, click Finish, and let Excel reparse the values as real dates.

#NUM! — Invalid DATEDIF Arguments

#NUM! inside a DATEDIF formula almost always means the start date is later than the end date. Check the order, and add IFERROR to display something readable: =IFERROR(DATEDIF(A2,B2,"Y"),"Check date order")

Dates Showing as Numbers — Format Fix

If a date formula returns 45292 instead of a date, the cell format is set to Number. Press Ctrl + 1 to open Format Cells, choose Date, and pick the display format you want. You can find a full walkthrough of cell formatting in Lesson 5: Cell Formatting in Excel.

Regional Format Conflicts (dd/mm vs mm/dd)

05/06/2024 means June 5 to someone in the UK and May 6 to someone in the US. When building spreadsheets for international use, always use =DATE(year, month, day) to construct dates explicitly — there is no ambiguity when year, month, and day are separate arguments.

Putting It All Together — 3 Real-World Excel Date Projects

Project 1 — HR Employee Tenure Dashboard

Column A has employee names, Column B has start dates. In column C, show full tenure:

In column D, flag five-year anniversaries:

This is exactly where date formulas and IF logic work brilliantly together. See Lesson 10: The IF Function in Excel for a full guide to building conditions like this one.

Project 2 — Project Deadline Tracker with WORKDAY

Column A has project names, B has contract dates, C has agreed business days. Column D calculates the deadline automatically:

Change the contract date or the number of days and the deadline recalculates instantly. No manual date-counting, ever.

Project 3 — Invoice Overdue Flag with TODAY and IF

Column A has invoice numbers, B has issue dates, C has due dates (=B2+30 for 30-day terms). Column D flags overdue invoices:

Every time the file opens, Excel checks today’s date against every due date and updates every flag automatically.

Frequently Asked Questions

What is the DATEDIF function in Excel?

DATEDIF calculates the difference between two dates expressed as complete years, months, or days. It is undocumented but works in all modern Excel versions. The syntax is =DATEDIF(start_date, end_date, unit).

How do I calculate someone’s age in Excel?

Use =DATEDIF(date_of_birth, TODAY(), "Y"). This returns their current age in complete years and updates automatically every day.

Why is my date showing as a number in Excel?

The cell is formatted as Number instead of Date. Press Ctrl + 1, select Date from the category list, and choose your preferred format.

What is the difference between WORKDAY and NETWORKDAYS?

WORKDAY calculates a future or past date based on a count of working days. NETWORKDAYS counts the working days that exist between two existing dates. Both accept an optional holiday list.

Can I combine date and time in one cell in Excel?

Yes. If you have a date in one cell and a time in another, simply add them together with =A2+B2. Format the result cell as dd/mm/yyyy hh:mm to display both clearly.