Avoid errors in an IF statement

Platform discussions
1

I have to columns of times that I need to find the difference between. These are 24hr times and I need to find the hours between an end time on a Monday and start time on the next Tuesday.

I have using this formula:
IF(AND(NOT({Tues Start Time}=“”), NOT({Mon Finish Time}=“”)), ROUND(ABS({Tues Start Time} - {Mon Finish Time}), 2), “”)

However, if a cell is empty, it still returns an error. I have tried using ISBLANK() also, but the same issue occurs.
As well as this, the outputs do not round to 2 decimal places, despite specifying.

I would like the output as a positive number of hours between the two times.

Can you suggest where this formula might be incorrect?

Thanks

2

Hey,
Sure! It looks like your formula could have an issues on these areas:

  1. The empty cell check isn’t working correctly. {Tues Start Time} and {Mon Finish Time} might be stored as numbers or text, and "" might not be correctly identifying blank cells. Instead, use ISBLANK({Column}) or check for "" explicitly.
  2. The time difference calculation needs to handle crossing midnight correctly (since the times are on different days).
  3. Rounding issue – The formula should ensure the calculation explicitly converts the difference to hours.

Try this formula:

IF(AND(NOT(ISBLANK({Tues Start Time})), NOT(ISBLANK({Mon Finish Time}))), 
   ROUND(ABS(({Tues Start Time} + 24) - {Mon Finish Time}), 2), 
   "")

Explanation:

  • NOT(ISBLANK({Column})) ensures both columns have values before performing the calculation.
  • Adding 24 to {Tues Start Time} correctly shifts Tuesday’s start time forward by a full day.
  • ROUND(ABS((...)), 2) ensures a positive number with two decimal places.

This should resolve both the error and rounding issues. Hope that helps! If it does, Let me know with a like! :blush:or ask for help!

3

Hi Clare,

Thanks for getting back.

Using your formula above, calculates the correct times. However, no decimal places are showing and blank cells are still causing errors.

I think it must be the empty cell check that is not working correctly. The blank cells come from a jotform submission. I’m not sure if this is handled differently?

Thanks

4

Hi,

I was wondering if you’re using the Hoyr column, or the Date column with the time enabled?

If you’re using the Date column option, try this
DAYS({Tues Start Time}, {Mon Finish Time}) *24

If you’re using the Hours column rather than text, is a little tricker to get rid of the errors.

5

Hi Matt,

They are hour columns that I am referencing - is there a way around this? Are you suggest a text column would be better?

Thanks

6

I am trying to perform this formula on “hour” columns, on times that are a day apart.

Does anyone have advice about how to get a successful output for this calculation? Whether that is changing column type or something else.

I will, however, not be able to include a date.

Thanks.