Dates and Parsing

[Claremont_Scout]

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?

[David_Skinner]

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

[Justin_Barrett]

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.

[Claremont_Scout]

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

[Justin_Barrett]

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

[Claremont_Scout]

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.

[Justin_Barrett]

Claremont_Scouts:

I can’t access that list of formats. I get the list of decimal, integer, etc.

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.