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!
Feliciad_06
Offline
Last seen: 4 years 1 month ago
Joined: 01/07/2019 - 10:56
Hi Feliciad_06,
Which version of IDEA are you using?
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 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
Thanks Ravi, you should now be able to attach py files.
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,
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,
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
Thank you Brian. Yest It works. I had been trying for the past 2 weeks and just joined the forum today and that problem is solved.
Regards,
Mkamagate
Glad I could help and welcome to IDEA. If you get stuck again post a message, hopefully someone will have already had the problem and has a solution.
Greetings,
I am tryng to identify transactions done in a weekend (SAT/SUN). Is it possible to use virtual date fields to identify weekend?
Regards,
Mkamagate
Pages