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

#### Arun_excel

##### New Member
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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### DataBlake

##### Well-known Member
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

#### juddaaaa

##### Board Regular
Try this

Rich (BB code):
``=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

##### New Member
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

##### Board Regular
Book1
ABCDEFG
1
201/06/2017to
11/01/2020
=
2 years, 7 months, 10 days
3
Sheet1
Cell Formulas
RangeFormula
D2D2=TODAY()
F2F2=DATEDIF(B2,D2,"y") & " years, " & DATEDIF(B2,D2,"ym") & " months, " & DATEDIF(B2,D2,"md") & " days"

#### Arun_excel

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

#### mikerickson

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

#### Arun_excel

##### New Member
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

##### MrExcel MVP
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.

Threads
1,095,816
Messages
5,446,661
Members
405,413
Latest member
AlainCar