Years, Months, Days Between two dates in Excel

Add to Favorites

I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel. First you will need to calculate the number of Days between the two Dates. Use the following Formaula.

=DAYS360(B3,G3)

Where B3 is the Statrting Date and G3 is the End Date

then use the following formula for the number of YEARS

=ROUNDDOWN(H3/360,0)

Where H3 is the Number of Days resulting from the previous calculation

Use the following formula for the Number of MONTHS

=ROUNDDOWN((H3 - (I3*360))/30,0)

With I3 being the previously calculated number of years

Use the following formula for the Number of Days

=H3-(I3*360) -(J3*30)

With J3 being the previously calculated months.

Don't worry that the system is based on a 360 day year and 12 equal months of 30 days, this is what makes it work without rounding errors.

I have also found a method of calculating Years, Months, Days Using The Now function to retrieve the number of days between two dates with one of the dates being the current date eg

=NOW()-B3

Whereas B3 still represents the Beginning date. But since the number of days returned is the Exact number of days since the beginning and is based on 365+Days per year with uneven months there are generally some rounding errors. Years and Months seem to come out ok but days can be off, I haven't noticed more than a 1 day difference but it could be more pronounced closer to February. I will paste those formulas below first year then month then days

=ROUNDDOWN(C3/365.24218967, 0)       

Where C3 is the location of the aforementioned NOW formula

=ROUNDDOWN(((C3-(D3*365.24218967))/30.41666667),0)

Where D3 is the calulation of the above year formula

=ROUNDDOWN(C3-(365.24218967*D3) - (E3*30.41666667),0)

Where E3 is the month Formula


Excel Function: DAYS360(), NOW(), ROUNDDOWN()

Similar Content on TeachExcel
Get the Number of Workdays Between Two Dates in Excel
Tutorial: How to calculate the total number of working days between two dates in Excel.  This allows...
Calculate the Difference Between Two Times in Excel
Tutorial: Here, youll learn how to get the difference between two times in Excel. A common example...
Dates in Excel Explained
Tutorial: I'll explain how dates in Excel work and then point you to many useful tutorials on how t...
Calculate the Difference Between Time Greater than 24 Hours in Excel
Tutorial: Ill show you how to calculate the difference between two times in Excel when that differe...
Increment a Date By X Days for Each Row in Excel
Tutorial: How to quickly increment a date by a certain number of days for each entry. Type the first...
Quickly Combine a List of Values and Put a Delimiter Between Each Value in Excel
Tutorial: How to combine a list of data into one cell while putting a delimiter between each piece ...