Time (without date) field option

Hello All,

Personally I use airtable to track things throughout the day, and I’ve found it quite annoying to not have a time field option that is separate from the date. Of course I can enter the time manually in a single line text entry, but I think since we can enter the date without the time it only makes sense that we be able to enter the time without the date. What do you all think? Is this something you would find useful or something you have wished for?

Claire

12 Likes

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

5 Likes

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

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.

5 Likes

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.

3 Likes

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.

5 Likes

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.

1 Like

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:

Screen Shot 2020-05-22 at 2.46.42 PM

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.

3 Likes

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?