Help translating Excel formula to Smartsheet formula

JSpears

I need some help translating a formula from Excel into something that I could use with Smartsheet.

The final formula in Smartsheet would end up being a nested if:

If Enterprise 2022 R1, give # of months between GA date and Go-live date, if EDIS 2022 R1, give # of months between EDGA Date and Go-live Date, if these are false then return "-"

Thank you in advance!

Jennifer

KPH

Try this:

=IF(CONTAINS("Enterprise 2022 R1", Version@row), (ROUND(NETDAYS([GA Ent. 2022 R1]#, [Go-Live Date]@row) / (365.25 / 12), 0)), IF(CONTAINS("EDIS 2022 R1", Version@row), (ROUND(NETDAYS([GA EDIS 2022 R1]#, [Go-Live Date]@row) / (365.25 / 12), 0)), ""))

The extra parenthesis is in bold above, and the ones I removed are in bold below.

=IF(CONTAINS("Enterprise 2022 R1", Version@row), (ROUND(NETDAYS([GA Ent. 2022 R1]#, [Go-Live Date]@row) / (365.25 / 12), 0))), IF(CONTAINS("EDIS 2022 R1", Version@row), (ROUND(NETDAYS([GA EDIS 2022 R1]#, [Go-Live Date]@row) / (365.25 / 12), 0), ""))))

KPH

Part 1 - Create the nested IF function

Assuming the column headings look like this:

Then the nested IF would be like this where the parts in bold are messages to be replaced by formula later (it is easier to explain the IF part first).

=IF([Version Update Name]@row = "Enterprise 2022 R1", "Difference between [Go-Live Date]@row and [GA Date]@row", IF([Version Update Name]@row = "EDIS 2022 R1", "Difference between [Go-Live Date]@row and [EDGA Date]@row", ""))

If you use difference column names you just need to change the parts between the square brackets to refer to the names you use.

Part 2 - Calculate DateDiff

There is not a DATEDIFF function in smartsheet so this will need to be a calculation.

The dates must be in columns formatted as Date Types.

I have assumed Go Live is always the later date.

How accurate do you want the number of months to be? This formula finds the net days between the two dates and divides that by 365.25 days divided by 12 to get the net months.

=NETDAYS([GA Date]@row, [Go-Live Date]@row) / (365.25 / 12)

You could round this result to 2 decimal places like this:

=ROUND(NETDAYS([GA Date]@row, [Go-Live Date]@row) / (365.25 / 12), 2)

If so you can slot it into the formula from part 1 like this:

=IF([Version Update Name]@row = "Enterprise 2022 R1", ROUND(NETDAYS([GA Date]@row, [Go-Live Date]@row) / (365.25 / 12), 2), IF([Version Update Name]@row = "EDIS 2022 R1",ROUND(NETDAYS([EDGA Date]@row, [Go-Live Date]@row) / (365.25 / 12), 2), ""))

Would that work? If not, do you have examples of the result you are looking to achieve?

JSpears

@KPH - Thank you. That worked but I realized that I need to use CONTAINS...so I have this now:

=IF(CONTAINS("Enterprise 2022 R1", Version@row), (ROUND(NETDAYS([GA Ent. 2022 R1]#, [Go-Live Date]@row) / (365.25 / 12), 0))), IF(CONTAINS("EDIS 2022 R1", Version@row), (ROUND(NETDAYS([GA EDIS 2022 R1]#, [Go-Live Date]@row) / (365.25 / 12), 0), ""))))

Not sure where I've gone wrong with this one...I'm sure it's a ( or ) but I can't find it.

Thank you,

Jennifer

KPH

Great! It certainly looks like a ( is the problem. If you look at the color coding you can see that you have two closing ) at the end that are black and do not have partners. I am joining a call right now. See if you can fix it. If not, I will take a proper look when I'm finished.

KPH

Try this:

=IF(CONTAINS("Enterprise 2022 R1", Version@row), (ROUND(NETDAYS([GA Ent. 2022 R1]#, [Go-Live Date]@row) / (365.25 / 12), 0)), IF(CONTAINS("EDIS 2022 R1", Version@row), (ROUND(NETDAYS([GA EDIS 2022 R1]#, [Go-Live Date]@row) / (365.25 / 12), 0)), ""))

The extra parenthesis is in bold above, and the ones I removed are in bold below.

=IF(CONTAINS("Enterprise 2022 R1", Version@row), (ROUND(NETDAYS([GA Ent. 2022 R1]#, [Go-Live Date]@row) / (365.25 / 12), 0))), IF(CONTAINS("EDIS 2022 R1", Version@row), (ROUND(NETDAYS([GA EDIS 2022 R1]#, [Go-Live Date]@row) / (365.25 / 12), 0), ""))))

JSpears

@KPH - You are a ROCK STAR!!! Thank you SO MUCH!!

KPH

No problem at all. Happy to help.