Return a % if date in column
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.
It is taking to long to figure out myself. Could someone please help?
Thank you
Best Answers
-
Hi @Mel Collis
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
-
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.).
Answers
-
Hi @Mel Collis
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
-
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?
-
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.).
-
Thank you Nick!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!