Date and time in Excel
Dealing with date and time in Excel doesn't usually require a complicated formula — but it can be tricky to get the exact result and format you want.
Formatting date and time in Excel
The easiest way to make sure your cell is in the right format is the dropdown box at the top of the ribbon:
For more options, select your cell(s) and press Ctrl + 1 (or right-click and choose Format Cells).
If you need a very specific format, =TEXT(cell, format) gives you almost any output you want — e.g. "Wednesday-07-2021" or "Wednesday 21-July/2021".
And from there you can get as creative as you like:
First/last working day of this month
Last working day of this month:
=IF(1<WEEKDAY(EOMONTH(TODAY(),0))<7,EOMONTH(TODAY(),0),WORKDAY(EOMONTH(TODAY(),0),-1))
Breaking that down:
- =TODAY() returns today's date, and updates automatically every day the file is opened.
- =EOMONTH(TODAY(),0) returns the last day of this month.
- =WEEKDAY(date) returns a number from 1–7 for the day of the week (1 = Sunday, 2 = Monday, ... 7 = Saturday).
In short: if the last day of the month falls Monday–Friday, return it as-is. If it falls on a weekend, return the previous working day.
Day/time difference
Difference in years/months
=DATEDIF(start date, end date, years/months/days) calculates the difference between two dates. A #NUM error means either the start date is after the end date, or you didn't specify "y", "m", or "d".
Difference in days
DATEDIF looks like the obvious choice for day differences, but it's not perfect once time is involved — 10:00am to 9:59am the next day isn't quite one full day, yet DATEDIF will still return 1.
The more reliable approach is subtracting start date from end date directly and wrapping it in INT, which returns the integer part of a number without rounding — =INT(1.99999) returns 1.
Difference in time
To calculate the difference between two times, subtract start time from end time. The trick is understanding the result:
By default the result stays in time format, but you can switch it to Number format. Once you do, remember that in Excel, 1 as a number represents one full day — time is converted to a number as a fraction of 24 hours.
####### error
When the end time is before the start time, subtracting in time format gives a ######## error. Converting the result to number format removes it — =(B2-A2)*24 will simply return -0.50.
Various formats
| Start | End | Result | Formula |
|---|---|---|---|
| 9:00 | 16:59 | 7:59 | =End − Start |
| 9:00 | 16:59 | 7.98 hrs | =ROUND((End−Start)*24,2)&" hrs" |
| 9:00 | 16:59 | 479 min | =ROUND((End−Start)*24*60,2)&" min" |
| 9:00 | 16:59 | 7 hours 59 minutes | =TEXT(End−Start,"h"" hours ""m"" minutes""") |
Adding time/hours together
Adding a number of hours to a start time isn't as simple as it looks — the intuitive approach doesn't work:
No matter what you add, the result stays 9:00. Since Excel converts time to a fraction of 24 hours, 9:00 + one full day is still 9:00. The fix is converting hours to a fraction of a day first:
And this works even when dates are included in the start time:
Converting date to number
Dates convert to numbers the same way time does. 01/01/1900 is the earliest valid date in Excel — enter anything earlier and Excel forces it to 0/01/1900, which isn't a real date:
Excel converts dates to numbers by counting days from 01/01/1900 — with 01/01/1900 itself being 1, 02/01/1900 being 2, and so on.
Have a date or time calculation we haven't covered? Let us know and we'll add it.
Get in touch