Jump to content
  • 0

Date Calculation in Formula field


Corpcat
 Share

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?

 

Link to comment
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])

Link to comment
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

Link to comment
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 

Link to comment
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

Link to comment
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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...