Understanding Time and Date Excel Functions

Time and date functions in Excel are some of the most useful tools in the program. Any time your data involves a deadline, a shift, a payment term, or a timeline, these functions let you calculate it instead of counting on a calendar.

They show up everywhere in business. Project managers use them to count business days until a launch. Payroll teams use them to turn clock-in and clock-out times into hours worked. Finance teams use them to find month-end dates and due dates, and HR uses them to calculate tenure and service anniversaries.

This guide explains how Excel stores dates and times, walks through the most important date functions and time functions with examples you can copy, and then puts them together in practical formulas for real tasks. It finishes with common errors and how to fix them, plus a free calculator that shows the exact formula behind every answer.

How Excel Stores Dates and Times

The single most important idea in this guide is that Excel stores every date as a whole number and every time as a fraction. What you see on screen is only formatting.

Each date is a serial number that counts days from a fixed starting point in Excel's calendar, where serial number 1 is January 1 of the first year Excel recognizes. Every day after that adds 1. So when you see a date in a cell, Excel is really holding a number in the tens of thousands, such as 45,000, and simply displaying it as a date.

Times work the same way, as a portion of a single day. Noon is 0.5 because it is halfway through the day. 6:00 AM is 0.25, and 6:00 PM is 0.75. One hour is 1/24, or about 0.0417, and one minute is 1/1440.

A date with a time is simply both together. A value of 45,000.75 means day 45,000 at 6:00 PM.

Why This Matters

Because dates and times are numbers, you can do math with them directly. Subtracting one date from another gives the number of days between them. Adding 15 to a date moves it 15 days forward. Subtracting a clock-in time from a clock-out time gives the fraction of a day worked, and multiplying by 24 turns that fraction into hours.

It also explains many strange results. If a formula suddenly shows 0.34375 or 45,120 instead of a time or date, nothing is broken. The cell just needs a time or date format, which you can apply from the Number Format box on the Home tab. Microsoft's date and time functions reference lists every function in this family.

Key Date Functions in Excel

The table below summarizes the date functions you will use most often. In the examples, cell A2 holds a start date of Friday, March 15, and B2 holds an end date.

TODAY

=TODAY() returns the current date and takes no arguments. It recalculates whenever the workbook opens or changes, so it is ideal for anything that should always be measured from today, such as days until a deadline or how overdue an invoice is.

For example, if a project deadline is in C2, =C2-TODAY() returns the number of days left. A negative result means the deadline has passed. Microsoft documents the details on its TODAY function page.

DATE

=DATE(year, month, day) builds a real date from three separate numbers. It is especially useful when a system exports the year, month, and day in different columns, or when you need to construct a date inside a formula.

If C2 holds the year, D2 holds 12, and E2 holds 25, then =DATE(C2,D2,E2) returns December 25 of that year. DATE also handles overflow gracefully. A month of 13 rolls into January of the next year, and a day of 0 returns the last day of the previous month.

YEAR, MONTH, and DAY

These three functions work in the opposite direction. They take a date and return one piece of it as a number. With March 15 in A2, =MONTH(A2) returns 3 and =DAY(A2) returns 15, while =YEAR(A2) returns the four-digit year.

They are most useful for grouping and summarizing. Add a helper column with =MONTH(A2) and you can total sales by month with SUMIF or a PivotTable.

EOMONTH and EDATE

=EOMONTH(start_date, months) returns the last day of the month that falls a given number of months from the start date. With March 15 in A2, =EOMONTH(A2,0) returns March 31 and =EOMONTH(A2,1) returns April 30. Use a negative number to go back in time. It is the standard way to find month-end reporting dates and to calculate "net end of month" payment terms.

=EDATE(start_date, months) returns the same day of the month a given number of months later, such as a contract renewal or the next quarterly review. =EDATE(A2,3) returns June 15. If the target month is shorter, EDATE uses its last day, so January 31 plus one month becomes the end of February.

WEEKDAY

=WEEKDAY(date, return_type) tells you which day of the week a date falls on. With a return type of 2, Monday is 1 and Sunday is 7, which makes weekend checks easy: =WEEKDAY(A2,2)>5 returns TRUE for Saturday and Sunday. If you want the name instead of a number, =TEXT(A2,"dddd") returns "Friday".

NETWORKDAYS and WORKDAY

=NETWORKDAYS(start_date, end_date, [holidays]) counts the working days between two dates, including both the start and end date, and skips Saturdays, Sundays, and any holiday dates you list in a range. A 31-day month that starts on a Monday contains 23 weekdays, so NETWORKDAYS returns 23 before holidays are removed.

=WORKDAY(start_date, days, [holidays]) answers the reverse question: what date is a given number of working days away? Starting on Friday, March 15, =WORKDAY(A2,10) returns Friday, March 29, because it skips two weekends.

If your team works a different weekend, such as Sunday only, use NETWORKDAYS.INTL or WORKDAY.INTL, which let you choose which days count as the weekend.

DATEDIF

=DATEDIF(start_date, end_date, unit) returns the number of complete years ("Y"), months ("M"), or days ("D") between two dates. It is the most accurate way to calculate age or length of service. It does not appear in Excel's function autocomplete, but it works in every modern version.

The start date must come before the end date, or DATEDIF returns #NUM!. Microsoft also advises against the "MD" unit because of known inaccuracies, as noted on its DATEDIF function page. Stick with "Y", "M", "YM", and "D".

Key Time Functions in Excel

Time functions follow the same rules as date functions, only on the fractional part of the number. In the examples below, cell B2 holds the time 2:30:45 PM.

NOW

=NOW() returns the current date and time together. Like TODAY, it updates every time the workbook recalculates, so it is useful for live dashboards but not for recording when something happened. If you need a timestamp that stays fixed, press Ctrl and semicolon for the date or Ctrl, Shift, and semicolon for the time, which enters a static value instead of a formula.

TIME

=TIME(hour, minute, second) builds a time from three numbers. =TIME(14,30,0) returns 2:30 PM. It is useful for adding a fixed amount to a time. For example, =B2+TIME(0,45,0) adds 45 minutes to the time in B2.

HOUR, MINUTE, and SECOND

These functions pull one piece out of a time. With 2:30:45 PM in B2, HOUR returns 14, MINUTE returns 30, and SECOND returns 45. HOUR always uses the 24-hour clock, which makes it easy to flag shifts that start after a certain time, such as =HOUR(B2)>=18 for evening shifts.

TIMEVALUE

=TIMEVALUE(time_text) converts a time written as text into a real Excel time. =TIMEVALUE("2:30 PM") returns 0.604166667, which is 14.5 hours divided by 24. It is the fix when times imported from another system refuse to calculate because Excel is treating them as text.

TEXT for Custom Formats

=TEXT(value, format_text) displays a date or time in any format you choose, which is ideal for labels, reports, and combining dates with words. =TEXT(A2,"mmmm d") returns "March 15", and ="Due "&TEXT(A2,"ddd, mmm d") returns "Due Fri, Mar 15". Keep in mind that the result is text, so use it for display rather than for further date math.

Free Excel Date and Time Calculator

The calculator below puts all of these functions to work on your own dates. Enter a start date and end date, up to three holidays, and a shift with a clock-in time, clock-out time, and unpaid break. Each row shows the question, the exact Excel formula, and the answer.

When you click Copy to Excel, your inputs land in column B and every result is pasted as a live formula, along with a readable version and the formula text itself. It is a quick way to build a working reference sheet you can reuse.

Practical Examples of Date and Time Formulas

The real power of these functions comes from combining them. Here are five everyday tasks and the formulas that solve them.

Example 1: Calculating Age or Length of Service

Suppose employee start dates are in column A and you want each person's years of service. The accurate formula is =DATEDIF(A2,TODAY(),"Y"), which returns the number of complete years.

You may see =INT((TODAY()-A2)/365) recommended elsewhere. It is close, but it ignores leap days, so it can be off by one on or near an anniversary. To show years and months together, use =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months".

Example 2: Adding or Subtracting Days

To find the date 15 days from today, use =TODAY()+15. To find the date 30 days before an invoice date in A2, use =A2-30. If you need 15 business days instead of calendar days, switch to =WORKDAY(TODAY(),15) so weekends are skipped.

Example 3: Calculating Hours Worked

If clock-in times are in B2 and clock-out times in C2, =C2-B2 returns the time worked as a fraction of a day. Multiply by 24 to convert it to decimal hours for payroll: =(C2-B2)*24. An employee who works from 8:30 AM to 5:15 PM gets 8.75 hours.

Subtract unpaid breaks in the same formula. With break minutes in D2, =(C2-B2)*24-D2/60 turns that same shift with a 30-minute break into 8.25 hours.

Overnight shifts need one extra step. A shift from 10:00 PM to 6:00 AM produces a negative number, because 6:00 AM is a smaller fraction than 10:00 PM. Wrapping the subtraction in MOD fixes it: =MOD(C2-B2,1)*24 returns 8 hours. For more help with payroll conversions, see our guide to converting hours and minutes to decimal in Excel.

Example 4: Finding the Difference Between Two Dates

For a project that starts on the date in A2 and ends on the date in B2, =B2-A2 returns the number of calendar days between them. Add 1 if you want to count both the first and last day.

For working days, use =NETWORKDAYS(A2,B2,H2:H10) with your company holidays listed in H2 through H10. For complete months, use =DATEDIF(A2,B2,"M").

Example 5: Building a Dynamic Timeline

A project tracker becomes far more useful when it updates itself. List each milestone with its due date in column B, then add a status column with =IF(B2<TODAY(),"Overdue",IF(B2-TODAY()<=7,"Due this week","On track")).

Pair that with conditional formatting, coloring overdue rows red and rows due this week amber, and the timeline refreshes every time the file opens, with no manual updates. Our guide to Excel conditional formatting walks through the setup.

Common Date and Time Errors and How to Fix Them

A cell full of ##### signs usually means the column is too narrow to show the date. Widen the column. If widening does not help, the result is a negative date or time, often from subtracting a later time from an earlier one, and MOD or a corrected order will fix it.

A #VALUE! error usually means one of the "dates" is actually text. This is common with data exported from other systems. Use DATEVALUE or TIMEVALUE to convert it, or select the column and use Data, then Text to Columns, and finish the wizard to force Excel to re-read the values.

If a date formula returns a plain number such as 45,382, the math is correct and only the format is wrong. Apply a Date format to the cell. And if a total of hours stops at 24 and starts over, format the cell with the custom format [h]:mm, where the square brackets let hours run past a full day.

Tips and Best Practices

Understand the format behind every date. Because dates are numbers, a cell can look right and still calculate wrong if it contains text. A quick test is =ISNUMBER(A2), which returns TRUE for a real date.

Use absolute references when a formula points to a fixed cell, such as a holiday list or a report date. Writing $H$2:$H$10 keeps the reference from shifting when you copy the formula down a column.

Keep TEXT for display. It is perfect for labels and reports, but it turns a date into text, so do the math first and format last.

Combine functions freely. Most real-world problems need two or three functions working together, such as TODAY with DATEDIF for tenure, or WORKDAY with a holiday range for due dates. Once you understand that everything is a number, these combinations become natural.

When to Move Beyond Spreadsheets

Date and time formulas are excellent for analysis, but using them to run payroll time tracking by hand gets risky as a team grows. One mistyped clock-out time or a missing MOD on an overnight shift can quietly change a paycheck.

Updoot is business management software that records clock-ins and clock-outs automatically, calculates hours and overtime, and ties time to projects and invoices, so the math is done for you. It costs $5 per user per month, and you can start a free trial when your spreadsheet starts to feel fragile.

Final Thoughts

Excel's time and date functions are powerful because they are built on one simple idea: dates are whole numbers and times are fractions. Once that clicks, calculating deadlines, business days, tenure, and hours worked becomes straightforward.

Start with TODAY, EOMONTH, NETWORKDAYS, and simple subtraction, then add DATEDIF, WORKDAY, and MOD as your needs grow. Use the calculator above to test your own dates and copy the formulas straight into your workbook.