Use text as a date for a calculation of months from today

[BOYERWMAP]

Hello,

I have a field called [Internal Value from] with dates as text like..  20210930

I need to convert that to a date format in the script so I can calculate number of months from today.  This is the formula I'm trying to use, but it's giving me a number that appears to be the difference in the two values as a plain number, not a date.

 If([Characteristic Name Desc]='Ar Contract Expiration Date',
(((Date([Internal Value from])-Date(Today()))/30))) AS [Ar Exp Months]

Any thoughts?
Thanks!!

[JordyWegman · Accepted solution]

Hi,

You need interpreter functions like Date#(). Else the function can't reed your date stamp.

Try this:

If([Characteristic Name Desc]='Ar Contract Expiration Date',

 (

Date( Today() ) - Date( Date#( [Internal Value from], 'YYYYMMDD' ) )

)

/ 30

)

Jordy

Climber 

[BOYERWMAP]

Jordy!
Thank you so much for your reply... this allowed to work it through to the answer I needed. However it gives me a negative number instead of a positive, so it required me to multiply it by -1. If I try to reverse your formula and do Today first, I get a 5 digit number.
It also errors out if I try to put it in the script... would you expect this to work in the script? Here's my calculation for the full field... I am calculating a score based on how many months are left until their contract expires... I need to do this for 6 or 7 different products and then sum them together, so it would really help if I can get it in the script.
But you had the answer that I'd been struggling with all week, so thank you!!
Wendy
=IF(ROUND(If([Characteristic Name Desc]='N2 Contract Expiration Date', (
Date( Today() ) - Date( Date#( [Internal Value from], 'YYYYMMDD' ) )
)/ 30
)*-1)>=18 AND (If([Characteristic Name Desc]='N2 Contract Expiration Date', (
Date( Today() ) - Date( Date#( [Internal Value from], 'YYYYMMDD' ) )
)/ 30
)*-1)<=24,10,IF(ROUND(If([Characteristic Name Desc]='N2 Contract Expiration Date', (
Date( Today() ) - Date( Date#( [Internal Value from], 'YYYYMMDD' ) )
)/ 30
)*-1)>=25 AND (If([Characteristic Name Desc]='N2 Contract Expiration Date', (
Date( Today() ) - Date( Date#( [Internal Value from], 'YYYYMMDD' ) )
)/ 30
)*-1)<=36,5,IF(ROUND(If([Characteristic Name Desc]='N2 Contract Expiration Date', (
Date( Today() ) - Date( Date#( [Internal Value from], 'YYYYMMDD' ) )
)/ 30
)*-1)>36,-10,0)))

Thanks!

[JordyWegman]

Hi,

Yes that 5 digit number is the numeric value of date. You can check this for instance in Excel where if you put 44200, you will get a date if you change the format.

And yes, your script will work in the back-end!

Jordy

Climber