Skip to main content

I am trying to calculate the number of months to an 18th birthday. I start with the Date of Birth and add 18 years to that. I format that date so that I get DD/MM/YYYY and not the American MM/DD/YYYY. That works fine. Now I use the following formula in the next column: DATETIME_DIFF({18th Birthday},TODAY(),‘months’).


I get a result (3 months instead of 5) which indicates that it is using (In think) the American date and reading the month and year backwards. Is there anyway around this?

Try formatting all fields as ISO date to see exactly what is happening.

Formatting with a formula converts a datetime object into a string, so your hunch about why DATETIME_DIFF() is miscalculating things is likely correct. However, if you format the date field using the field’s formatting options instead of a formula, my gut says that the calculation would work correctly.


Formatting with a formula converts a datetime object into a string, so your hunch about why DATETIME_DIFF() is miscalculating things is likely correct. However, if you format the date field using the field’s formatting options instead of a formula, my gut says that the calculation would work correctly.



Um. If I change to a Date format, I would not be able to insert a formula. Am I not understanding things?

Um. If I change to a Date format, I would not be able to insert a formula. Am I not understanding things?


Sorry, misleading screenshot. However, the same options are also available for formula fields. Click on the “Formatting” tab when editing the field info.


Sorry, misleading screenshot. However, the same options are also available for formula fields. Click on the “Formatting” tab when editing the field info.



I can’t access that list of formats. I get the list of decimal, integer, etc. I would post an image but it says I cannot put an image in a post.


That’s likely because you’re using DATETIME_FORMAT() to convert the processed date into a string. Remove the format, and leave what I assume is the DATEADD() function that you’re using to add 18 years to the birth date. That will return a datetime object, and the field formatting can then be used to display that date as you wish.

Reply


Terms & Conditions