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

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.
https://generalcaster.app/website/documentation/formula/#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:
https://generalcaster.app/website/documentation/formula/#differences