Return a % if date in column

Options

Hi there,

I am trying to create a formula where it identifies if there is a date in the Actual Start Column it is 50%, if there is a date in the Actual Start and Finish column it is 100% and no dates is 0%.

I managed to get it to work in excel using IFS but Smartsheet doesnt use IFS.

I tried using IF and OR but still no luck. Have now just tried IF(CONTAINS), I either get INPARESABLE or INCORRECT ARGUMENT.

Thank you

• ✭✭✭✭✭✭
Options

I assume you have set the Actual Start Date and Actual Finish Date to accept only dates and your % Complete column is formatted as a percentage. If that is the case, you can simplify the formula as =IF([Actual Finish Date]@row <>"", 1, IF([Actual Start Date]@row <> "", 0.5, 0))

Thanks,

Aravind GP| Principal Consultant

Atturra Data & Integration

M: +61493337445

E:Aravind.GP@atturra.com

W: www.atturra.com

• ✭✭✭✭✭✭
Options

Hi @Mel Collis,

To set the column as a percentage, click the column header and press this button on the toolbar:

This will turn the numbers in the formula already posted into percentages (1=100%, 0.5=50%, etc.).

• ✭✭✭✭✭✭
Options

I assume you have set the Actual Start Date and Actual Finish Date to accept only dates and your % Complete column is formatted as a percentage. If that is the case, you can simplify the formula as =IF([Actual Finish Date]@row <>"", 1, IF([Actual Start Date]@row <> "", 0.5, 0))

Thanks,

Aravind GP| Principal Consultant

Atturra Data & Integration

M: +61493337445

E:Aravind.GP@atturra.com

W: www.atturra.com

• Options

Thank you Aravind, I have set the date columns but unsure how to set %complete column to format as a percentage, could you advise please?

• ✭✭✭✭✭✭
Options

Hi @Mel Collis,

To set the column as a percentage, click the column header and press this button on the toolbar:

This will turn the numbers in the formula already posted into percentages (1=100%, 0.5=50%, etc.).

• Options

Thank you Nick!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!