Calculate the time difference between two dates in Excel and Google Sheets

Try using mapflo for data analysis. It’s great for joining datasets. Get Started for Free.

Summary

Two good ways to calculate the time difference between two dates in Excel and Google Sheets are:

• DATEDIF function
• Use Math formula or convert dates/times to a serial number

DATEDIF

DATEDIF function calculates the time between two dates. You can choose whether to output in Years, Months or Days.

The syntax is =DATEDIF(start_date, end_date, time_unit)

where start_date and end_date can be:

• a cell reference, e.g. =DATEDIF(A2,B2,”y”)
• a string, e.g. =DATEDIF(“1/8/2020″,”1/9/2020″,”d”) [note whether the strong is perceived as dd/mm/yyyy or mm/dd/yyyy depends on your regional settings, set at a system level on a Mac and within Excel on Windows]
• a serial number e.g. =DATEDIF(“44700″,”44750″,”d”)

Other important notes:

• the start date must be before the end date or a #NUM! value will be returned
• the return value is complete whole days, months, years

time_unit can be “d” for days; “m” for months or “y” for years.

Maths formula using serial Numbers

All dates have a corresponding serial number (where 1 = 1st Jan 1900)

You can change the format of date values to a serial number using format in the Home ribbon > Number.

Regardless of how a date is displayed you can use simple maths operators such as + and – to find the difference in days between two dates.

Note: this avoids the issue of the start_date having to be before the end_date as the output can be negative. However, the output will always be in days and can include decimals. If you are looking for complete months or years than DATEDIF is a better option.

mapflo makes working out time difference between dates really easy

mapflo uses the date_diff function to calculate time between two dates. Unlike

If your two dates are ISO dates then you can use a Math Node with the formula:

formula: date_diff(${Col:date1},${Col:date2},”time unit”)

Where time unit can be “Years”; “Months”; “Days”; “Hours”; “Minutes”; “Seconds”.