Formula conversion from Excel to Monday

Platform discussions Special workflows & use cases
1

Hello! Out team tracks dates of when something started and when that same thing ended.

We have a formula in Excel that calculates the number of days between two dates. If no start date than it returns blank. If no end date, then it uses Today (results in the number increasing by 1 everyday).

This same formula does not work in Monday.

Here is the Excel Formula: =IF(B2=“”,“”,IF(C2=“”,TODAY()-B2,C2-B2))
Where B2 is the start date and C2 is the end date.

Monday evuivalent that doesn’t seem to work:
=IF({Date Investigator Assigned}=“”,“”,IF({Date Investigation Report Received}=“”,TODAY()-{Date Investigator Assigned},{Date Investigation Report Received}-{Date Investigator Assigned}))

Any guidance would be appreciated.

2

Hey Christina!

Quick q - are you including the = sign at the beginning of your formula in the monday formula column? If so, you should exclude it and see if that helps your formula show up as it should!

3

Hi! Yes it was there. I removed it but the formula is still not showing results. Just a red exclamation mark.

4

Hi Christina,

Monday doesn’t just let you subtract dates usually. Using the DAYS function is normally better. How is this?

IF(
    {Date Investigator Assigned}="",
    "",
    IF(
        {Date Investigation Report Received}="",
        DAYS(TODAY(),{Date Investigator Assigned}),
        DAYS({Date Investigation Report Received},{Date Investigator Assigned})
    )
)
1 Like
5

That worked! Thank you!

Here is a screenshot of what it looks like:

image
image716×621 29.2 KB

Any chance it could be tweaked to round to the next whole number?

6

Hi Christina,

Sure! There’s a function for that too. :slight_smile:

If you get some spare time, I’d recommend browsing the function list, as that can generate a lot of ideas. The Formula Column is a powerful feature!

ROUNDUP(
IF(
    {Date Investigator Assigned}="",
    "",
    IF(
        {Date Investigation Report Received}="",
        DAYS(TODAY(),{Date Investigator Assigned}),
        DAYS({Date Investigation Report Received},{Date Investigator Assigned})
    )
),0
)

How’s that? And please consider marking my reply a solution if it works. :slight_smile:

1 Like
7

Ah-mazing! thanks so much!

8

Is there a way to make this show only business days?