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

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.

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.

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?
Thanks

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

How would I produce the formula in simple terms to just calculate the time between 2 date/time columns. Iâ€™m having a real issue trying to reverse engineer the formula.

Something seems to have changed to stop the original formula working.

I am looking for a simple solution and the following should work but doesnâ€™t. Anyone have any ideas why this will not return the time in minutes between the 2 date/time columns:

=DAYS({itemâ€™s End Date},{itemâ€™s Start Date})*1440

Nothing has changed, @Noelie2000 DATEVALUE function is missing in the formula.
Itâ€™s used to convert a date string into a valid date (in days) that then can be used to perform operations.

DAYS(DATEVALUE({itemâ€™s End Date}), DATEVALUE({itemâ€™s Start Date})) / 24 / 60

Thanks. I have tried that on a few boards and changed to minutes with *1440 and it is either not recognised or only works on full days; not taking in to account the hh:mm element

Hi Iâ€™m looking for something similar.
I have 2 column- Clock-in and Clock-out.
They both list the specific time for when they clocked in and clocked out.
I want the difference between those 2 times to cast into a number column to show me the total hours worked that day for payroll.