Duration Calc once you work past midnight

[Tanya]

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

[JonathanBowen]

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

[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

[W_Vann_Hall]

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.)

[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

[W_Vann_Hall]

Tanya:

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

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 hmm[a|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: