How do I create an if blank then this and if populated then that statement?

AMcElfresh
AMcElfresh ✭
inFormulas and Functions

Hello! Below is my current formula for a column to calculate days behind.

=[Planned Start Date]@row - TODAY()

The problem is we want this sheet to be accurate without having to have cells have different formulas.

What I would like it to calculate is IF[Actual Start Date]@rowis populated, then[Planned Start Date]@row-[Actual Start Date]@row. IF[Actual Start Date]@rowISBLANK, then[Planned Start Date]@row-TODAY.

· Share on Facebook Share on Twitter

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi@AMcElfresh

    I hope you're well and safe!

    Try something like this.

    =IF([Actual Start Date]@row <> "", [Planned Start Date]@row - [Actual Start Date]@row, [Planned Start Date]@row - TODAY())

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic weekend!

    Best,

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    ✅我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Make sure all three columns are set as date type columns.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107871,"type":"question","name":"Want to compare values in 3 columns to return a value in 4th column","excerpt":"Trying to sum up all many active projects are over due. Can add up my active projects with formula: =if(and(type@row=\"project\",status@row=\"in progress),1,0) but now I want to add the criteria of target completion date is late. Tried the following but get the dreaded \"unparseable\" message... =if((and(\"target completion…","snippet":"Trying to sum up all many active projects are over due. Can add up my active projects with formula: =if(and(type@row=\"project\",status@row=\"in progress),1,0) but now I want to add…","categoryID":322,"dateInserted":"2023-07-20T17:34:51+00:00","dateUpdated":null,"dateLastComment":"2023-07-20T17:52:55+00:00","insertUserID":139408,"insertUser":{"userID":139408,"name":"BJTidi","url":"https:\/\/community.smartsheet.com\/profile\/BJTidi","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T17:53:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":139408,"lastUser":{"userID":139408,"name":"BJTidi","url":"https:\/\/community.smartsheet.com\/profile\/BJTidi","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T17:53:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3379751266,"url":"https:\/\/community.smartsheet.com\/discussion\/107871\/want-to-compare-values-in-3-columns-to-return-a-value-in-4th-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107871\/want-to-compare-values-in-3-columns-to-return-a-value-in-4th-column","format":"Rich","lastPost":{"discussionID":107871,"commentID":386225,"name":"Re: Want to compare values in 3 columns to return a value in 4th column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386225#Comment_386225","dateInserted":"2023-07-20T17:52:55+00:00","insertUserID":139408,"insertUser":{"userID":139408,"name":"BJTidi","url":"https:\/\/community.smartsheet.com\/profile\/BJTidi","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T17:53:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-20T17:53:25+00:00","dateAnswered":"2023-07-20T17:47:21+00:00","acceptedAnswers":[{"commentID":386222,"body":"

I am not sure I am interpreting your request correctly. Is this what you are trying to accomplish?<\/p>

=IF(AND(type@row=\"project\", status@row=\"in progress\", [target completion date]@row < TODAY()), 1, 0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107853,"type":"question","name":"Need help with a date formula to always return a Friday date.","excerpt":"I see there is a standard formula and you just need to change the last number depending on the day of the week. Date@row - WEEKDAY(Date@row) + 2 The \"+ 2\" can be modified to pick a different day of the week. 1 = Sunday, 3 = Tuesday, etc. Here is the formula on my sheet but is shows #unparseable. =Customer Date Needed@row -…","snippet":"I see there is a standard formula and you just need to change the last number depending on the day of the week. Date@row - WEEKDAY(Date@row) + 2 The \"+ 2\" can be modified to pick…","categoryID":322,"dateInserted":"2023-07-20T13:20:08+00:00","dateUpdated":null,"dateLastComment":"2023-07-20T18:36:28+00:00","insertUserID":163628,"insertUser":{"userID":163628,"name":"Jose G.","title":"Planner","url":"https:\/\/community.smartsheet.com\/profile\/Jose%20G.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T18:39:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":147819,"lastUser":{"userID":147819,"name":"MichaelTCA","url":"https:\/\/community.smartsheet.com\/profile\/MichaelTCA","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!naloP9MDFDs!u7kqZ3eepDs!ZlXWVSCN_A6","dateLastActive":"2023-07-20T20:03:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":51,"score":null,"hot":3379741596,"url":"https:\/\/community.smartsheet.com\/discussion\/107853\/need-help-with-a-date-formula-to-always-return-a-friday-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107853\/need-help-with-a-date-formula-to-always-return-a-friday-date","format":"Rich","lastPost":{"discussionID":107853,"commentID":386244,"name":"Re: Need help with a date formula to always return a Friday date.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386244#Comment_386244","dateInserted":"2023-07-20T18:36:28+00:00","insertUserID":147819,"insertUser":{"userID":147819,"name":"MichaelTCA","url":"https:\/\/community.smartsheet.com\/profile\/MichaelTCA","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!naloP9MDFDs!u7kqZ3eepDs!ZlXWVSCN_A6","dateLastActive":"2023-07-20T20:03:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-20T17:56:13+00:00","dateAnswered":"2023-07-20T13:28:45+00:00","acceptedAnswers":[{"commentID":386145,"body":"

If your column name contains spaces, you will need to enclose it in square brackets.<\/p>

=[Customer Date Needed]@row - WEEKDAY([Customer Dated Needed]@row) + 6<\/p>"},{"commentID":386152,"body":"

Column names containing a space, number, and\/or special character need to be wrapped in [square brackets]<\/strong> when referenced in a formula.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • IF Statement with multiple dates an count.

    Hello, I have a large sheet that I am trying to calculate several dates to populate dependent on a Count in a row field (ie - a count of 0 would generate a date being pulled from one column, and a count of one generating a date from another column). Columns in Question: "Was Rejected" - this is a count on either it has…

  • Want to compare values in 3 columns to return a value in 4th column

    Trying to sum up all many active projects are over due. Can add up my active projects with formula: =if(and(type@row="project",status@row="in progress),1,0) but now I want to add the criteria of target completion date is late. Tried the following but get the dreaded "unparseable" message... =if((and("target completion…

  • Need help with a date formula to always return a Friday date.

    I see there is a standard formula and you just need to change the last number depending on the day of the week. Date@row - WEEKDAY(Date@row) + 2 The "+ 2" can be modified to pick a different day of the week. 1 = Sunday, 3 = Tuesday, etc. Here is the formula on my sheet but is shows #unparseable. =Customer Date Needed@row -…