Skip to main content

Sort existing information by date & time

  • April 8, 2024
  • 5 replies
  • 127 views
S
Forum|alt.badge.img+1

Hi! I have a large existing google sheets I am trying to organize into Airtable. I own an events agency that books out talent for hundreds of events each month. This is a living document that is updated every day by people who fill out our form to book - this was a google form that dropped down into a google doc, but Airtable seems much more user friendly. I want the rows to automatically drop into date & event start time order with an AM/PM format. My current information has the date, start time & end time in separate columns but the Airtable automation has date/time within one field. Is there a way to merge this so my existing data can format without me having to re-input hundreds of upcoming events into a new spreadsheet? 

Screenshots of my Google Sheets spreadsheet & how it imported into Airtable. 

Thanks! 

5 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Hmm, try this?

DATETIME_PARSE( DATETIME_FORMAT( {date of event}, 'DD MM YYYY' ) & " " & {start time}, "DD MM YYYY hh:mm A" )


Link to base

Adam, Airtable Consultant - thetimesaving.company
S
Forum|alt.badge.img+1
  • SAEntertainment
  • Author
  • New Participant
  • 2 replies
  • April 9, 2024

Hmm, try this?

DATETIME_PARSE( DATETIME_FORMAT( {date of event}, 'DD MM YYYY' ) & " " & {start time}, "DD MM YYYY hh:mm A" )


Link to base


Thank you for responding! I am very new to Airtable so I'm not sure where this code would go? 

Also, my time blocks are currently just a "single text line" field rather than a "single select" field that your example is using. I have been playing with the single select filed as the option on my Fillout form, but rather than using the options I added, it adds a new different option every time someone fills out the form. 

Is there a better way to be formatting this by day & time? I'm afraid I will have to go in retype all my events one by one. 

TheTimeSavingCo
Forum|alt.badge.img+31

Thank you for responding! I am very new to Airtable so I'm not sure where this code would go? 

Also, my time blocks are currently just a "single text line" field rather than a "single select" field that your example is using. I have been playing with the single select filed as the option on my Fillout form, but rather than using the options I added, it adds a new different option every time someone fills out the form. 

Is there a better way to be formatting this by day & time? I'm afraid I will have to go in retype all my events one by one. 


Ah you'd need to make a formula field for that.  Whether it's a select field or a text field shouldn't affect the formula in this scenario, so should be fine I think

If your end goal is to have two fields, one with the start date and time and one with the end date and time, then I think this is the way to go, and you wouldn't need to retype anything

Adam, Airtable Consultant - thetimesaving.company
S
Forum|alt.badge.img+1
  • SAEntertainment
  • Author
  • New Participant
  • 2 replies
  • April 10, 2024

So, it sort of works? When I test my form submissions (using Fillout.com) the Start Time column & calculation align with what I put in the form but the date & time within the original "Date of Event" are way off? Not sure how that is calculating or if it will effect the calculation in the long run. Also, the End Times are showing up in military time - any ideas on how to make that AM/PM without having the date repeating? 

TheTimeSavingCo
Forum|alt.badge.img+31

So, it sort of works? When I test my form submissions (using Fillout.com) the Start Time column & calculation align with what I put in the form but the date & time within the original "Date of Event" are way off? Not sure how that is calculating or if it will effect the calculation in the long run. Also, the End Times are showing up in military time - any ideas on how to make that AM/PM without having the date repeating? 


Hm, given that your "Date of Event" field doesn't use the time at all, I'd suggest just turning it off:



---
re: Also, the End Times are showing up in military time - any ideas on how to make that AM/PM without having the date repeating?

Hmm, that's probably how Fillout's populating it.  Is there a setting on the Fillout side for you to determine the format of the time output?  Not sure what you mean about not having the date repeating, sorry

Adam, Airtable Consultant - thetimesaving.company


Terms & ConditionsAccessibility statement