Managing partial dates

[John_S]

Our company is cataloging data that often has incomplete date information. For instance some entires we know the year and month and others have the year, month and day. I am trying to figure out how to best solution for capturing the data so use on a timeline. So far the best solution seems to be to use have a year, month and date field.

Thoughts?

[Justin_Barrett]

Welcome to the community! :slightly_smiling_face: My gut approach would be the same as what you mentioned: separate fields for year, month, and day. If you’re assembling these into actual date entries that can be tracked on a calendar, you’d need to include logic in the assembling formula that defaults to January for a missing month, or the 1st of the month for a missing day.

[John_S]

This is exactly what I did. Now the only thing I’m missing is the ability to have a formula field and be able to set its data type: I want to be able to set a formula and designate that column as a “date”.

[Justin_Barrett]

You can set a format for a formula field as long as the formula always generates a consistent format. If it’s a date sometimes and text other times, it won’t let you do it.

To set this up, click the “Formatting” tab when editing the field properties, and Airtable will “test” your formula for a brief moment to see what kind of data it generates. Assuming your formula always generates a date, it should let you choose between various date display options.

[John_S]

Yes, this does work, unfortunately now I’m running into the problem that if I have a lookup from another table to this data field it is not recognized as a date. It seems that data formatting is lost for any lookup field.

[Justin_Barrett]

Lookup fields have the same “Formatting” tab when you set them up. If you’re looking up a date, that should stick if the field is formatted as such.

[John_S]

Yes, they do, but try and go make a timeline block and use a lookup date field for your start and end dates. It will not let you.

[Justin_Barrett]

In that case, add a formula field that copies the date from the lookup field. For example, if lookup field {Lookup Date} pulls in the date, the formula for {Lookup Converted} would simply be:

{Lookup Date}

Once formatted as a date, {Lookup Converted} would work in a timeline block (I just tested it).

[John_S]

Thats exactly what I ended up doing. Seems like a bug on Airtable’s end that a lookup date field isn’t recognized by the timeline block.