What to do when you have 2 dates on a single record?

[Cristian_Rojas3]

I’ve imported a CSV where one of the columns contains 2 dates. Like this:

8/23/21 - 11/6/21

In order for me to create a time line I’m thinking I need to create 2 additional fields (one for each date). I know I need to use FIND to tell the formula to find the “-” and work from there, I just don’t know how. Can some one please help me?

[Dimitris_Goudis]

Hey @Cristian_Rojas3,

Welcome to Airtable community.

I would recommend you to split the two dates in two separated columns in csv before you import it in Airtable. This will help you to not spend fields to switch the dates in Airtable.

Otherwise for the find formula you can do the following process:

1. First you have to find the the position of the " - " in the field which includes both dates. So you will need the find formula:

FIND("-",{date 1& 2})

2. Then you need a formula that will extract the first date form the field based on the find formula result:

LEFT({date 1& 2},formula_find-2)

3. Then you need an other formula to extract the second date from the field:

RIGHT({date 1& 2},formula_find-1)

Last but not least!!!
The RIGHT() and the LEFT() formula return string as a result. So if you want to use them as date data type you have to nest those formulas in DATETIME_FORMAT().

I wish my answer help you and for any further information please do not hesitate to text the community back.

Yours sincerely,
Dimitris Goudis

[Cristian_Rojas3]

Thank you Dimitris!

I just got a bit confused with the last part and haven’t been able to figure it out. :grimacing: Could you please write how would the formula syntax would look like? :pray:

[Dimitris_Goudis · Accepted solution]

The last past is to convert the returns of Right and Left formulas into date data type:

DATETIME_FORMAT(RIGHT({date 1& 2},formula_find-1),"DD-MM-YYYYY")

For any further questions please feel free to text back :slightly_smiling_face: