Forcing input format

[MarcusV]

I’m trying to design an easy-to-enter table or form where people can enter bookings very quickly. The issue is with choosing a date, a start time and an end time.

In other software I’ve used - you can tap a 'Start time" field - quickly type 10 or 1030, or 10:30, or 7 or 730 or 0730 or 07:30 and the software sorts out the formatting. (This is 24hr format by the way)

I’ve been trying to get something similar in Airtable and got halfway there with DateTimeParse - but it has been choking on 3-digit times (eg 730 for 7:30am). I’ve even done a formula including an IF statement to check the length of the input and try a different format.

But frankly these all seem like massive cludges to fix a basic lack in Airtable - either a Time-only field type, or Input Masking - or input format forcing.

I should be able to easily choose the correct format for a field and if someone enters a time incorrectly it will pop up a message to direct them to use the correct format (in this case a 4-digit time field).

I’ve been adding second and even third Formula Columns next to the input column in order to correctly format all possible input formats.

By the way I don’t want to use the normal Date field with time, because it is unnecessary extra steps for the user, and can also add errors into the entries. People are choosing a single date, then entering a start time and an end time.

Perhaps there’s a simpler route that I’m not considering?

[Justin_Barrett]

Welcome to the community, @MarcusV! :grinning_face_with_big_eyes: Airtable’s lack of input validation is a known sore spot for many. When it comes to using a field to only enter times, here are some workarounds:

• Use a single-select field and create entries that match your available options. Use a formula to combine it with a formatted version of the selected date, then use DATETIME_PARSE() to parse the final result. Pros: it’s impossible for the user to make a mistake. Cons: it takes a bit of time to set up all of the options.
• Use a duration field, which auto-formats the result as hh:mm. Under the hood the data is stored as a number representing seconds, but this can be converted to an actual time using a formula (you’ll have to tweak the formula listed on that post to add in the actual date, but it’s doable). Pros: pretty unlikely that the user will make entry errors because they’ll see Airtable formatting the time the moment that they exit the field. Cons: duration fields won’t allow for “am” or “pm” entry, so if the user needs to book at 4 pm, they’ll have to use 24-hour notation; e.g. 16:00. Not convenient for all users, but if your audience is used to it, it might be passable.
• Let the user enter raw text, and have an adjacent formula field that uses regex to validate the entry and display some indicator of success/failure. Pros: puts the burden of correct entry on the user. Cons: this won’t work with forms because the data entered into a form only gets added to the table after submission (which also explains why formula fields aren’t allowed in forms: it’s not a record yet, and formulas only operate on records).
• A similar option to the last one can be done by building an interface and including those feedback fields as part of the interface design (along with some clever design to allow for new records to be added via an interface). Pros: easier than entering into a grid view if there are lots of fields to fill out. Cons: this will only work if your users are all collaborators.
• There are third-party services that let you build your own interfaces to interact with Airtable data, and I believe that some of them have included input validation features. However, I have very limited experience with such services, so I’m not sure if any of them have tackled the time-entry issue directly.

[MarcusV]

Thanks for the terrific response Justin. Very much appreciated!

I had begun work on two of your possible solutions in some form.
1 - a single line text field with another column that uses a formula to parse the input using some combination of DateParse and DateFormat. Haven’t quite nailed it yet though
2 - I started setting up a table to link to that contained all the possible time combinations so that we could use single select to choose a time.
But having a looong list to pick from isn’t really the best user experience!

We already use 24hr time and we are all collaborators, or will be if this works. So some of your suggestions are still valid options.

I’ve haven’t looked into regex validation yet. I have very limited experience in regex so that will be another learning curve! Which seems ridiculous for airtable to force me into jumping through so many hoops to achieve this simple option that I could do in an Excel spreadsheet probably decades ago!

The second component of my “complaint” is the lack of a native time field. I wouldn’t even be looking for input validation workarounds if we just had that. Seems pretty basic.

Oh well I appreciate your excellent reply and I will give your suggestions a go soon!

[Justin_Barrett]

MarcusV:

The second component of my “complaint” is the lack of a native time field. I wouldn’t even be looking for input validation workarounds if we just had that. Seems pretty basic.

True, and there are other threads that have discussed this issue at length. The main argument that I’ve seen against having a time-only field is that time isn’t necessarily an independent data point. It’s always part of a reference to a specific day, which (guessing) is why Airtable only includes time as an option in date fields. Even when working in JavaScript, there isn’t a separate data type to represent time only. It’s part of the Date type. My gut says that a separate time field will never be implemented, but part of me kinda-sorta wants to be proven wrong.

[HannesK-ME]

Hey there! I just wanted to add that our miniExtensions form allows for regEx validation of input fields without the need for any extra fields. ChatGPT is really good at generating regEx these days, so you don't really have to know much about it. Just describe your desired format and maybe have it tweak the results a couple of times to get the desired functionality. Using regEx, you can force the input to be in any specific format you need!

For example, this completely untested regEx should work for your use case, as it allows for 3 or 4 digits and even validates the minutes (e.g. 760 would be invalid):

^(?:[0-9]|[01][0-9]|2[0-3])[0-5][0-9]$

Using this in the miniExtensions form is super easy. You just set up your field and add the "matches regex" condition to the field's validation settings! The form will then only accept submissions with valid values according to the regex that's provided and show an error if it the condition is not met!

You can try this out now by creating a free account! 🙂