Date Calculation in Formula field

[Corpcat]

I want to create a formula to calculate the following.

Determine is the date in one field is later than the date in another field and select the later date for the calculation, then add a number from another field to the Years. 

If Field 1 is later than field 2 use Field 1 otherwise use field 2 then add the number of years from Field 3. Also the data type from Field 3 is text, not a number although all the content is an integer.

Could anyone help me with this?

 

[Vitalikssssss]

Hi @Corpcat,

You can utilize CASE expression in the formula field in order to achieve the desired functionality.

https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

Hope this helps.

 

Regards,

vitalikssssss

[Corpcat]

Will this work the same in formula fields? I need the calculation done after the data is imported, not from a datapage!

 

regards

David 

[cheonsa]

Hi @Corpcat,

 

If you are referring to the formula field in the table, then, yes. It will work the same. 

You may also check these links: https://howto.caspio.com/tables-and-views/data-types/formula-fields/

https://howto.caspio.com/function-reference/

 

Regards,

kristina

 

[Corpcat]

21 hours ago, kristina said:

Hi @Corpcat,

 

If you are referring to the formula field in the table, then, yes. It will work the same. 

You may also check these links: https://howto.caspio.com/tables-and-views/data-types/formula-fields/

https://howto.caspio.com/function-reference/

 

Regards,

kristina

 

this is my formula but doesn't seem to work

 

CASE 
WHEN [@field:End_of_Project_Date]<[@field:Doc_Creation_Date]
THEN Dateadd(Year,[@field:Retention],[@field:End_of_Project_Date])
ELSE Dateadd(Year,[@field:Retention],[@field:Doc_Creation_Date])

[Vitalikssssss]

hi @Corpcat,

Please note that you use Dateadd() function wrong.

This function can produce a new date which results from the addition of the specified number to datepart.

Here is an example of the expression which adds one year:

CASE 
WHEN [@field:End_of_Project_Date]<[@field:Doc_Creation_Date]
THEN Dateadd(Year,[@field:End_of_Project_Date],1)
ELSE Dateadd(Year,[@field:Doc_Creation_Date], 1)
END

Please let us know the business logic of formula expression, so we could help.

Regards,

vitalikssssss

[Corpcat]

Thanks

I need to grab the number to add from another field, this is the number of years I need to keep a document either after the date the document is produced or after the date a project had ended.

CASE 
WHEN [@field:End_of_Project_Date]<[@field:Doc_Creation_Date]
THEN Dateadd(Year,[@field:End_of_Project_Date],[@field:Retention])
ELSE Dateadd(Year,[@field:Doc_Creation_Date],[@field:Retention])
END

Is this correct? I got incorrect Syntax

 

regards

David 

[Vitalikssssss]

Hi David,

I think there is a misunderstanding on your end of Formula field functionality.

Please note that you cannot write values in other fields with a Formula field. 

You would like to capture a Year part of "End_of_Project_Date" or "Doc_Creation_Date" conditionally as far as I understood.

In this case you should try the following formula:

CASE 
WHEN [@field:End_of_Project_Date]<[@field:Doc_Creation_Date]
THEN Datepart(Year, [@field:End_of_Project_Date])
ELSE Datepart(Year, [@field:Doc_Creation_Date])
END

Hope this helps.

Regards,

vitalikssssss

[DefinitelyNot31337]

On 10/8/2018 at 5:24 PM, Corpcat said:

I want to create a formula to calculate the following.

Determine is the date in one field is later than the date in another field and select the later date for the calculation, then add a number from another field to the Years. 

If Field 1 is later than field 2 use Field 1 otherwise use field 2 then add the number of years from Field 3. Also the data type from Field 3 is text, not a number although all the content is an integer.

Could anyone help me with this?

 

--

On 10/10/2018 at 7:34 PM, Corpcat said:

this is my formula but doesn't seem to work

 

CASE 
WHEN [@field:End_of_Project_Date]<[@field:Doc_Creation_Date]
THEN Dateadd(Year,[@field:Retention],[@field:End_of_Project_Date])
ELSE Dateadd(Year,[@field:Retention],[@field:Doc_Creation_Date])

Given that following fields have respective DataTypes:

* End_of_Project_Date is Date/Time

* Doc_Creation_Date is Date/Time; and

* Retention is Text(255)

 

The formula below should work

CASE WHEN [@field:End_of_Project_Date] > [@field:Doc_Creation_Date]

THEN DateAdd(year, CAST([@field:Retention] as INT), [@field:End_of_Project_Date])

ELSE DateAdd(year, CAST([@field:Retention] as INT), [@field:Doc_Creation_Date])

END

 

Reference: https://howto.caspio.com/function-reference/

 

Hope this helps.

 

Regards,

DN31337

[Corpcat]

Awesome thanks the formula works !!