Datediff display year.months and datediff using 3 variables

[tkoty]

I have a table that includes person's:

1. date of birth (DOB)
2. scheduled date for services (ScheduledDate)

On several datapages/forms/reports I need to display:

• Age (in years and months - EX: 3.4)
• Age at time of services (in years and months - EX: 3.4)

I have used "Calculated Value" and  "Datediff(year, [@field:DOB], SysUTCDateTime())" , but haven't been able to figure out how to update it to show the month (X.4)

 

For the scheduled date age:

Similar issue, but now I have to calculate the age as above but include the extended scheduled age. This date is usually not more that 2-3 months difference.

Would it be?:

Datediff (year, [@field:DOB], [@field:ScheduledDate]) (I doubt it because formula isn't displaying anything in the form)

Thank you for any direction or push in the right direction.

[KlisaN137]

Hi @tkoty,

You could try to separately calculate difference in years, and difference in months, and then concatenate the results. So, something like this:

CAST(DATEDIFF(year, [@field:DOB], SysUTCDateTime()) as varchar) + '.' + CAST(Abs(Month([@field:FollowUp_Date]) - Month([@field:Start_Date])) as varchar)

[cheonsa]

Hello @tkoty,

As I understand, you wanted to display the age as years.months.

To achieve that you can use this formula:

SELECT CAST(DATEDIFF(YEAR,'[@field:DOB]',GETDATE()) AS VARCHAR(10)) + '.' + CAST(DATEDIFF(MONTH,'[@field:DOB]',GETDATE())-(DATEDIFF(YEAR,'[@field:DOB]',GETDATE())*12) AS VARCHAR(10)) AS Age

 

You can use the same formula for the scheduled date age. Just change the DOB field.

Kindly check these links for reference:

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

 

Hope this helps.

[tkoty]

On 10/27/2022 at 6:07 AM, KlisaN137 said:

Hi @tkoty,

You could try to separately calculate difference in years, and difference in months, and then concatenate the results. So, something like this:

CAST(DATEDIFF(year, [@field:DOB], SysUTCDateTime()) as varchar) + '.' + CAST(Abs(Month([@field:FollowUp_Date]) - Month([@field:Start_Date])) as varchar)

Thank you, @KlisaN137. When I entered these values (substituting the field names), I get a blank in the Age calculated field value:

CAST(DATEDIFF(year, [@field:DOB], SysUTCDateTime()) as varchar) + '.' + CAST(Abs(Month([@field:ScheduledDate]) - Month([@field:DOB])) as varchar)

I have a feeling I'm not using the correct "Start_Date" field substitution. Thoughts? 

[tkoty]

19 hours ago, cheonsa said:

Hello @tkoty,

As I understand, you wanted to display the age as years.months.

To achieve that you can use this formula:

SELECT CAST(DATEDIFF(YEAR,'[@field:DOB]',GETDATE()) AS VARCHAR(10)) + '.' + CAST(DATEDIFF(MONTH,'[@field:DOB]',GETDATE())-(DATEDIFF(YEAR,'[@field:DOB]',GETDATE())*12) AS VARCHAR(10)) AS Age

 

You can use the same formula for the scheduled date age. Just change the DOB field.

Kindly check these links for reference:

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

 

Hope this helps.

Hi @cheonsa! Thanks for the response. Your code worked well with 1 minor hiccup. When the date displays for months 1-10

E.G.

1. DOB=10/28/2020
2. Age = 2.0
3. DOB=11/28/2020
4. Age = 2.-1 - Is there a way to get it show as 2.11

Also when the DOB field is blank, the number "122.9" is displayed instead of blank.

[cheonsa]

Hello @tkoty,

Please try this instead:

SELECT CAST((DATEDIFF(m, [@field:DOB], GETDATE())/12) as varchar) + '.' + 
       CAST((DATEDIFF(m, [@field:DOB], GETDATE())%12) as varchar) as Age

I checked the result based on your example

1. DOB=10/28/2020
2. Age = 2.0
3. DOB=11/28/2020
4. Age = 1.11

 

[tkoty]

19 hours ago, cheonsa said:

Hello @tkoty,

Please try this instead:

SELECT CAST((DATEDIFF(m, [@field:DOB], GETDATE())/12) as varchar) + '.' + 
       CAST((DATEDIFF(m, [@field:DOB], GETDATE())%12) as varchar) as Age

I checked the result based on your example

1. DOB=10/28/2020
2. Age = 2.0
3. DOB=11/28/2020
4. Age = 1.11

 

@cheonsaThat's perfect for Age (in years and months - EX: 3.4)! I also love the simplicity of the code! Thank you!

Interestingly, when updating formula to use "ScheduledDate" (always a future date (E.g.; 11/24/2022)), this is the returned value: 
Age at time of services: 0.-1

ScheduledDate is 1 - 60 days in the future of Case entry. 

 

SELECT CAST((DATEDIFF(m, [@field:ScheduledDate], GETDATE())/12) as varchar) + '.' + 
       CAST((DATEDIFF(m, [@field:ScheduledDate], GETDATE())%12) as varchar) as EvalAge

Additionally, while I enjoy the building aspect of this, I don't have the skill or time once I get beyond this basic set up. Is there a resource of providers (not consulting companies) I can view? I'd like to develop a long -term relationship with someone who understands our business as we grow.

Thanks again!

[tkoty]

On 10/29/2022 at 7:59 AM, tkoty said:

@cheonsaThat's perfect for Age (in years and months - EX: 3.4)! I also love the simplicity of the code! Thank you!

Interestingly, when updating formula to use "ScheduledDate" (always a future date (E.g.; 11/24/2022)), this is the returned value: 
Age at time of services: 0.-1

ScheduledDate is 1 - 60 days in the future of Case entry. 

 

SELECT CAST((DATEDIFF(m, [@field:ScheduledDate], GETDATE())/12) as varchar) + '.' + 
       CAST((DATEDIFF(m, [@field:ScheduledDate], GETDATE())%12) as varchar) as EvalAge

Additionally, while I enjoy the building aspect of this, I don't have the skill or time once I get beyond this basic set up. Is there a resource of providers (not consulting companies) I can view? I'd like to develop a long -term relationship with someone who understands our business as we grow.

Thanks again!

Anyone one have any help on this one? @cheonsasolution was 99% there, until I got some additional feedback see example below:

EX:

Today's Date= 11/02/2022

DOB: 10/22/2020
Returned value should be = 2.0 (2 years + less than 1 month - Correct)
The formula above returns: 2.1 (2 years + 1 month - Incorrect)

ScheduledDate (always in the future): 11/21/2022
Returned Value = 0.-1
Should be returning: 2.1 (2 years + 1 month - won't change to 2.2 until 12/23/2022)

IF:
ScheduledDate (always in the future): 12/23/2022
Returned Value = 0.-1
Should be returning: 2.2

This the formula I use in Excel, but I can't translate it for use in Caspio:

=IF(ISBLANK(ScheduledDate),"",DATEDIF(DOB,ScheduledDate,"Y")&"."&DATEDIF(DOB,ScheduledDate,"YM")) 

So based on the above both formulae need to be updated:

• DOB = 10/22/2022
• Age (in years and months: 2.0
• Age at time of services (in years and months
  • 2.0 for 10/22/2022 - 11/22/2022
  • 2.1 for 11/23/2022 - 12/22/2022
  • 2.2 for 12/23/2022 - ...