How to Add Value (integer) to Date

[#1]

Hello,

I have Date in the following format  'D/MMM/YYYY' in a field called Date i want to add 20 years in value to it. Use this scenario

(Date, 'D/MMM/YYYY') + 20 years.

Just the addition of 20 more years in integer to the Date

Regards

[its_anandrjs · Accepted solution]

Hi,

Use Addmonths function add 20 years to the current field

Ex:-

=AddMonths( Date(Date#( '28/Dec/2014' ,'D/MMM/YYYY'),'D/MMM/YYYY'),240)

Or Add the New Field in the load script

Load

Date,

AddMonths(Date,240) as YearsAdd20

From Source;

Or

Load

Date,

Date(AddMonths(Date,240),'D/MMM/YYYY') as YearsAdd20

From Source;

Regards

Anand

[#3]

Good Anand,

How do i subract a previous date with format 'D-MMM-YYYY' from today? To get the number of "Years, Month and Days" in this format example "2 years, 6 months and 2 days"

Any idea?

[its_anandrjs]

Hi,

For this you have to convert the Today() date format to 'D-MMM-YYYY' and then add new field into load script for the calculation of the Years, Month and Days. Take difference of Year then month and then for days and concatenate all fields to get single field.

Source:
LOAD *,
     (Year(Date(Today(),'D-MMM-YYYY')) - Year(DATE))&' Years '&
     (Month(Date(Today(),'D-MMM-YYYY'))- Month(DATE)+(Year(Date(Today(),'D-MMM-YYYY'))-Year(DATE))*12)&' Months and '&
     Interval(Date(Today(),'D-MMM-YYYY') - DATE,'D')&' Days '   as DateDetails     
Resident tmpSource;

Drop Table tmpSource;

And you get field with

Hope this helps

Regards,

Anand

[#5]

Hello Anand,

No this isnt what i wanna achieve.

SCENARIO:

Today() - December 1st 2000 = "14 years, and 27 days"

This is what i want to achieve

[#6]

Read this thread, its been solved already. I believe a VB function linked thru the Module is enough and you can call it from wherever you want in your document.

[#7]

Dear ,

Use Like This

  floor((Today() -Date_of_Loss)/365) &' Years ' &   floor(Frac((Today() -Date_of_Loss)/365)*12) &' Months '& frac(Frac((Today() -Date_of_Loss)/365)*12)*30 & ' Days'  as Date,

08/06/2010

4 Years 4 Months 24 Days

    Regards

Vimlesh Gupta

[#8]

Will this code work for a Leap-Year?

[#9]

Yes ,You can create Leap-year using it.