Converting text to Date to populate Gant & Calendar App.

kaia2001
kaia2001 ✭
inFormulas and Functions

I have an Allocation sheet where I need the user to choose the month in the "Month" column and the 'Start' populates with the first date of the month and the "End" populates with the last date of the month so I can have a start and finish date to show the resource and Allocation on a Gant and Calendar app. I have tried to use =DATE(YEAR(Month@row), MONTH(Month@row), 1) and =DATE(YEAR(Month@row), MONTH(Month@row) + 1, 1) - 1

but they are formatted as dates. Is there any other way to accomplish this?


image.png


Tags:
· Share on Facebook Share on Twitter

Best Answers

  • MCorbin
    MCorbin Overachievers Alumni
    Answer ✓

    First... I highly recommend having a Cheat Sheet for Date Related info. Here's Mine: (it's referenced in the formula I'm about to give you)

    image.png

    I use this as a reference in a LOT of formulas, especially when I need to get to the last day of a month.


    Now, for your sheet....

    You can create a helper column that extracts the month number from your month dropdown:

    image.png

    The formula I used is:

    =IF(LEFT(Month@row, 3) = "JAN", 1, IF(LEFT(Month@row, 3) = "FEB", 2, IF(LEFT(Month@row, 3) = "MAR", 3, IF(LEFT(Month@row, 3) = "APR", 4, IF(LEFT(Month@row, 3) = "MAY", 5, IF(LEFT(Month@row, 3) = "JUN", 6, IF(LEFT(Month@row, 3) = "JUL", 7, IF(LEFT(Month@row, 3) = "AUG", 8, IF(LEFT(Month@row, 3) = "SEP", 9, IF(LEFT(Month@row, 3) = "OCT", 10, IF(LEFT(Month@row, 3) = "NOV", 11, IF(LEFT(Month@row, 3)) = "DEC")))))))))))


    From there, your formula for the Start Date:

    =DATE(VALUE("20" + RIGHT(Month@row, 2)), [Month Number]@row, 1)


    And the formula for the End Date (which includes a cross sheet reference to my Date Cheat Sheet):

    =DATE(VALUE("20" + RIGHT(Month@row, 2)), [Month Number]@row, INDEX({Last Day}, MATCH([Month Number]@row, {Month No}, 0)))



    Now... if you didn't want to have a helper column, you could technically include that formula inside the other formulas:

    =DATE(VALUE("20" + RIGHT(Month@row, 2)), IF(LEFT(Month@row, 3) = "JAN", 1, IF(LEFT(Month@row, 3) = "FEB", 2, IF(LEFT(Month@row, 3) = "MAR", 3, IF(LEFT(Month@row, 3) = "JAN", 1, IF(LEFT(Month@row, 3) = "FEB", 2, IF(LEFT(Month@row, 3) = "MAR", 3, IF(LEFT(Month@row, 3) = "APR", 4, IF(LEFT(Month@row, 3) = "MAY", 5, IF(LEFT(Month@row, 3) = "JUN", 6, IF(LEFT(Month@row, 3) = "JUL", 7, IF(LEFT(Month@row, 3) = "AUG", 8, IF(LEFT(Month@row, 3) = "SEP", 9, IF(LEFT(Month@row, 3) = "OCT", 10, IF(LEFT(Month@row, 3) = "NOV", 11, IF(LEFT(Month@row, 3) = "DEC", 12))))))))))))))), 1)

    But frankly.... I don't recommend it.... I prefer to create helper columns and hide them....

    · Share on Facebook Share on Twitter
  • kaia2001
    kaia2001 ✭
    Answer ✓

    Great! Thank you! Much appreciated!

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @kaia2001

    Excellent!

    Happy to help!

    ✅Remember!我的帖子(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

  • MCorbin
    MCorbin Overachievers Alumni
    Answer ✓

    First... I highly recommend having a Cheat Sheet for Date Related info. Here's Mine: (it's referenced in the formula I'm about to give you)

    image.png

    I use this as a reference in a LOT of formulas, especially when I need to get to the last day of a month.


    Now, for your sheet....

    You can create a helper column that extracts the month number from your month dropdown:

    image.png

    The formula I used is:

    =IF(LEFT(Month@row, 3) = "JAN", 1, IF(LEFT(Month@row, 3) = "FEB", 2, IF(LEFT(Month@row, 3) = "MAR", 3, IF(LEFT(Month@row, 3) = "APR", 4, IF(LEFT(Month@row, 3) = "MAY", 5, IF(LEFT(Month@row, 3) = "JUN", 6, IF(LEFT(Month@row, 3) = "JUL", 7, IF(LEFT(Month@row, 3) = "AUG", 8, IF(LEFT(Month@row, 3) = "SEP", 9, IF(LEFT(Month@row, 3) = "OCT", 10, IF(LEFT(Month@row, 3) = "NOV", 11, IF(LEFT(Month@row, 3)) = "DEC")))))))))))


    From there, your formula for the Start Date:

    =DATE(VALUE("20" + RIGHT(Month@row, 2)), [Month Number]@row, 1)


    And the formula for the End Date (which includes a cross sheet reference to my Date Cheat Sheet):

    =DATE(VALUE("20" + RIGHT(Month@row, 2)), [Month Number]@row, INDEX({Last Day}, MATCH([Month Number]@row, {Month No}, 0)))



    Now... if you didn't want to have a helper column, you could technically include that formula inside the other formulas:

    =DATE(VALUE("20" + RIGHT(Month@row, 2)), IF(LEFT(Month@row, 3) = "JAN", 1, IF(LEFT(Month@row, 3) = "FEB", 2, IF(LEFT(Month@row, 3) = "MAR", 3, IF(LEFT(Month@row, 3) = "JAN", 1, IF(LEFT(Month@row, 3) = "FEB", 2, IF(LEFT(Month@row, 3) = "MAR", 3, IF(LEFT(Month@row, 3) = "APR", 4, IF(LEFT(Month@row, 3) = "MAY", 5, IF(LEFT(Month@row, 3) = "JUN", 6, IF(LEFT(Month@row, 3) = "JUL", 7, IF(LEFT(Month@row, 3) = "AUG", 8, IF(LEFT(Month@row, 3) = "SEP", 9, IF(LEFT(Month@row, 3) = "OCT", 10, IF(LEFT(Month@row, 3) = "NOV", 11, IF(LEFT(Month@row, 3) = "DEC", 12))))))))))))))), 1)

    But frankly.... I don't recommend it.... I prefer to create helper columns and hide them....

    · Share on Facebook Share on Twitter
  • kaia2001
    kaia2001 ✭
    Answer ✓

    Great! Thank you! Much appreciated!

    · Share on Facebook Share on Twitter
  • kaia2001
    kaia2001 ✭

    @MCorbin你知道为什么它不是认识到列s as being date columns? When I attempt to see a Gant View of the months it says "The Sheet must contain 2 date columns"


    image.png


    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi@kaia2001

    I hope you're well and safe!

    It's because they are set with Column Formulas. If you remove it, it should work.

    Did it work?

    I hope that helps!

    Be safe, and have a fantastic week!

    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
  • kaia2001
    kaia2001 ✭

    It worked! Thank you!!

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @kaia2001

    Excellent!

    Happy to help!

    ✅Remember!我的帖子(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

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
You were, indeed, very close.<\/p>

=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula\n <\/a>\n<\/div>\n

It looks like you forgot to close out the AND <\/strong>funtion. Try this<\/p>

=IF([Invoice Received?]@row = 0, \"Green\", IF(AND([Renewal Date]@row >= TODAY(0), [Renewal Date]@row <= TODAY(+30)), \"Red\", \"Yellow\"))<\/code><\/p>

Hope this helps!<\/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":108832,"type":"question","name":"How do I create a IF(AND formula combining multiple cross references?","excerpt":"In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting different errors using formula: =COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), IF(AND({Dispute & Chargeback…","snippet":"In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting…","categoryID":322,"dateInserted":"2023-08-10T23:46:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:12:56+00:00","insertUserID":140084,"insertUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":140084,"lastUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":28,"score":null,"hot":3383486357,"url":"https:\/\/community.smartsheet.com\/discussion\/108832\/how-do-i-create-a-if-and-formula-combining-multiple-cross-references","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108832\/how-do-i-create-a-if-and-formula-combining-multiple-cross-references","format":"Rich","tagIDs":[207,219,254,344,440],"lastPost":{"discussionID":108832,"commentID":390262,"name":"Re: How do I create a IF(AND formula combining multiple cross references?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390262#Comment_390262","dateInserted":"2023-08-11T17:12:56+00:00","insertUserID":140084,"insertUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/9UG82HSGCS9G\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:23:34+00:00","dateAnswered":"2023-08-11T00:00:03+00:00","acceptedAnswers":[{"commentID":390143,"body":"

I think I am following what you are attempting to do. Try this:<\/p>

COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), {Dispute & Chargeback Intake Sheet Type of CB}, \"AMEX\")<\/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":207,"urlcode":"Sales","name":"Sales"},{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":344,"urlcode":"it-operations","name":"IT & Operations"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]},{"discussionID":108811,"type":"question","name":"If all Child rows = \"Complete\" change Parent to \"Complete\" and send to new sheet","excerpt":"Hello! I have an automation that sends \"Complete\" Parent columns to a new sheet (my \"Closed Orders\" sheet). I also am setting up a formula for when all Children status' are set to \"Complete\" it automatically switches the Parent row to \"Complete\". What I am wanting to do is send ALL rows including Parent and its Children in…","snippet":"Hello! I have an automation that sends \"Complete\" Parent columns to a new sheet (my \"Closed Orders\" sheet). I also am setting up a formula for when all Children status' are set to…","categoryID":322,"dateInserted":"2023-08-10T18:05:54+00:00","dateUpdated":"2023-08-10T18:35:26+00:00","dateLastComment":"2023-08-11T16:30:30+00:00","insertUserID":162263,"insertUser":{"userID":162263,"name":"clairehunter","url":"https:\/\/community.smartsheet.com\/profile\/clairehunter","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:02:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":91566,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-11T17:24:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":28,"score":null,"hot":3383462784,"url":"https:\/\/community.smartsheet.com\/discussion\/108811\/if-all-child-rows-complete-change-parent-to-complete-and-send-to-new-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108811\/if-all-child-rows-complete-change-parent-to-complete-and-send-to-new-sheet","format":"Rich","tagIDs":[254,334],"lastPost":{"discussionID":108811,"commentID":390242,"name":"Re: If all Child rows = \"Complete\" change Parent to \"Complete\" and send to new sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390242#Comment_390242","dateInserted":"2023-08-11T16:30:30+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-11T17:24:44+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-08-11T17:02:06+00:00","dateAnswered":"2023-08-11T16:30:30+00:00","acceptedAnswers":[{"commentID":390242,"body":"

The move row automation will move all children underneath of a parent row if just the parent row is what triggers the automation.<\/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"},{"tagID":334,"urlcode":"automations","name":"Automations"}]}],"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/AND Formula

    Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from "unparsable" to "incorrect…

  • How do I create a IF(AND formula combining multiple cross references?

    In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting different errors using formula: =COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), IF(AND({Dispute & Chargeback…

  • If all Child rows = "Complete" change Parent to "Complete" and send to new sheet

    Hello! I have an automation that sends "Complete" Parent columns to a new sheet (my "Closed Orders" sheet). I also am setting up a formula for when all Children status' are set to "Complete" it automatically switches the Parent row to "Complete". What I am wanting to do is send ALL rows including Parent and its Children in…