Skip to main content
Solved

Formula to create time fields from existing text field

  • June 23, 2025
  • 6 replies
  • 135 views
L
Forum|alt.badge.img+3

 

Originally when we created our system, we used text fields to record the Call Time and Gig Time Span. Since we have the time zones already saved for each record, I am wondering if there is an easy way to do the following:

 

  1. Switch the Call Time Field from a Text Field to a Time field (without having to type the time zone every time)
  2. Switch the Gig time Span Field into 2 different Time fields for both previous records and current records, a Start Time and End Time. Similarly, I do not want to type the time zone every time. Moving forward, I would like to only have to type the start start and end time for an event in separate fields (not in the text box).

If this is possible, these would save a ton of time and allow us to do better, more efficient work. Thank you for your help!

Best answer by Mike_AutomaticN

Hey ​@Lowell_Ringel,

Following up with Adam’s question above, by “Time” filed you mean Duration field type right? This is the duration field type we are making reference to. 

If so, do you need it in duration field type for any reason, or could we have a formula and format of some sort?

Is this ONLY for your historical data mentioned above, or how is this data being pushed to Airtable? Assuming this is coming through a Zapier, Make, or n8n automation (more on these tools here) it will probably be way cleaner to handle this data processing on the automation itself and only push the clean data you actually need in the format you need to Airtable.

If only for processing historical data, then you might want to use the following formulas (Using Gig Time Span rather than Time Range field name), and then just copy and paste the values into the duration field.

Start Time:

IF(
  {Time Range},
  DATETIME_FORMAT(
    DATETIME_PARSE(REGEX_EXTRACT({Time Range}, "^(\\d{1,2}:\\d{2}[ap]m)"), 'h:mma'),
    'HH:mm'
  )
)


End Time:
 

IF(
  {Time Range},
  DATETIME_FORMAT(
    DATETIME_PARSE(REGEX_EXTRACT({Time Range}, "(\\d{1,2}:\\d{2}[ap]m)$"), 'h:mma'),
    'HH:mm'
  )
)



Result:
 


As you can see, it converts the am/pm format into a 24hs format to use on a duration type field.

Hoep this helps! Feel free to reach out as needed.

Mike, Consultant @ Automatic Nation​​​​​​​

6 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Is a ‘Time’ field a ‘Duration’ type field in this context?  Could you give an example input and output?  Would love to help but not really following, sorry!

Adam, Airtable Consultant - thetimesaving.company
L
Forum|alt.badge.img+3
  • Lowell_Ringel
  • Author
  • New Participant
  • June 24, 2025

I would prefer to split this Gig Time Span field that currently contains the duration (12:00pm - 3:00pm) into 2 time fields, Start Time (12:00pm) and End Time (3:00pm).

 

This also involves converting a text field into 2 time fields, hence where I thought an automation or formula might allow me to do this. This is so my historical data and records that have been entered in the old way can get converted over. 

Mike_AutomaticN
Forum|alt.badge.img+28

Hey ​@Lowell_Ringel,

Following up with Adam’s question above, by “Time” filed you mean Duration field type right? This is the duration field type we are making reference to. 

If so, do you need it in duration field type for any reason, or could we have a formula and format of some sort?

Is this ONLY for your historical data mentioned above, or how is this data being pushed to Airtable? Assuming this is coming through a Zapier, Make, or n8n automation (more on these tools here) it will probably be way cleaner to handle this data processing on the automation itself and only push the clean data you actually need in the format you need to Airtable.

If only for processing historical data, then you might want to use the following formulas (Using Gig Time Span rather than Time Range field name), and then just copy and paste the values into the duration field.

Start Time:

IF(
  {Time Range},
  DATETIME_FORMAT(
    DATETIME_PARSE(REGEX_EXTRACT({Time Range}, "^(\\d{1,2}:\\d{2}[ap]m)"), 'h:mma'),
    'HH:mm'
  )
)


End Time:
 

IF(
  {Time Range},
  DATETIME_FORMAT(
    DATETIME_PARSE(REGEX_EXTRACT({Time Range}, "(\\d{1,2}:\\d{2}[ap]m)$"), 'h:mma'),
    'HH:mm'
  )
)



Result:
 


As you can see, it converts the am/pm format into a 24hs format to use on a duration type field.

Hoep this helps! Feel free to reach out as needed.

Mike, Consultant @ Automatic Nation​​​​​​​

TheTimeSavingCo
Forum|alt.badge.img+31

Hmm, Airtable only has a Date Time field I’m afraid, and no stand alone ‘Time’ field type.  If you want it to be a display thing then you could try using a formula field to parse that ‘Gig’ field into two formula fields? 

Start

LEFT(
  Name,
  FIND(
    ' - ',
    Name
  ) - 1
)

 

End

SUBSTITUTE(
  Name,
  Start & ' - ',
  ''
)

 

As this is a formula field it’s not editable or usable in other forms though, it’s pretty much just text

 

Adam, Airtable Consultant - thetimesaving.company
L
Forum|alt.badge.img+3
  • Lowell_Ringel
  • Author
  • New Participant
  • June 25, 2025

Thank you all! I think I am going to have to do this on the automation side, as I do need it to end up in a Date/Time field that is useful from a calendar/chronological standpoint.

Mike_AutomaticN
Forum|alt.badge.img+28

Yes ​@Lowell_Ringel, I would def suggest getting that handled on the automation itself! 

Terms & ConditionsAccessibility statement