How to create a Formula based on another formula

[Woolf]

I need to be able to calculate a date, created by a formula, which is prior to whatever the current date is. That sounds easy but I cannot figure out how to do it in my specific situation.

Because I needed to determine a date that was 2 weeks after a date entered based on two different scenarios I created this formula in my data table:

CASE

     WHEN [@field:Brand_Received] = 'Brand 1' THEN Dateadd(day, 14, [@field:Date_1])

     WHEN [@field:Brand_Received] = 'Brand 2' THEN Dateadd(day, 14, [@field:Date_2])

END

It works well to give me the date that is two weeks after the specific date given, based on unique requirements.

However, now I need to create a field that checks to see if the new Formula Date is before whatever the current date is. I have not been able to get a result for this. Caspio does not allow me to use results from one formula field as variable in another formula field. And I have not been able to figure out just yet how to combine all the variables I need to produce a valid formula.

Any thoughts?

[NailDyanC]

Hi @Woolf, just to clarify, you would like to get 14 days before the current date? If yes, then you may use this formula:

DATEADD(Day,-14,SysDateTime())

I use SysDateTime() function to get the current date.  However, SysDateTime() only works on DataPage level.

[Woolf]

On 10/16/2021 at 10:18 AM, NailDyanC said:

Hi @Woolf, just to clarify, you would like to get 14 days before the current date? If yes, then you may use this formula:

DATEADD(Day,-14,SysDateTime())

I use SysDateTime() function to get the current date.  However, SysDateTime() only works on DataPage level.

Depending on which version of a product a person uses, I am trying to get a new date that is 14 days AFTER the original date. This I already figured out how to do.

CASE

     WHEN [@field:Brand_Received] = 'Brand 1' THEN Dateadd(day, 14, [@field:Date_1])

     WHEN [@field:Brand_Received] = 'Brand 2' THEN Dateadd(day, 14, [@field:Date_2])

END

But then I need to determine if the new date (14 days after the original date) is BEFORE whatever the current date is (which would change depending on when the user accessed the report).

[NailDyanC]

Hi @Woolf,

I created a sample workflow that would check if the new date is before or after the current date. Here's my formula(You will be using two calculated fields):

CASE WHEN

[@calcfield:1] >= (SysDateTime())

THEN
'AFTER'
ELSE
'BEFORE'
END

The "[@calcfield:1] " is the first formula that you have that would determine the new date. Please let me know if that works for you.

[cheonsa]

For additional details about SQL functions, check out this article: https://howto.caspio.com/function-reference/

[GoodBoy]

Just to share this reference in date & time conversion in SQL: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/