rrandall
(Ryan Randall)
April 8, 2022, 1:47pm
#1
I am needing to calculate the days between two dates but if there are no dates, I would like the formula to return the number 0. I would normally use iferror in excel to run this but that isn’t an option on monday.

Any advice?

days({ End Date},{Start Date})

Thank you!

JCorrell
(Jim - The Monday Man)
April 8, 2022, 4:42pm
#2
@rrandall

IF(AND({End Date}, {Start Date}), DAYS({End Date}, {Start Date}), 0)

Monday Man ever the genius. How would I apply the same logic to a formula that is doing the same but with times. I have tried a couple of ways but didn’t work:

IF({Dep D2} < {Arr D1 (Rtn)}, HOURS_DIFF({Arr D1 (Rtn)}, {Dep D2}), HOURS_DIFF({Dep D2}, {Arr D1 (Rtn)}))

Thanks.

JCorrell
(Jim - The Monday Man)
July 14, 2022, 10:41am
#4
@Noelie2000

Assuming your formula is working…

```
IF(AND({Dep D2}, {Arr D1 (Rtn)}),
IF({Dep D2} < {Arr D1 (Rtn)}, HOURS_DIFF({Arr D1 (Rtn)}, {Dep D2}), HOURS_DIFF({Dep D2}, {Arr D1 (Rtn)}))
, 0)
```

Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you?
We Create Custom Solutions
Schedule a 1-on-1 Tutorial Session (for monday, Make & “Paper or Plastic”)

Outstanding that works. I get the same problem when I try to change that field to decimal:

MID({Drive D2-D1Rtn},1,LEN({Drive D2-D1Rtn})-3)+right({Drive D2-D1Rtn},2)/60

Could the same rationale be added to that to return 0 or possibly instead of 0 in the original formula return 00:00 so it is a time.

Sorry is cooking my brain.

JCorrell
(Jim - The Monday Man)
July 15, 2022, 12:58am
#6
@Noelie2000

If you looking to turn the difference into decimal hours, you might try this:

```
IF(AND({H1}, {H2}),
ABS(TIMEVALUE("1/1/1 " & {Dep D2}) - TIMEVALUE("1/1/1 " & {Arr D1 (Rtn)})) * 24
, 0)
```

Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you?
We Create Custom Solutions
Schedule a 1-on-1 Tutorial Session (for monday, Make & “The High Price of Getting By”)

1 Like

JCorrell:

```
IF(AND({H1}, {H2}),
ABS(TIMEVALUE("1/1/1 " & {Dep D2}) - TIMEVALUE("1/1/1 " & {Arr D1 (Rtn)})) * 24
, 0)
```

That just totally made me rethink the way I calculated the columns; reduced the process by half.

Many thanks.

1 Like

system
(system)
closed
July 22, 2022, 8:49am
#8
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.