From dates to working hours formula with General Caster App

Platform discussions Special workflows & use cases
, ,
1

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
2

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

3

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

4

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

5

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

6

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.

7

Please read documentation about how to convert a formula from a Formula column into GC:
https://generalcaster.app/website/documentation/formula/#differences

8

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

9

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

10

Thanks! Yes i needed numeric

11

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

1 Like
12

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.

Thanks.

13

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.

14

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

15

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

16

@Noelie2000

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

17

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?

image
image1183Ă—188 13.9 KB

18

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)

19

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.

20

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