From dates to working hours formula with General Caster App

Hi guys,
in the last few days several General Caster users asked us how to calculate working hours between two date/times.
So we decided to publish the formula in our General Caster Ideas page.

Scenario: get working hours between two dates with times in a 09:00-18:00 (9AM-6PM) work schedule

Requirements: two dates column with time and one number column

Integration: When column changes, perform formula and cast result to column

((NETWORKDAYS(DATEVALUE({item's From DateTime}), DATEVALUE({item's To DateTime})) - 1) * (TIMEVALUE("18:00") - TIMEVALUE("09:00")) + IF(NETWORKDAYS({item's To DateTime}, {item's To DateTime}), MEDIAN(MOD(TIMEVALUE({item's To DateTime}), 1), TIMEVALUE("18:00"), TIMEVALUE("09:00")), TIMEVALUE("18:00")) - MEDIAN(NETWORKDAYS(DATEVALUE({item's From DateTime}), DATEVALUE({item's From DateTime})) * MOD(TIMEVALUE({item's From DateTime}), 1), TIMEVALUE("18:00"), TIMEVALUE("09:00"))) * 24


Hi, I’m looking for similar but I only need the number of years between 2 dates. Would love your guidance.

YEAR(DATEVALUE({Item's To})) - YEAR(DATEVALUE({Item's From}))

In this case you’ll get the exact difference between two years, like 2022 - 2019 = 3.
If, instead, you need “how much of a year” is between two dates, just use the following formula.

(DATEVALUE({Item's To}) - DATEVALUE({Item's From})) / 365

Does anyone know how to do this but with just times not DateTimes. Particularly, I believe mine are written in as text.

Hey @mochmaster
What do you mean? Casting to a Hour column, if this is the purpose, requires to use the HH:MM format.

Honestly this might not even be the right thread for this as IDK if its a general caster problem. I have 4 columns date 1, time 1, and date 2, time 2. I want to know the Hours_DIFF between the two times, but only if it’s within business hours (i.e 9am to 5 pm). As an exmaple, if time 1 is 4pm and time 2 is 10 am, that is only 2 hours essentially.

Please read documentation about how to convert a formula from a Formula column into GC:

Hi @rob
I’m trying to cast from Hour column to Number column but its only round numbers (17:30 → 17)
is it possible to get the current working hours based on hours?

What do you mean?
Converting 17:30 to 17,5?
If it’s a time value and not a duration it makes little sense.
Anyway you can extract “hour” and “minute” pieces using

HOUR({item's Time}) + MINUTE({item's Time}) / 60

Thanks! Yes i needed numeric

That was excellent. I needed minutes so just changed the 24 to 1440 and worked perfectly.

1 Like