Counting hours if the column is not empty

Platform discussions Special workflows & use cases
1

Hello, trying to count the number of hours from two hours columns, but i like to have a “0” (or a blank) if the colmns are not completed.

IF({Heure debut} = "", 0,
(LEFT(HOURS_DIFF({Heure fin}, {Heure debut}), 2) +
   RIGHT(HOURS_DIFF({Heure fin}, {Heure debut}), 2) / 60)
   )

But it doesn’t work with columns without any hours. see printscreen below

image
image1274×581 50.5 KB

I have tryed :
IF({Heure debut} = 0, 0,…
IF({Heure debut} = " ", 0,…

Any idea how to solve this?
Thanks

2

Hey there!

After playing around with it in my own board, it looks like the reason this is happening is because the formula is trying to divide the 0, which will always return an error. The blank cell is considered equal to 0 so that’s why it is trying to divide the 0 in those empty cells.

Does this help clarify why you might be running into this?

1 Like
3

Hi Joachim,

The problem is that HOURS_DIFF({Heure fin}, {Heure debut}) returns an empty string when there is no data in one of the columns.

Then when you try to extract either the 2 characters on the left or on the right of this empty string… the formula column throws an error.

You’ll need to use if statements to cater for this scenario.

Try:

IF(HOURS_DIFF({Heure fin}, {Heure debut})="",0,LEFT(HOURS_DIFF({Heure fin}, {Heure debut}),2)) + IF(HOURS_DIFF({Heure fin}, {Heure debut})="",0,RIGHT(HOURS_DIFF({Heure fin}, {Heure debut}),2))/60

Looking for a simpler way to write complex formulas? Check out the Advanced Formula Booster. It’s a convenient third-party app that simplifies formula writing. With it, you can spread your formulas across several lines, utilize variables, and access dozens of functions not found in the standard formula column.

1 Like
4

Thanks a lot to both of you. You made my day!