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

Arun_excel

New Member
Joined
Jan 6, 2020
Messages
4
Office Version
365, 2019, 2016, 2013, 2011, 2010, 2007
Platform
Windows, MacOS, Mobile
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
 

Some videos you may like

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
Joined
Jan 26, 2015
Messages
774
Office Version
2016
Platform
Windows
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
Joined
Jan 4, 2020
Messages
208
Office Version
365
Platform
Windows
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)"
 
Joined
Nov 22, 2019
Messages
3
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
Joined
Jan 4, 2020
Messages
208
Office Version
365
Platform
Windows
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
Joined
Jan 6, 2020
Messages
4
Office Version
365, 2019, 2016, 2013, 2011, 2010, 2007
Platform
Windows, MacOS, Mobile
I am working with mac, in that i am not able to find dateif function
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,281
This worked on my Mac
=MID(TEXT(ABS(A2-A1), "y ""years"" m ""months"" d ""days"""), 2, 255)
 

Arun_excel

New Member
Joined
Jan 6, 2020
Messages
4
Office Version
365, 2019, 2016, 2013, 2011, 2010, 2007
Platform
Windows, MacOS, Mobile
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
Joined
Jan 15, 2007
Messages
23,281
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.
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top