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

1 Like

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

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

Works fine on a Monday Formula.

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


((DATEVALUE({item’s End Date}) + TIMEVALUE({item’s End Date})) - (DATEVALUE({item’s Start Date}) + TIMEVALUE({item’s Start Date}))) / 24 / 60

Initially it did not work and kept sending unexpected errors. It for some reason started working and now settled on this formula:

((DATEVALUE({item’s End Date}) + TIMEVALUE({item’s End Date})) - (DATEVALUE({item’s Start Date}) + TIMEVALUE({item’s Start Date}))) * 1440

This now returns minutes between the 2 date/times.

Is there a way to round it up as I cast the result to the Item Name but always has a vast decimal after the minutes?

Got it, thanks:

ROUNDUP(((DATEVALUE({item’s End Date}) + TIMEVALUE({item’s End Date})) - (DATEVALUE({item’s Start Date}) + TIMEVALUE({item’s Start Date}))) * 1440,0)

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.

@rob do you have a formula for this. I keep getting error messages.