Add Months to Date
Forums
Hello,
Would anyone be able to tell me how to add a specified number of months to a date? I'm looking for a function that has the same functionality as Excel's EDate function. For example, if I have a date of 10/1/18 in one column, and a number of "11" in another number, I would like to append a column that will add 11 months to 10/1/18, to return 9/1/19.
Thanks!
Hi,
Hi,
my name is Jan .
I have been reading this forum for some time and am always impressed by the inspirations you can get here.
To solve this problem, it would be a good idea to add several fields. Another way could be smarter(maybe), but with a positive number of additional months it works.
Field-name: Month_positive_addition
Fill with your own number. >= 0
Value: numeric
Lenght: 8
Field-name: day_char
Transferring the days in two characters.
Value: character
Lenght: 2
@if(@CurVal(@CurForm(@day(DATE_OLD);"";"";2;0);"";"")<10; @insert(@CurForm(@day(DATE_OLD);"";"";2;0);0;"0"); @CurForm(@day(DATE_OLD);"";"";2;0))
field-name: month_numeric
Adding the months to the additional months.
Value: numeric
dezimale: 0
@if( @month(DATE_OLD) + MONTH_POSITIV_ADDITION>12; (@month(DATE_OLD) + MONTH_POSITIV_ADDITION) - (@int ((@month(DATE_OLD) + MONTH_POSITIV_ADDITION) / 12) *12) ; @month(DATE_OLD) + MONTH_POSITIV_ADDITION )
field-name: month_char
Transferring the month in two characters.
Value: character
dezimale: 2
@if(month_numeric <10; @insert(@CurForm(month_numeric;"";"";2;0);0;"0") ; @CurForm(month_numeric;"";"";2;0) )
Field-name: year_char
Maybe adding the years and transferring the year in four characters.
Value: character
Lenght: 4
@CurForm(@year(DATE_OLD)+@int ((@month(DATE_OLD) + MONTH_POSITIV_ADDITION) / 12);"";"";4;0)
Field-name: date_new
Combine the characters and transfer into a date.
Value: date
@Ctod( @insert(DAY_CHAR;0;@insert (YEAR_CHAR;0;MONTH_CHAR)) ;"ddmmyyyy")
I hope to find a solution despite my limited language skills...
cu
Jan
If you are using IDEA v10.3
If you are using IDEA v10.3 and above, you could use the following @Python function. Assuming your date column is called "INV_DATE" and column with number of months to add is called "MONTHS_TO_ADD", please define a new virtual field, say "NEW_DATE" as @Ctod( @Python("DateDelta", INV_DATE, 0, MONTHS_TO_ADD, 0), "YYYYMMDD"). The DateDelta function accepts 4 arguments: date, number of days, number of months, number of years. Since you want to add only months, number of days and number of years are 0. You could also use negative values in any argument to calculate a date in the past.
Please copy the attached file "DateDelta.txt" to the "Custom Functions.ILB" folder of your current project and then rename it to DateDelta.py (Hope Brian would enable uploading .py files going forward)
Regards,
Ravi
Hi everyone,
Hi everyone,
I'm using an older version of IDEA. I ended up using a combination of built-in functions and a custom function. I was always adding 12 months or less to my date, and this worked for doing that; it would not always work for adding more than a year to the date. Here's what I used:
@Ctod(#add_months(@Dtoc(DATE_FIELD,"YYYYMMDD"),#_OF_MONTHS_FIELD),"YYYYMMDD")
There certainly may be a better way to do this, but this seemed to work fine for my needs. Thank you all for your suggestions!
Hello Everyone,
Hello Everyone,
I am ussing IDEA 10.3. I am trying to my date from (MM-DD) to add the year at the end (YYYY). is there a way to conver the date format to a formal that can be read by IDEA? My current date format is 11-23 and i would like to change it to 11/23/2018. Thank you all for any advise.
Hello mkamagate,
Hello mkamagate,
So can I assume that the MM-DD is in character format? What I would do is create a new virtual date field with the following formula:
@Ctod("2018" + @left(YOUR_DATE_FIELD, 2) + @right(YOUR_DATE_FIELD, 2), "YYYYMMDD")
What I am doing above is using the character to date function, I first add on the year as a character (that is why it is in double quotes), I then attach the month to it using the @left and then the day using the @right, finally I gite it the mask which is this case is YYYYMMDD for year, month, day.
Hopefully this will help fix your problem.
Brian
Hi Feliciad_06,
Hi Feliciad_06,
Which version of IDEA are you using?