Excel Calculate Date Time: How-To & Examples
Excel can calculate differences between dates and times, add or subtract days, months, years, hours, and minutes, and convert between time units. You enter date/time values in cells and use built-in functions like DATEDIF, NETWORKDAYS, and simple arithmetic to get the result you need. This guide explains the essential formulas, common pitfalls, and how to format your results correctly.
How Excel Stores Dates and Times
Excel stores dates as sequential serial numbers, starting with January 1, 1900, as serial number 1. For example, January 1, 2025, is serial number 45658. Times are stored as decimal fractions of a day: 6:00 AM is 0.25, 12:00 PM is 0.5, and 6:00 PM is 0.75. A full date and time, such as January 1, 2025, 6:00 AM, is stored as 45658.25.
This system is what makes date and time arithmetic possible. When you subtract one date from another, Excel returns the number of days between them. When you subtract one time from another, you get a fraction of a day, which you can then convert to hours or minutes.
Basic Date Difference: Days Between Two Dates
The simplest calculation is the number of days between two dates. If cell A1 contains a start date and B1 contains an end date, the formula is:
=B1-A1
Format the result as a number (not a date). For example, if A1 is 1/1/2025 and B1 is 1/10/2025, the result is 9. To include both the start and end days, add 1: =B1-A1+1.
For more complex differences, use the DATEDIF function (date difference). The syntax is:
=DATEDIF(start_date, end_date, unit)
Units include:
- “D” – complete days
- “M” – complete calendar months
- “Y” – complete years
- “YM” – months remaining after full years
- “YD” – days remaining after full years
- “MD” – days remaining after full months
For instance, =DATEDIF(A1,B1,"Y") returns the number of full years between the two dates.
Calculating Time Differences
To find the difference between two times, subtract the earlier time from the later time. If A1 is 9:00 AM and B1 is 5:30 PM, the formula =B1-A1 returns 0.3541667 (8.5 hours as a fraction of a day). To display it as hours and minutes, format the cell as h:mm – it will show 8:30.
If you want the result as a decimal number of hours, multiply by 24: =(B1-A1)*24 gives 8.5. For minutes, multiply by 1440 (24*60): =(B1-A1)*1440 gives 510.
A common issue: if the time range crosses midnight (e.g., from 10:00 PM to 6:00 AM), a simple subtraction yields a negative value. Use the MOD function to handle this:
=MOD(B1-A1,1)
This returns the correct positive fraction (0.3333 for 8 hours).
Adding and Subtracting Dates and Times
To add days to a date, simply add a number to the date cell. For example, =A1+30 gives the date 30 days after A1. To add months or years, use the EDATE function:
=EDATE(start_date, months)
For instance, =EDATE(A1,3) returns the date three months after A1. For more advanced adjustments (e.g., add 1 year, 2 months, and 5 days), you can combine functions:
=DATE(YEAR(A1)+1, MONTH(A1)+2, DAY(A1)+5)
For times, add a fraction of a day. To add 3 hours, use =A1+3/24. To add 45 minutes, use =A1+45/1440. Because Excel treats a day as 1, any time amount must be expressed as a fraction of 24 hours.
Working with Business Days and Workdays
To calculate the number of working days between two dates (excluding weekends), use NETWORKDAYS:
=NETWORKDAYS(start_date, end_date)
This counts all days from start to end, excluding Saturdays and Sundays by default. To also exclude holidays, add a range of holiday dates as a third argument:
=NETWORKDAYS(A1,B1,HolidayRange)
To add working days to a date (e.g., find the date 10 business days from today), use WORKDAY:
=WORKDAY(start_date, days)
Both functions have international versions (NETWORKDAYS.INTL and WORKDAY.INTL) that let you specify which days are weekends.
Formatting Results Correctly
When you calculate a date difference, Excel may automatically format the result as a date, which looks wrong (e.g., 1/9/1900 instead of 9). To fix this, select the cell, open Format Cells (Ctrl+1), and choose Number or General.
For time differences, use a custom format to display hours over 24 (e.g., 30:15 for 30 hours and 15 minutes). In Format Cells > Custom, enter [h]:mm. The square brackets prevent the hours from rolling over at 24.
For date-time combinations, use formats like yyyy-mm-dd hh:mm to show both parts clearly. If you see ##### in a cell, it means the column is too narrow – widen it or change the format.
Common Mistakes and How to Avoid Them
- Text vs. date values: If a cell contains text like “January 1, 2025” instead of a true date, arithmetic won’t work. Use DATEVALUE to convert text to a date, or re-enter as a proper date.
- Negative time values: When subtracting times that cross midnight, use MOD to avoid negative results.
- Wrong unit conversion: Remember that time differences are fractions of a day. Multiply by 24 for hours, 1440 for minutes, and 86400 for seconds.
- DATEDIF with start > end: DATEDIF returns an error if the start date is later than the end date. Check your arguments.
- Rounding issues: Floating-point arithmetic can cause tiny errors. Use ROUND if you need exact decimals.
Frequently Asked Questions
How do I calculate the age in years from a birthdate?
Use DATEDIF with the “Y” unit: =DATEDIF(birthdate, TODAY(), "Y"). This gives the number of complete years. To include months and days, use multiple DATEDIF formulas and concatenate the results.
Why does my date subtraction return a date like 1/9/1900?
That’s because the result is being formatted as a date. The serial number 9 corresponds to January 9, 1900. Change the cell format to Number or General to see the actual day count.
How can I add 8 hours to a time in Excel?
Add 8/24 to the time cell: =A1+8/24. Since 8 hours is one-third of a day, you could also use =A1+TIME(8,0,0), which is more readable.
What is the difference between NETWORKDAYS and DATEDIF?
DATEDIF counts all calendar days, months, or years between two dates. NETWORKDAYS counts only weekdays (Monday–Friday) between two dates, excluding weekends and optional holidays.
How do I calculate the difference in hours between two timestamps that include dates?
Subtract the earlier timestamp from the later one and multiply by 24: =(B1-A1)*24. Format the result as a number with decimals. If the timestamps are in different days, the subtraction automatically accounts for the date part.
Can Excel handle dates before 1900?
No. Excel’s date system starts at January 1, 1900. For earlier dates, you’d need to use a text representation or a different system. This is a known limitation.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.