Calculate date with number

Hello All,

Need your help urgently on one of my form attached
test_tool.xlsx (16.5 KB)

I am having two issues on this form:

  • I did a calculation as: format-date(today(), '%Y%m%d')+(50-${age}) and the result I am expecting is, when the age of the person is 50 or more, then the service1 will expire. My expected output should be if the person's age is 20 today then it will expire in 2050. My calculation is adding up with the date value, not with year.
  • 2nd problem, service2 -service5 have expiry dates. I need to display an expiry date based on the date the service was provided to the client/patient. For example service 2 should expire 4 weeks after the service was provided to the patient and service3 should expire after 3 months of the service was provided to the patient.

I need to resolve this as soon as possible. Your support will be highly appreciated.


Hi @A.N.M_AL-IMRAN here you want to see a year, not a date ?

Here is the calculation for that (date need to be "cast" as a year)
format-date(today(), '%Y')+(50-${age})

1 Like

For the second question the idea is to get the duration of each service and to compute it with the subscription date.
I know how to do it from an external csv choice list (with pulldata() ) but I think it is possible from the choices sheet.
I found this to get a value associated to the choices in choices sheet but I can't get it run : Get custom data from choices sheet for calculation - #2 by MattFoy

1 Like

Dear @mathieubossaert,

Thank you so much for your quick response! I tried

However, the output I received is just the year. I need complete data of expiration, but again when I am using

It is showing the full date but the difference between age and 50 has been added to the day value instead of year value. This means the result was something like this: 20200541 If the patient's age is 10. Please see the example:

Secondly, I checked the link you shared; however, I could not figure out how do I achieve on my test form. For example, if service2 expires 3 weeks after the date of service received or service3 expires 1 year after from the date of service received. How do I need to calculate that?

Your support will be highly appreciated,

As @mathieubossaert has provided, this will correctly calculate the desired year.

format-date(today(), '%Y%m%d')+(50-${age})

This doesnt work because format-date(today(), '%Y%m%d') returns a string, eg "20200531" (for May 31, 2020), which gets converted to the number 20200531 when used in an arithmetic axpression, to which you are adding the number 10, hence you get the number 20200541.

If you want to add 10 years to your new custom date 'number' format, in your case you'd have to add 100000. So try this:

format-date(today(), '%Y%m%d') + ((50-${age}) * 10000)


Dear @Xiphware,

Did not work, for some reason. it returned 20100551. If the patient's is 10 then it should expire on 2060.

Thank you,

I find it easier to reason about the year and the month/day separately:

concat(int(format-date(today(), ‘%Y’)) + (50-${age}), format-date(today(), ‘%m%d’))

That would be the correct expression if the service was provided today. You probably need to use the service date both to calculate the age and the components of the expiration date.

To compute a date N weeks from now:

format-date(date(decimal-date-time(today())+(N * 7)), ‘%Y%m%d’)

Same as above, you'll need to use the service date rather than today.

If you haven't already, I also encourage you to consider whether the form needs to compute these dates or whether it's something that can be computed in analysis.


Can you please double-check... this works for me:

datething.xlsx (8.4 KB)

All that said, @LN is solution is more elegant! (and in fact the one I was going to propose too as a more logical alternative, but I thought I'd fix your math first... :wink: )

1 Like


I did what I imagined with external csv services list, to take advantage of pulldata function.
For each selected service I can ask the duration colmun value of the selected service and compute it with the subscription date.

Here is a form with only this part.
test_tool.xlsx (8.5 KB) services.csv (191 Bytes)

1 Like

Dear @LN, @mathieubossaert, @Xiphware,

Thank you all for being so supportive. I was completely stuck with my form and the issues have been resolved with your help.

Thank you all again,