Using DATETIMESUM Function and Countdown Function

Hello @janethorie

Yes, you can sum negative values to date. Furthermore, if you need the result in yyyy-mm-dd format, you should implement the equation as follows:

GETDATETIMESTRING(DATETIMESUM(fieldname39, 'yyyy-mm-dd', (-730), 'd', true), 'yyyy-mm-dd')

Best regards.

Dear Codepeople

Thank you so much for your quick reply. Had to reorder date format to read this:

GETDATETIMESTRING(DATETIMESUM(fieldname39, ‘mm-dd-yyyy’, (-730), ‘d’, true), ‘mm-dd-yyyy’)

What I have experienced from this code is that (-730) does not compute. So I played around and any value 10 and under, eg: (-10) and the code does work….so I am wondering if there is a code limit or a format issue for higher value numbers placed between the brackets?

Hello @janethorie

Is fieldname39 a date field? What is the date selected?

I created a mockup form for testing your equation. Please, watch the video by visiting the following link:

https://resources.developers4web.com/cff/tmp/2022/10/04/video-date_o.mp4

Best regards.

Dear Codepeople,

Simply WOW! Thanks so much for the quick video. ?? Please allow me a little time to review your video and I will get back to you.

Many Kind Thanks
jh

Dear Codepeople

Thanks so much for your video. ?? I really appreciate your efforts !!

In my own attempt to fix this function, I opted to change from the function from days to months to read as follows:

GETDATETIMESTRING(DATETIMESUM(fieldname39, ‘mm-dd-yyyy’, (-24), ‘m’, true), ‘mm-dd-yyyy’)

And this latest reiteration seems to work much better regardless the number of the months subtracted from the future date (fieldname39).

The end goal is enter one date value, once, into the (fieldname39) and have the CFF form automatically update the calendar values for the whole form. With this first automation piece of the function puzzle now resolved, is it possible to use another fieldname to complete countdown days left each time this code is repeated but with a different month variable?

Eg:
GETDATETIMESTRING(DATETIMESUM(fieldname39, ‘mm-dd-yyyy’, (-24), ‘m’, true), ‘mm-dd-yyyy’)
Get Countdown days from 24 months of fieldname39?

GETDATETIMESTRING(DATETIMESUM(fieldname39, ‘mm-dd-yyyy’, (-12), ‘m’, true), ‘mm-dd-yyyy’)
Get Countdown days from 12 months of fieldname39?

GETDATETIMESTRING(DATETIMESUM(fieldname39, ‘mm-dd-yyyy’, (-5), ‘m’, true), ‘mm-dd-yyyy’)
Get Countdown days from 5 months of fieldname39?

Of course, I know the code for countdown would need its own field placeholder for each (24,12,5 mths)to delivery the three countdown outputs.

Or is the GETDATETIMESTRING(DATETIMESUM(fieldname39, ‘mm-dd-yyyy’, (-5), ‘m’, true), ‘mm-dd-yyyy’) now too complicated to create a countdown days code?

In my own attempt for countdown, I used this code (below) but it did not work.

PREC(abs(fieldname39-fieldname36),0)

In advance, I thank you for all your help so far.

Best Regards
jh

Hello @janethorie

I’m sorry, I don’t understand your question. If the fieldname36 value is a date text, and you want to get the countdown days between fieldname39 and fieldname36, you should use the DATEDIFF operation:

DATEDIFF(fieldname39, fieldname36, 'mm-dd-yyyy', 'd')['days'];

Best regards.

It Works!

Here are my notes for future look up if I need to repeat:

Code 01: To Calculate 24 Months Before Renewal Date: (Use Months Instead of Days) **Code Successful***
GETDATETIMESTRING(DATETIMESUM(fieldname39, ‘mm-dd-yyyy’, (-24), ‘m’, true), ‘mm-dd-yyyy’)

Code 02: Last Treatment: Countdown **Code Successful**
DATEDIFF(fieldname38, fieldname20, ‘mm-dd-yyyy’, ‘d’)[‘days’];

where fieldname39=any future date greater than 24 months from today
where fieldname38=today’s date expressed as blank
where fieldname20=code 01

Thank you so much for your patience. You did an excellent job!!

Hello @janethorie

To use today date in the equations, you don’t need additional fields in the form, only use the correct operation:

DATEDIFF(TODAY(), fieldname20, 'mm-dd-yyyy', 'd')['days'];

Furthermore, if you use the fieldname20 field only as an auxiliary for the definitive result “Code 02”, you can remove it and edit the equation as follows:

DATEDIFF(TODAY(), GETDATETIMESTRING(DATETIMESUM(fieldname39, 'mm-dd-yyyy', -24, 'm', true), 'mm-dd-yyyy'), 'mm-dd-yyyy', 'd')['days'];

Best regards.

Dear Codepeople,

All is now working….here are my codes

For Column One
GETDATETIMESTRING(DATETIMESUM(fieldname39, ‘mm-dd-yyyy’, (-24), ‘m’, true), ‘mm-dd-yyyy’)

For Column Two
DATEDIFF(fieldname38, fieldname20, ‘mm-dd-yyyy’, ‘d’)[‘days’];

And since a picture is a 1000 words, here is an image of my autofill form.
https://ibb.co/N7NdWj2

Thank you so much. I wouldn’t have been able to resolve without your help.
jh

Excellent !!!!

Best regards.

And thank you for your patience!!!
Have a great weekend.
jh