Jump to content
  • 0
Sign in to follow this  
Corpcat

Date Calculation in Formula field

Question

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?

 

Share this post


Link to post
Share on other sites

9 answers to this question

Recommended Posts

  • 0
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])

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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 

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×