Skip to main content

Hi Community,


i’m extracting the date from the below string


27909270100235 = 1979-SEP-27



using a formula below


DATETIME_FORMAT(RIGHT(LEFT(RIGHT(LEFT(ARRAYJOIN({National ID#}),7),6),4),2)&"/"&RIGHT(RIGHT(LEFT(ARRAYJOIN({National ID#}),7),6),2)&"/"&IF(LEFT(RIGHT(LEFT(ARRAYJOIN({National ID#}),7),6),2)>50,“19”&LEFT(RIGHT(LEFT(ARRAYJOIN({National ID#}),7),6),2),“20”&LEFT(RIGHT(LEFT(ARRAYJOIN({National ID#}),7),6),2)),“DD-MMM-YYYY”)



the result is ok, 27-sep-1979



but I’m trying to get the age by further calculations/formula but it seems it doesn’t recognise the date above to make further actions



and I realised that the formatting tye in the field doesn’t recognise it too, and received below message





Blockquote





Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.





Blockquote





any suggestions?

Welcome to the community, @Ahmed_ABOELEANIN! :grinning_face_with_big_eyes: It appears that you might have confused a couple of Airtable’s date-related functions. You’re currently using DATETIME_FORMAT(), which is meant to take a datetime—a raw data element representing a specific date and time—and convert it to a string. What you need to do is use DATETIME_PARSE(), which takes a string and converts it into a datetime. Only datetimes can have date and/or time calculations performed on them.



On a related note, you can greatly simplify your formula by parsing directly from a subset of the source string instead of trying to break it up with various text functions:



DATETIME_PARSE(MID({National ID#}, 2, 6), "YYMMDD")




Notice how certain years might not be parsed correctly (see the third line) based on assumptions about two-digit abbreviations. I noticed that your formula calculates years greater than 50 as being in the 1900s instead of the 2000s. To account for that, the formula would look like this:



DATETIME_PARSE(IF(VALUE(MID({National ID#}, 2, 2)) > 50, "19", "20") & MID({National ID#}, 2, 6), "YYYYMMDD")


On a side note, I’m not sure why you’re using the ARRAYJOIN() function, as you’re dealing with a string, not an array. If the {National ID#} field is a number, the correct way to turn it into a string is to concatenate it with an empty string. That would turn the formula into this:



DATETIME_PARSE(IF(VALUE(MID({National ID#} & "", 2, 2)) > 50, "19", "20") & MID({National ID#} & "", 2, 6), "YYYYMMDD")


Reply


Terms & Conditions