I had a virtual column that changes the numbe...

(SABIO Soporte) #1

I had a virtual column that changes the number every month, eg: 2,3,5,10…etc I need to have a Date column, that when a particular number is in the virtual column pull a date from another column, sum a specific day and add the future date in the accctual column:

Eg: [VColumn]=1

[Date]= Today

[automatic change date]= If [VColumn]=1 then [Date]+30 Result adding the date in the [Change date column]

Any thoughts?

(Suvrutt Gurjar) #2

Hi @SABIO_Soporte , is it correct to assume that your need is follows? If [VC]=27 and current month is August 2018,

[change date column] should compute a date of 27th September 2018

(SABIO Soporte) #3


Yes that is correct

(Suvrutt Gurjar) #4

Hi @SABIO_Soporte, You may wish to try the following expression in the [Change date column]

I have tested this expression for current month that it gives dates for the next month.

The expression will work for all months including February to add 28, 29,30 or 31 days depending on current month’s number of days till year 2027.

Again,please note that I could not test it for current month other than August 2018 as the expression uses Month(Today()) expression.

Hope this helps

=IF(OR(MONTH(Today())=1, MONTH(Today())=3, MONTH(Today())=5, MONTH(Today())=7, MONTH(Today())=8, MONTH(Today())=10, MONTH(Today())=12), DATE(Today())+( 31+[VC]-DAY(Today())),IF(AND(OR(YEAR(Today())=2020,YEAR(Today())=2024),MONTH(Today())=2), DATE(Today())+( 29+[VC]-DAY(Today())), IF(MONTH(Today())=2, DATE(Today())+( 28+[VC]-DAY(Today())),DATE(Today())+( 30+[VC]-DAY(Today())))))