I think the issue is knowing what assumption to make about the date. For instance, a date entered without a time field defaults to midnight — but if I enter a time, what date should I assume? If I assume the current date, that potentially raises issues if I’m logging times after-the-fact (for instance, at the end of a call), but if I assume the most recent occurrence of that time (e.g., at 2 am I enter 11 pm, so I assume 11 pm the previous day), it screws me up if I’m trying to schedule an appointment. I’m not saying it’s a bad or unworkable idea; I’m just saying I’m not sure how I’d handle assignment of the date portion of the datetime value…

Hi W_Vann_Hall,

I’m not sure you understood my post, sorry - I am asking for a time field WITHOUT any date. Because I find I often need to enter multiple times for one date or want to enter times without dates at all, and would like to be able to do so without having to figure out what to do with a pointless date value that, as you said, could be confusing.

Totally agree though that it can get rather screwwy if you enter datetime fields later - I often log things later that I did earlier and if it’s past midnight it definitely screws me up

As you suggest, there isn’t such a thing as a time field without an assumed date. :winking_face:

Wait: I take that back. Say, for instance, you wanted to track the time-of-day at which something takes place. In that instance, you might want to store a date-less time. In that case, though, storing it as a single-line text field makes as much sense as anything, as it’s ceased to have any relative meaning. If you’re using the value to track how long you spent on a task, though, then there is an assumed date value. Agreed, Airtable doesn’t currently make it as easy as it could be to enter such a value,¹ but I suspect a disembodied time sans date would be more confusing than helpful…

— but that was why I was asking how you would like to see it work, as you evidently have a use case calling for such a feature

1. My suggestion would be to treat any two numbers separated by a colon or three numbers separated by two colons as a time, with an optional ‘a’ or ‘p’ for 12-hour clocks, and an assumed date of TODAY() — but that seemingly might be problematic for some regional uses.

Without a timecode field, you have to go through an extraordinary rigamarole to combine a separate time field with a date field and to get it into the right timezone and/or suitable for an iCal subscription. I can see the value of such a field type for that reason.

I agree as well. We use airtable in part to generate a calendar of events. It is problematic that I cannot use the Group feature to sort by day, since it treats events on the same day as new given that the time is different.

Every single database on the planet knows how to format a date field and just show the time, in excel it’s simply h:mm, the universal format is hh:mm:ss. But Airtable doesn’t know how do simple things like time.

If you want to format a date/time field to show just the time field, you can create a formula field with DATETIME_FORMAT.

When I want a time field without a date, I use a duration field. It isn’t perfect. I have to enter time as military time, and it is possible to enter invalid times. I have formula fields for viewing the time as am/pm. When I want to apply that time to a specific date, I use formulas for that as well.

Please advise how to deal with the following usecase: I have working hours table for multiple stores. I need a record for every store like {weekday} = “Monday”, {from} = 9:00AM, {to} = 5:00PM. I’d like to calculate the {Name} field with a formula (if I use duration for the hour fields I can’t convert them to string and concatenate). And I need the time to not be a string, as I’d like to have another calculated field for the store like “Now open”, depending on the current weekday and time.

Yes you can, though there will be some work involved. Anything can be converted to a string by concatenating it with another string. In the case of the duration field, it’s going to return the duration in seconds, so 1:00 becomes 3600, 2:00 becomes 7200, etc. Do some math to convert that back into hours and minutes, then concatenate it with the rest of your data in the formula. Not exactly easy, but doable.

Actually, using a string for the time would be much simpler than all the math required with a duration field. Use DATETIME_PARSE(), building the string to parse by combining the formatted version of today’s date with the time from the {From} or {To} field. Here a test I ran using this method:

Here’s the formula in {Parsed From}. {Parsed To} does the same with the {To} field.

DATETIME_PARSE(DATETIME_FORMAT(NOW(), "MM/DD/YYYY ") & From, "MM/DD/YYYY h:mmA")

Add another formula field to see if the current time falls between the {Parsed From} and {Parsed To} values.

Thank you, I’ll try.

If that info gives you the answer you were seeking, please mark it as the solution to your question. This helps others who may be searching with similar questions. Thanks!

Do you have a formula for this? I’m in same boat and messing around with DATETIME_PARSE() but not getting anywhere. Numbers are always all jumbled up in the results

No. Did you try the formula given a few replies up?

Here’s a formula that I use, based on getting the numbers out of the string
adjust column names, etc.

Column 1: Begin - single line text (ex. 13:03)
Column 2: End - single line text (ex. 15:22)
Column 3: Duration - Formula
format the field to duration, h:mm:ss (click formatting above the formula) and don’t ask me why you have to choose the option with the seconds instead of the one without, it’s just how this works for some reason …

Formula:
60*(LEFT(end,2)-LEFT(begin,2))+(RIGHT(end,2)-RIGHT(begin,2))

Well here i am, two years later, and this function apparently doesn’t exist. I would like to create appointments by having a form with a selectable date and then a selectable time. Availability tables (like this one, How to make a simple booking system) do a great job when you want to create each availability manually, but it doesn’t seem like there is a way to select a date and then a time from a {time} field. Very frustrating and seemingly basic.

I realize this is an old post but let me help you understand what she wants as well as why I am on this thread. I feed airtable through a front-end interface for my small business clients. they need to select what their business hours of operation are. I would like it to be easy for them to make a selection and not have to flat type their daily open and closed hours. And on the back end, we connect their data with other platforms, but can’t have nonstandardized time entries. Just looking for a spinner select to some sort of field like below.

In my base I have a field for Date … When I used your formula , the times are correct but It populates Today’s date … I’m assuming that’s because of the “NOW” wording in your formula … I changed the now to “Date” to call in the date that’s already inside that record but it doesn’t work …

Is there a workaround?

If not … Can I hide the display date and just show the time?

Did you replace NOW() with Date or with Date()? If it’s the latter, that explains the failure because there is no Date() function. “NOW” isn’t just a “wording” as you describe. It’s the name of a function, and the parentheses immediately after it indicate the calling of the function, which is why I prefer to include them whenever referring to functions in these forum conversations.

Anyway, if you replace NOW() with Date (no trailing parentheses), and your date field is actually named “Date”, then it should work. If not, please copy and paste the exact formula that you’re trying to execute and we can more easily help to solve the problem (if it’s possible to be solved).

For time-only entry, the options are quite limited. If you don’t want to use the manual entry method from my comment above, you could create a single-select field with the available options pre-created for users to pick from. A variant on that technique would be to use a multiple-select field where the user chooses “pieces” of the full time; e.g. choosing items labeled “12”, “:00”, and “PM” which could be assembled by a formula into “12:00PM”. Admittedly that’s quite messy and prone to errors in user input, and frankly I’d be hesitant to use such a system myself, but you asked for workarounds, and that’s definitely one.

Not currently. That’s a long-requested feature that hasn’t yet been addressed, and frankly I doubt that it ever will be.

I have worked my way through this thread, as I am stumped by the same problem.
I want to create a calendar with the begin and end times of various shifts allocated to certain employees on specific days.

I have a table holding information about specific shifts, eg Shift 1- beginn 07:00 end 13:00
now there is no specific date allocated as this is just generic, usable for any day by the person making the plan for a specific week.
I have no problem calculating duration and turning the text into a date field, but the issue remains the date it uses. I would want to be able to pick a specific date and a specific time and combine it into a date field I can use in a calendar.

So I am sitting here, wanting to create say an entry for May 22 that pulls in Employee A (linked tabled) working the Shift 1 (linked table) and pulling in the start and end times automatically so we have a proper beginn and end date for the specific day of may 22, without having to type in the times each and every time.

I tried it out with the suggestions here but didn’t get anywhere near to what I need. Are there any other tricks one can use?

The two main ideas are to use a text field or a duration field, and both work. Use a text field in conjunction with DATETIME_PARSE(). Use a duration field in conjunction with DATEADD() with seconds as the units.

You may also need to include a timezone offset if you want to show the resulting date/times on a calendar in the correct local time.

In fact I found the solution, it’s a function
TIMESTR()

This extracts the time field from a date field. Works like a charm

Hey Bettina, can you provide further details on how you used TIMESTR()?

Thanks!

@Airtable is there any progress on this?

I need a time only function to manage and find out how well social media posts do at certain times, but there isn’t an option without a date (or even to hide it). Also annoyingly when trying to select the date time in the field as it currently is - it defaults to the current time and I have to click into the cell again to select the time I want. At least fix this behavior / give us the option to change the default behavior of the field.

Reading through ~3 years of history on this time thread and there’s still not a great solution. AT has been making a lot of headway on things, so hopefully they can continue fixing things like this.

Thanks for your time!

Dear Airtable team,
Please can you reply to this with haste:
Will you be implementing a proper Time only field before the end of December 2022?

We need to know so we can decide if we are moving everything over to Smartsuite - who do have a proper Time field as requested here for years.
(They also have a few other fields that people have been waiting years for on Airtable with no concrete reply from you - Location fields come to mind for example.)
This is mission critical for a lot of us and our clients, and we have waiting long enough.
As such we have set our own cut of day of the end of this year 2022 for us and our clients.

Again, will you be implementing a proper Time only file before the end of 2022 please?
We will take silence to mean no, and suggest everyone else reading this does too.

I wouldn’t hold my breath. Airtable employees rarely read, let alone answer, posts in this community (although it has improved somewhat over the last weeks). Your best chance is to send an email to support@airtable.com.