Excel deference between two dates in year month and days in mac 2016

[Arun_excel]

Hi,

How to get deference between two dates in mac excel 2016 i tried but it is not working accurately

Refer screenshot

Ex: date - date2 =( 2 years 4 months 11 days) in one cell

 

[DataBlake]

you get the days when you subtract dates, but make sure your cells are formatted as dates prior and the cell with the formula is formatted as general.
you can also use a REALLY old function from lotus thats =datedif(B1,A1,"Y") to get things like the difference in years, etc note that this method isn't always accurate

to combine it in the format you would have to have a rather complicated formula
you can calculate years and months rather easily, but getting days is hard because of 29 through 31 and also leap years.
heres a more accurate article

Calculate the difference between two dates - Microsoft Support

How to calculate the number of days, months, or years between two dates using the DATEIF function in Excel.

support.office.com

 

[juddaaaa]

Try this

=TEXT(A1, "dd/mm/yyyy") & " - " & TEXT(A2, "dd/mm/yyyy") & " = (" & DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " & DATEDIF(A1,A2,"md") & " days)"

 

[Aureliusz Nowakowski]

In a new cell, type in =DATEDIF(A1,B1,”Y”). The “Y” signifies that you'd like the information reported in years. This will give you the number of years between the two dates. To find the number of months or days between two dates, type into a new cell: =DATEDIF(A1,B1,”M”) for months or =DATEDIF(A1,B1,”D”) for days.

 

[juddaaaa]

Book1
	A	B	C	D	E	F	G
1
2		01/06/2017	to	11/01/2020	=	2 years, 7 months, 10 days
3
Sheet1

Cell Formulas
Range	Formula
D2	D2	=TODAY()
F2	F2	=DATEDIF(B2,D2,"y") & " years, " & DATEDIF(B2,D2,"ym") & " months, " & DATEDIF(B2,D2,"md") & " days"

 

[Arun_excel]

I am working with mac, in that i am not able to find dateif function

 

[mikerickson]

This worked on my Mac
=MID(TEXT(ABS(A2-A1), "y ""years"" m ""months"" d ""days"""), 2, 255)

 

[Arun_excel]

Thq @mikerickson close but not accurate.. Check this

Ex: 13/01/20 - 29/01/18 it is showing 1 year 12 months and 14 days... Where as it should show 11 months

 

[mikerickson]

Between Jan 29, 2018 and Jan 13, 2020 there is 1 year, 11 months and 17 days.

And yes, my formula doesn't work, for a variety of reasons, mostly because the date represented by 0 is in month 1 not month 0.

But also the system of counting time intervals in months, is inconsistent due to the lengths of months differing.

Yep, my formula doesn't work.