Need help Calculating time by start time and end time

[Kerry_Johnson]

I have a database that I track service calls with.
i have a start time, end time, and a field called invoice time.
I would like the invoice time to calculate the hours i am going to invoice the customer for based on the start time and end time entered for the service call.

For example:
start time 8:00am End Time 9:00am = invoice time 60 minutes or 1 hour.

Another example:
start time 8:00am End Time 8:45am = invoice time 45 minutes.

It would be nice if i could pick a time on the start and end colums with a picker, right now i have to manually enter the times.

[Nathalie_Collin]

Hi @Kerry_Johnson and welcome to Airtable Community :smiling_face_with_three_hearts:

Try switching your field type to a Date format and select the INCLUDE A TIME FIELD which will then grant you a date picker.

I use it for the same reason you do and what’s really nice about it, is that there is a TODAY button which automatically inputs the current time

[Kerry_Johnson]

Great Idea, Nathalie!
How do you parse just the time out of the start and end time fields to calculate the duration? Do you have a formula?

[Nathalie_Collin]

From there, I calculate the duration and to do that, I have a formula field that contains;

DATETIME_DIFF({End Time},{Start Time},'seconds')

and the formatting for that field is;

So this gives me the duration in hours/minutes and from there, I create another formula field to calculate the value, or the amount that I need to bill for.

So that formula would look something like this;

Duration*Rate/3600

With the formatting on that one being Currency.

Hope this makes sense. If you’d like, I can create a sample base for you to review.

[Kerry_Johnson]

Nathalie_Collins:

DATETIME_DIFF({End Time},{Start Time},‘seconds’)

Makes perfect sense, exactly what i was looking for!! Thanks!! This also helped me get a grasp on the function. Thanks again, i just started looking at air table last night to use for my business. Everything off the shelf is way more than what i need.

[Kerry_Johnson]

Curious if you ever thought about rounding the amount up if you have a minimum hourly service call charge?
For example if you charge $85 and the duration is 47 minutes, round up to $85 for billing?

[Nathalie_Collin]

Kerry_Johnson:

charge $85 and the duration is 47 minutes, round up to $85 for billing

Sounds like you’re referring to having a flat rate. Do you always perform flat rates or is it a mixture?
I do a bit of both. So my setup has been created to allow for markups, discounts and flat rates. This allows me to keep track of physical performance rather than focusing ONLY on the billing amount.

[Kerry_Johnson]

Not always a flat rate, just if its under an hour minimum is $85.

I am still using the actual time field to see it, have a separate field to see the amount, just thought it would be nice to calculate the minimum in the amount fieldif its less than an hour.

[Nathalie_Collin]

Kerry_Johnson:

calculate the minimum in the amount field if its less than an hour

Yes, you most certainly can do it with an if statement to your currency field that you implemented earlier in this post. I’m not at my PC at the moment, so I can’t give you an exact formula.