Ageing dullard needs help (again)

Hello gentlefolk. I have a problem getting a Monday Board to 'do sums correctly’.

My board consists of three columns that calculates time durations for three different activities. I then need to add the three separate time durations for input into an accounting system. If all the time durations are exact hours, 1, 2 or 3, etc, it all works fine but, if any of the duration are for (by example) 1.5, 1.25 etc, only the 1 is taken.

So, if the three durations are 1, 1 and 1, I get 3 which is correct but, if the time durations are 1.5, 1 and 1, I get 3 which is incorrect.

The time durations are calculated from time started and time ended using HOURS_DIFF({Visit End},{Visit Start}) for 1st visit, again for 2nd visit and again for the 3rd visit: I then using SUM({1st Visit Hours},{2nd Visit Hours},{3rd Visit Hours}) to calculate the total but the response only comes back as integers.

Any help or thoughts would be welcomed.

1 Like

Hi Kevin,

No need for the self-deprecation! Monday.com can be terribly idiosyncratic and almost counter-intuitive at times.

I think your issue here is that you’re trying to sum “Hours” data, which is different and can’t be summed in the same way.

If you want to convert your time difference to hours as decimals, you can use the following formula (there may also be a simpler way of doing this).

FORMAT_DATE({End},"HH")
-
FORMAT_DATE({Start},"HH")
+
(FORMAT_DATE({End},"mm")
-
FORMAT_DATE({Start},"mm"))
/60

The result of this will be more suited to summing. Hope that helps!

Francis,

So many thanks for your suggestion. Looking through it, I can see no reason why it shouldn’t work but, it doesn’t :frowning_face: . I used the formula as suggested thus (for 1st Visit Hours):

FORMAT_DATE({Visit End},“HH”) - FORMAT_DATE({Visit Start},“HH”) + (FORMAT_DATE({Visit End},“mm”) - FORMAT_DATE({Visit Start},“mm”))/60

But, it just returns a zero!

I’m trying to do this as a (no charge) favour for an elderly care group and their support workers but I’m at my wits end on this one. If you, or anybody else, have any thoughts or ideas at all I really would be so grateful.

Many thanks
Podge, aka Kev.

What type of columns are your Visit Start and Visit End columns?

The formula I created is designed to work with date columns, which I recommend using over these.

Hmmm, I did wonder that. They are just time columns. I tried using:

HOURS_DIFF({Visit End},{Visit Start}) which sort of works but only gives integers so 09:00 to 11:00 gives me 2 but 09:00 to 11:30 also gives me 2. Using Date columns gave me other issues especially given that. some of the support workers are totally computer illiterate (their words not mine) with no access to PCs/Laptops and do everything on their smartphones.

Many thanks
Kev

Hi Kevin,

No problem!

I’d still recommend Date columns in almost all cases, however here’s a simple change to make the formula work with Hour columns. You should be able to paste this directly.

LEFT({Visit End},2)
-
LEFT({Visit Start},2)
+
(RIGHT({Visit End},2)
-
RIGHT({Visit Start},2))/60

Best of luck!

1 Like

Francis,

You are an absolute star :star2:

So many thanks. That seems to work perfectly.

Thank you once more :pray:

Your help is so much appreciated.

Cheers
Podge, aka Kev