Skip to main content
Solved

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

  • January 7, 2022
  • 3 replies
  • 44 views
C

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?

Best answer by Dimitris_Goudis

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:


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:

3 replies

Dimitris_Goudis
Forum|alt.badge.img+20

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})
  1. 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)
  1. 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

C
C
  • Cristian_Rojas3
  • Author
  • New Participant
  • January 7, 2022

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})
  1. 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)
  1. 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


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
Forum|alt.badge.img+20

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:


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:

Terms & ConditionsAccessibility statement