AirTable World - Need a formula that calculates or date / hr / min difference - Start date / time - end date / time

[SHAWN_MCGIBNEY]

AirTable World - Need a formula that calculates or date / hr / min difference - Start date / time - end date / time

[Ilan_Ben_Yaakov]

try

DATETIME_DIFF(start time,end time,'days')

[SHAWN_MCGIBNEY]

[SHAWN_MCGIBNEY]

Thanks But No GO
Thanks for the support

[Justin_Barrett]

When referencing a field that contains more than one word in its name, you must wrap the name in curly braces. The formula you show in your screenshot only has a single curly brace before the {Start TIME} field, and none around the {End TIME}. The formula should be this:

DATETIME_DIFF({Start TIME}, {End TIME}, "days")

That will only give you the raw days rounded down to the nearest day. The formula would need to be a lot more complex to also break down the hours, especially when taking work hours into account.

[kuovonne]

My app Ready-Made Formulas makes creating formulas for calculating the difference between dates easier. You pick your table, fields, and units and don’t have to worry about pesky things like whether you need to include curly braces, commas, quote type, or any of the other common problems that prevent a formula from working properly.

The date/time formulas do require a premium license, but you get all the formulas in the app for one low price, including some pretty complex formulas.

[SHAWN_MCGIBNEY]

Thanks Justin

The hours captured would be key for this request
I have various engineers working on different projects and hoping to catch hrs/min on each

[Justin_Barrett]

Thanks for the clarification. After thinking on this more, I wouldn’t recommend trying to solve this with a formula field. There are too many variables involved, and formula fields don’t really provide a clean way of covering them all in a single formula:

1. How many hours are in a workday? (Not all businesses are the same) This factors into the next three.
2. How many hours (if any) are left between the project start time and the end of that workday?
3. How many hours in whole workdays that fall between the start and end days?
4. How many hours between the start of the last workday and the project end time?

Add up the total of 2–4, and you’ve got the answer, but again, doing that in a single formula would be a nightmare. (Airtable does have a WORKDAY() function, but that only tracks whole workdays, not hours-per-workday.) It may be possible to split it up into several formula fields, but even that makes me cringe. When something becomes a complex multi-formula solution to a single problem, I’m more inclined to tackle it via a script (either in the Scripting app or in an automation).

Either way, it’s going to take at least an hour (possibly more) to work out and properly test a solution, and that kind of investment turns this into work for hire. If you’ve got a budget and are willing to invest in a solution, reach out directly and we can talk details.

[SHAWN_MCGIBNEY · Accepted solution]

For any all who need to know - this worked for me
DATETIME_DIFF({End TIME},{Start TIME},‘minutes’)/60

[Justin_Barrett]

If the job is in a single day, that works. However, most of the jobs in your screenshot above span multiple days. In that case, the formula that you listed will also count hours outside of the workday—not just normal working hours—which won’t give you an accurate count.