Skip to main content

Hi

New to Airtable ‘today’, trying to convert my elaborate spreadsheet into a database.


I am struggling with a duration calculation.


Works fine if you start at 12:00 and finish at 23:00, I hit the wall when the finish time is 01:00 or later the next day.


Any ideas?

I can create an if statement that is if end is greater than start end-start. if not true i can ask it to midnight takeaway start + end. It’s the formatting the midnight time to subtract the start time causing me an issue.

or

If there is there a better solution out there ??


Thanks in advance


Tanya

Hi @Tanya - do your start and end fields have date and time, i.e. just normal date fields with time added? If so you can just use the DATETIME_DIFF formula:


DATETIME_DIFF(End, Start, 'hours')



JB

Hi Jonathan

Thanks for your prompt response.

The times are duration fields, i thought this the correct type to use.

I already have a date field.

Should I use something like an And statement to concatenate the date and time then evaluate duration.

or

Thinking on the fly I could create columns that combine the 2 for an easier calc for duration?


Regards

Tanya

Hi Jonathan

Thanks for your prompt response.

The times are duration fields, i thought this the correct type to use.

I already have a date field.

Should I use something like an And statement to concatenate the date and time then evaluate duration.

or

Thinking on the fly I could create columns that combine the 2 for an easier calc for duration?


Regards

Tanya


How are you using the duration fields?


Typically, you would only use a single duration, as it would contain a single value indicating elapsed time. If you are using one field to indicate start time and a second to indicate end, what you want are two date fields, each with ‘Include a time field’ toggled on. That would allow you to set start and stop dates and times. (For an example, see the screenshot @JonathanBowen posted.)

Thank you, I will try combine the 2 columns into one.

For the ease of data entry I think better to have the date & time different.


Regards

Tanya

Thank you, I will try combine the 2 columns into one.

For the ease of data entry I think better to have the date & time different.


Regards

Tanya



If you’re using a 24-hour clock, it’s actually fewer clicks/keystrokes to enter date and time in a datetime field than to enter date in a date field and hours:minutes in a duration field. (If you’re using a 12-hour clock, it uses the same number of clicks/keystrokes.)


24-hour
































Datetime Field Date and Duration Fields
Double-click field to pop up calendar pick window. Double-click field to pop up calendar pick window.
Select date. Select date.

Triple-click on time field.		 Select duration field.
Enter hour and minutes as hmm or hhmm (no colon). (That is, for ‘10:14pm’ enter ‘2214’.) Enter hour and minutes as h:mm or hh:mm (with colon).
Press tab. Press tab.

12-hour
































Datetime Field Date and Duration Fields
Double-click field to pop up calendar pick window. Double-click field to pop up calendar pick window.
Select date. Select date.

Triple-click on time field.		 Select duration field.
Enter hour and minutes as hmmsa|p] or hhmm a|p] (no colon). (That is, for ‘10:14am’ enter ‘1014a’.) Enter hour and minutes as h:mm or hh:mm (with colon).
Press tab. Press tab.

:winking_face:

Reply


Terms & Conditions