If else for length of time based on start date

[taylorswiftlover]

Hi,

I'm trying to create an if/else statement for a length of time, based on a start date we put in for each client. Basically its the following:

If a person has been with our company less than 60 days, (THIS TEXT APPEARS), If a person has been with our company longer than 60 days, (THAT TEXT APPEARS)

 

Say that the field is Start_Date. 

[SinJunYoung]

Just now, joneslovescaspio said:

Hi,

I'm trying to create an if/else statement for a length of time, based on a start date we put in for each client. Basically its the following:

If a person has been with our company less than 60 days, (THIS TEXT APPEARS), If a person has been with our company longer than 60 days, (THIS TEXT APPEARS)

 

Say that the field is Start_Date. 

I did recreate your field Start_Date and created another as Some_Date. 

Try this:

CASE
WHEN
(Datediff(day, [@field:Start_Date], [@field:Some_Date])) < 60 THEN
'THIS TEXT APPEARS'
ELSE
'OTHER TEXT APPEARS'
END

 

[taylorswiftlover]

I get that this works for the Start Date and Some Date (thank you btw), how about if I want to make today's date as the basis. Do I manually input today's date then?

[SinJunYoung]

I see. If that's the case, you can remove the Some_Date field and change it to GetUTCDate() to get the Current system timestamp in UTC time.

So formula should be:

CASE
WHEN
(Datediff(day, [@field:PackageStartDate], GetUTCDate())) < 60 THEN
'THIS TEXT APPEARS'
ELSE
'OTHER TEXT APPEARS'
END

 

[SinJunYoung]

Just to add, the GetUTCDate() is not available in Formula Field, I just checked here: https://howto.caspio.com/function-reference/

[Glitch]

I have a question regarding the 'THIS TEXT APPEARS'.

I understand that you can not use calculation inside rules, however, if I implement this CASE WHEN statement to hide or show fields, what exactly is the code for that?

Like 

CASE
WHEN
(Datediff(day, [@field:PackageStartDate], GetUTCDate())) < 60 THEN
'HOW DO I HIDE TEXT'
ELSE
'HOW DO I SHOW TEXT'

Anyone who knows for a workaround?

[kpcollier]

@Vanellope Maybe you can create a virtual field that receives the calculation as a parameter and use that in the Rules section of the DataPage wizard? Sorry I am not at home so I can't test on my end.

[Glitch]

Right! Exactly!

I posted the question here: 

Somebody answered with the same idea. 

Thanks @kpcollier for your help!

[SinJunYoung]

I think you may also want to know this additional information just in case you need a formula as to how to get the number of days and hours in a single calculated field and the datatype is a timestamp and a date/time:

CAST ((Floor((Datediff(hour,[@field:DateTime],[@field:Date_Submitted])/24)) ) as VARCHAR) +
' DAY(S) '

 

Hope this helps. 

[SinJunYoung]

Also, you may add the number of hours using the same reference as above: 

CAST(((Datediff(hour,[@field:DateTime],[@field:Date_Submitted]) - (Floor((Datediff(hour,[@field:DateTime],[@field:Date_Submitted])/24))) *24)) as VARCHAR) + ' HOUR(S) ' 

 

[SinJunYoung]

Add both of them: 

CAST ((Floor((Datediff(hour,[@field:DateTime],[@field:Date_Submitted])/24)) ) as VARCHAR) +
' DAY(S) ' + 
CAST(((Datediff(hour,[@field:DateTime],[@field:Date_Submitted]) - (Floor((Datediff(hour,[@field:DateTime],[@field:Date_Submitted])/24))) *24)) as VARCHAR) + ' HOUR(S) ' 

There are cases when other formula are not applicable when you are trying to add both of the equation to the calculated field. This formula works though. Hope this helps
 

[ParkLoey]

Hiyaa! Dropping by to provide caspio's article about conditional forms or usage of rules  I hope this helps!

https://howto.caspio.com/datapages/forms/conditional-forms/

[Queso]

Hello guys, you can refer to the following HowTo article for more details about the CASE statement: https://howto.caspio.com/function-reference/