Problem calculating difference in hours between 2 date columns when one of them is empty

Platform discussions
1

Hi,

I am new to monday. Sorry if it is a trivial question but I can’t figure it out.
I want to calculate the number of hours between 2 dates and it raises an error when one of the 2 dates are empty:
DAYS({Resolved On},{Reported On})*24
I tried If without success…

2

Hi John,

Thats a complicated one.

If you didn’t need the hours, this would give you the answer in days, without any error if one or both dates are empty:

DAYS({Resolved On},{Reported On})

The problem starts with you trying to multiply it by 24. Here is the solution:

When one or both dates are empty, the above function returns nothing, so you need to test for nothing before multiplying by 24.

MULTIPLY(IF(DAYS({Resolved On},{Reported On}),DAYS({Resolved On},{Reported On}),0),24)

In this case, you get 0 if one or both dates are not supplied, so if you want the formula cell to remain blank, you need to check the result of the above formula for 0:

IF(MULTIPLY(IF(DAYS({Resolved On},{Reported On}),DAYS({Resolved On},{Reported On}),0),24)=0,"",MULTIPLY(IF(DAYS({Resolved On},{Reported On}),DAYS({Resolved On},{Reported On}),0),24))

 
 

Curious to know how that same formula would look in the Advanced Formula Booster, the 3rd party app that makes it so easy to write formulas, does not use the Formula column but rather can write to any type of columns?

Since the Advanced Formula Booster does not use the formula column, you would create a Hours passed number column to store the value (which you can then re-use everywhere, contrarily to the formula column).

Then you would write a formula like this:

2024-02-26_06-56-10
2024-02-26_06-56-10873×141 16.4 KB

Formulas in AFB can have up to 100 lines (instructions).

  • in the 1st line, we set the target field to “” in case it contains a value (only necessary because of the following 2 lines that might stop the formula).
  • in the following 2 lines, we test if both values are empty and we stop the formula if it is the case (no calculation is needed + we already blanked out the column in the 1st line, so we can stop there).
  • in the last line, since we already know dates are in the 2 columns, we can perform the calculation.

This is a good example of why we decided to create the Advanced Formula Booster: so that everyone could write formulas that work. The app has many more advantages…

1 Like
3

Thanks, Gilles.

It works. Thanks for being so thorough.