11 Replies Latest reply on Jun 4, 2015 12:40 AM by Mark Fraser

# Date difference in Days, Month and Year

Hello Team,

I am looking for date difference in no of Days, Month and Year.

I have two dates DOB and Ac opening date

 Cust ID DOB Ac opening date 1 01-01-1911 30-01-2011 2 08-08-1983 03-06-2015 3 01-01-1911 29-12-2010

I tried multiple things, as you will find in attached workbook.

Date Difference

Here Aaron had give a solution for Year and Month but I also want same for days.

How to get it?

Regards

Jaideep

• ###### 1. Re: Date difference in Days, Month and Year

HI Jaideep

I first made 2 new dates (using the date function), start (from DOB) and end (from AC date) date, for ease.

I then calculated the days, months, years between the 2.

Is that what you wanted of have I missed something?!

ps. it is your workbook, I just removed all your calculated fields.

Cheers

Mark

• ###### 2. Re: Date difference in Days, Month and Year

Hello Mark,

But Here I want to see the difference of Year, Month Days.

Lets take 1st record

 Cust ID DOB Ac opening date 1 01-01-1911 30-01-2011

I am expecting difference in the form of 100 Years, 0 Months and 29 Days.

• ###### 3. Re: Date difference in Days, Month and Year

Oh sorry Jaideep, I see now...

For the days: If the DOB was 30/01/1911 and the AC Open Date was 01/01/2011

You would expect 100 years, 0 months, -29 days? or 0 days?

• ###### 4. Re: Date difference in Days, Month and Year

29 Days.

• ###### 5. Re: Date difference in Days, Month and Year

I used below calculation formula.

'years: ' + str(DIV(datediff('month',[DOB],[Ac opening date]),12))

+ "      months: " +  str(datediff('month',[DOB],[Ac opening date])-12*DIV(datediff('month',[DOB],[Ac opening date]),12))

+ "      days: " +  str(datediff('month',[DOB],[Ac opening date])-30*DIV(datediff('month',[DOB],[Ac opening date]),30))

By this I am getting correct year and month but not the days.

for above example I am getting 0 days instead of 29 days.

3 of 3 people found this helpful
• ###### 6. Re: Date difference in Days, Month and Year

Hi Jaideep

Attempt 2...

Lets take 1st record

 Cust ID DOB Ac opening date 1 01-01-1911 30-01-2011

I am expecting difference in the form of 100 Years, 0 Months and 29 Days.

This is the current output, the first record matches expectations, I hope the other 2 do!

Formulas >>

Years: IF DATEPART('year',[StartDate])-DATEPART('year',[EndDate]) <0 THEN (DATEPART('year',[StartDate])-DATEPART('year',[EndDate]))*-1 ELSE DATEPART('year',[StartDate])-DATEPART('year',[EndDate]) END

Months: IF DATEPART('month',[StartDate])-DATEPART('month',[EndDate]) <0 THEN (DATEPART('month',[StartDate])-DATEPART('month',[EndDate]))*-1 ELSE DATEPART('month',[StartDate])-DATEPART('month',[EndDate]) END

Days: IF DATEPART('day',[StartDate])-DATEPART('day',[EndDate]) <0 THEN (DATEPART('day',[StartDate])-DATEPART('day',[EndDate]))*-1 ELSE DATEPART('day',[StartDate])-DATEPART('day',[EndDate]) END

The reason for the IF is to deal with negative values, if they are negative then I multiple by -1.

Workbook attached.

Let me know if we are making progress

Cheers

Mark

2 of 2 people found this helpful
• ###### 7. Re: Date difference in Days, Month and Year

Its working.

Thanks Mark.

• ###### 8. Re: Date difference in Days, Month and Year

You're welcome

Its actually easier than I originally thought... The formula identifies the individual time elements (day, month, year) and does a simple deduction between start and end. As mentioned previously anything <0 multiplied by -1 to ensure everything is positive. If you want you can concatenate into a single function/ output.

The formulas you had/ were using... I'd forget them, the way I used is much easier/ more efficient.

Cheers

Mark

• ###### 9. Re: Date difference in Days, Month and Year

When I checked your calculations.... Yes it was easier.

Initially I tried to subtract dates but I have not used DATEPART function so the results was not as expected.

Then I tried with logic

Year: str(DIV(datediff('month',[DOB],[Ac opening date]),12))

again it was not giving me the expected results.

Your formula was easy and it worked.

Thanks

Cheers

Jaideep

• ###### 10. Re: Date difference in Days, Month and Year

We already have a built-in function to calculate the date differences. Try using them for your need.

Years: DATEDIFF('year',[StartDate],[EndDate])

Months: DATEDIFF('month',[StartDate],[EndDate])

Days: DATEDIFF('day',[StartDate],[EndDate])

• ###### 11. Re: Date difference in Days, Month and Year

Hi Vasu