HOURS_DIFF glitch

Lots of roadblocks lately. Here’s the one that currently has me stumped. HOURS_DIFF seems to be calculating only minutes - not hours.

Sample data:

Current formula is:
if({TimerStatus}=“Stopped”,HOURS_DIFF({end2},{start2}),“foo”)

The only relevant part is:
HOURS_DIFF({end2},{start2})

What’s going on?

@jscottsmith

HOURS_DIFF() prefers Hours columns.

This will work (only considers the TIME portion):

HOURS_DIFF(FORMAT_DATE({Start Date},"HH:mm"), FORMAT_DATE({End Date},"HH:mm"))

Using the undocumented functions DATEVALUE() and TIMEVALUE(), you might also consider a variation of this (considers DATE portion as well):

((DATEVALUE({End Date}) + TIMEVALUE({End Date})) - (DATEVALUE({Start Date}) + TIMEVALUE({Start Date}))) * 24

You see more about other (previously) undocumented functions here: The monday.com Hidden Functions

Jim - Subscribe to The Monday Man
Watch Our Latest Video: The Power of the Right-Click

1 Like

Brilliant! I was actually searching through your undocumented functions yesterday looking for ideas.

The time side of things is working but there appears to be a glitch in the date. Check out the middle Dec 28 results
image

It’s calculating different DATEVALUEs for the same day… 44559 and 44560

The error seems to occur if the time span between start and end includes 7pm. Given that is 5 hours from midnight and I am in EST (UTC−5), I wonder if they are related. Is this a Monday calculation glitch, or do I need to make an offset calc in your formula?

@jscottsmith

Good catch!!

Just goes to show you, even The Monday Man makes misstakes! :grin:

This should do the trick:

((DATEVALUE(FORMAT_DATE({End Date})) + TIMEVALUE({End Date})) - (DATEVALUE(FORMAT_DATE({Start Date})) + TIMEVALUE({Start Date}))) * 24

I’ll add that to my docs.

Jim - Subscribe to The Monday Man
Watch Our Latest Video: The Power of the Right-Click

Awesome. THANKS!!

Onward I go…