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

2 Likes

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