Error while transfering a formula from Excel to Smartsheet

Etienne Desbiens

Hi!

 

Someone tried to transfer a formula from excel to smartsheet... The only problem is that the formula is quite long and there is one/multiple error(s) left.

Can someone help? I've found a few ones ( ; instead of , ... WEEKNUM instead of WEEKNUMBER) but I can't get it to work...

Here it is. I colored the different if parts to help when looking at the formula.

 

=IF(ISBLANK([Kick off Date]51),"",IF(WEEKNUMBER(IF(YEAR([Kick off Date]51+([Timing (weeks)]51*7))-YEAR([Kick off Date]51)=1, [Kick off Date]51+((([Timing (weeks)]51+2)*7), [Kick off Date]51+(([Timing (weeks)]51*7)),1))>51,IF(YEAR([Kick off Date]51+(([Timing (weeks)]51*7))-YEAR([Kick off Date]51)=1,[Kick off Date]51+((([Timing (weeks)]51+2)*7), [Kick off Date]51+(([Timing (weeks)]51*7))+(2*7),IF(YEAR([Kick off Date]51+(([Timing (weeks)]51*7))-YEAR([Kick off Date]51)=1, [Kick off Date]51+((([Timing (weeks)]51+2)*7), [Kick off Date]51+(([Timing (weeks)]51*7))

Kennedy Stomps

Hi Etienne-- Would you be able to share this sheet (or a copy) with me at Kennedy.Stomps@Smartsheet.com? I'll be happy to help troubleshoot once I can get a better look at it

J. Craig Williams

Your () are mismatched.

 

This section (near the end of the IF in the WEEKNUMBER)

 

Date]51+(([Timing (weeks)]51*7)),1))>51,IF(YEAR([ 

 

looks odd.

 

If you could put it into words what you are trying to accomplish, the formula might be simpler to write.

Craig

 

 

 

 

Etienne Desbiens

My problem is that I actually don't know what they want that formula to do... Someone that doesn't know how it's supposed to be used gave me an excel file containing it and told me to translate to smartsheet...

J. Craig Williams

Does the formula work in Excel?

It looks like it wouldn't, but I don't have Excel to test it on.

Craig

Etienne Desbiens

This is the adaptation of the formula for Smartsheet... The excel formula works fine.

 

J. Craig Williams

Can you post the Excel formula?

It might be easier to fix.

 

Craig