Jump to content
  • 0

Datediff display year.months and datediff using 3 variables


tkoty
 Share

Question

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.

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0

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)

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0
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? 

Link to comment
Share on other sites

  • 0
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.

Link to comment
Share on other sites

  • 0

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

 

Link to comment
Share on other sites

  • 0
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!

Link to comment
Share on other sites

  • 0
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 - ...
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...