Difference between dates in Years, Months...

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
239
Office Version
  1. 2016
Platform
  1. Windows
Hi,
Please, help. Excel is still mystery to me.

I worked before with =DATEDIF() but I want avoid "MD" unit as it's prone to inaccurate result.
I need difference between dates as: in Years, Months, Days, hh:mm:ss, four columns and additional columns as simple =end-startDate as elapsed Days and one column as total elapsed [HH]:mm:ss.
I will only have start date, because i will use =NOW() or =Today() for end date. But, formula should work with cell values also.(Start Date(smaller), EndDate(bigger date))

I have problems with third column( DAYS). I have wrong results with formula: =DAYS(TODAY();EDATE(E4;J4)) where E4 is startDate and J4 is value from =datedif(small, big, "YM") for months.

I NEED e.g.: 15/04/2020 23:59 =NOW() 15/03/2021 16:41 0y 10m 30d 7:18 330d [1569]:26:59 <<< approximately

Thank you in advance, God Bless!
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Did you review or try any of the formulas?
Which calculation are you referring to?
The hours calculations reconcile.

=(INT(B2)-INT(A2)-1)*24+F2*24 = complete days *24 + 1 minute start date + 16 hours and 41 minutes end date
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
239
Office Version
  1. 2016
Platform
  1. Windows
I have to give up the DATEDIF function. Gotta use Year Eomonth etc, to manually calculate difference.
I have to calculate hhmmss and substract to days and days substract to months, to have: 0y 10m 29,7d 17:29 i guess.
CaptureA.JPG
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Good luck on your project.

The following shows the calculation of total hours.
Datedif.xlsm
ABCDE
1StartEndDays InclusiveHours
201-Jan-2021 00:00:0131-Dec-2021 23:59:593658,759.999
301-Jan-2020 06:00:0031-Dec-2020 18:00:003668,772.000leap year
401-Jan-202131-Dec-20213658,760.000
5
1aa
Cell Formulas
RangeFormula
C2:C3C2=INT(B2)-INT(A2)+1
D2:D3D2=(C2-2)*24+(1-MOD(A2,1))*24+(MOD(B2,1))*24+N("Total day -2 to exclude start and end days+Start hours +End hours")
C4C4=B4-A4+1
D4D4=365*24
 

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
239
Office Version
  1. 2016
Platform
  1. Windows
Thank you Dave, i'll give up, cannot calculate exact days, moths, years elapsed. :cry::cry:
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Row 2 is inclusive; the hours in the start date and the hours in the end date are included.
If a person works Monday to Friday, is that 4 days or 5 days? 5-1 =4 5-1+1= 5

Datedif.xlsm
ABCDEFG
7Mon 01-Jan-18Fri 05-Jan-180055.0120
1a
Cell Formulas
RangeFormula
C7C7=DATEDIF(A7-1,B7,"y")
D7D7=DATEDIF(A7-1,B7,"ym")
E7E7=DATEDIF(A7-1,B7,"MD")
F7F7=B7-A7+1
G7G7=F7*24


A non leap year is 365 days; a year that includes Feb 29 is 366. Should the calculation reflect 366 days?

Datedif.xlsm
ABCDEFG
1StartEndYearsMonthsDaysT DaysHH:MM:SS
215-Apr-2020 23:5915-Mar-2021 16:410111334.78008:42
315-Apr-202015-Mar-20210111335.08040
401-Jan-202030-Apr-20211330486.011664
501-Jan-202031-Dec-2020100366.08784
1a
Cell Formulas
RangeFormula
C2:C5C2=DATEDIF(A2-1,B2,"y")
D2:D5D2=DATEDIF(A2-1,B2,"ym")
E2:E5E2=DATEDIF(A2-1,B2,"MD")
F2:F5F2=B2-A2+1
G2G2=(INT(B2)-INT(A2)-1)+1-MOD(A2,1)+MOD(B2,1)
G3:G5G3=F3*24
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top