Default time for set date

[anaaaa]

Hi everyone!

I'm looking for a way to automatically set the time to 00:00 for certain entries in Airtable that have a date field. The goal is to allow some entries to have a specific time (like for flight schedules) while others default to 00:00 (such as open train tickets). I’ve tried using forms to set a default time, but the options only allow me to set a specific date and time or use the current date and time, which doesn’t work for my needs. Does anyone know how to achieve this?

Thanks!

[Mike_AutomaticN]

Hey!

I believe you could have 3 fields:
1. Type (single select)
2. Date and Time (date time field)
3. Adjusted Date and Time (formula).

Each time you create a new record, you will select the corresponding Type (e.g. flight schedule); then manually set a given Date and Time. 
The Adjusted Date Time formula will take care of applying the corresponding time (same, or 00:00) regardless of the actual time you input on your Date Time field for Open Train Tickets.
For visualizing this info or sharing with 3rd parties, you will never use Date Time, but Adjusted Date Time.

IF(
  Type = "Open Train Ticket",
  DATETIME_FORMAT(
    DATETIME_PARSE(
      DATETIME_FORMAT({Date and Time}, 'YYYY-MM-DD') & " 00:00",
      'YYYY-MM-DD HH:mm'
    ),
    'M/D/YYYY HH:mm'
  ),
  DATETIME_FORMAT({Date and Time}, 'M/D/YYYY HH:mm')
)

Depending on your Time Zone, you might need some small additional tweaks.

If you need a more scalable and robust solution, I would suggest creating a new table for "Types", create one record per type, and have a checkbox field on it for "Default to 00:00". Then, have a lookup on your initial table (Demo on my screenshot above), and have the Adjusted Date Time formula verify whether the lookup is checked or unchecked. In this way, you will not have to change the formula each time you create a new Type.

I hope this helps.

Mike, Consultant @ Automatic Nation