Auto generating mmm-yy cell

Richard Heath
Richard Heath ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hello,

We have set up a cashflow sheet that gets graphed within a Project Dashboard.

The example attached suggests that the Project commences in January 2019. Ideally, a user could enter the commencement month mmm-yy in Column 4 (Green Cell) and the adjacent cells automatically increase incrementally by one month across the page.

Any suggestions how this could be achieved? Keeping in mind all the data below these cell are treated as $numbers. 

Thanks,

Richard. 

Capture.JPG

Tags:

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Richard,

    Heres one way!

    I'd suggest using a helper column (Date). We can call it Start Date.

    Try something like this.

    Row 2 in the Start Date column and then drag fill that down until it's showing December.

    =DATE(YEAR([Start Date]1); MONTH([Start Date]1) + 1)

    Row 1 - Month 1

    =IF(MONTH([Start Date]1) = 1; "Jan"; IF(MONTH([Start Date]1) = 2; "Feb"; IF(MONTH([Start Date]1) = 3; "Mar"; IF(MONTH([Start Date]1) = 4; "Apr"; IF(MONTH([Start Date]1) = 5; "May"; IF(MONTH([Start Date]1) = 6; "Jun"; IF(MONTH([Start Date]1) = 7; "Jul-"; IF(MONTH([Start Date]1) = 8; "Aug-"; IF(MONTH([Start Date]1) = 9; "Sep"; IF(MONTH([Start Date]1) = 10; "Oct"; IF(MONTH([Start Date]1) = 11; "Nov"; IF(MONTH([Start Date]1) = 12; "Dec")))))))))))) + "-" + YEAR([Start Date]1)

    Row 1 - Month 2

    =IF(MONTH([Start Date]2) = 1; "Jan"; IF(MONTH([Start Date]2) = 2; "Feb"; IF(MONTH([Start Date]2) = 3; "Mar"; IF(MONTH([Start Date]2) = 4; "Apr"; IF(MONTH([Start Date]2) = 5; "May"; IF(MONTH([Start Date]2) = 6; "Jun"; IF(MONTH([Start Date]2) = 7; "Jul-"; IF(MONTH([Start Date]2) = 8; "Aug-"; IF(MONTH([Start Date]2) = 9; "Sep"; IF(MONTH([Start Date]2) = 10; "Oct"; IF(MONTH([Start Date]2) = 11; "Nov"; IF(MONTH([Start Date]2) = 12; "Dec")))))))))))) + "-" + YEAR([Start Date]2)

    Row 3 - Month 3 =Continue the pattern.

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Have a fantastic weekend and let me know if you have questions!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Screenshot! (Couldn't add to the other post, got blocked by Cloudflare)

    CleanShot 2019-06-14 at 09.37.21@2x.png

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Blocked again??

     

    And (unless you're reading between the lines again) I think they are trying to automate that top row.

     

    I enter Jan-2019 into the green cell, and the rest of the cells to the right automatically update to be in order from left to right in the same format.

     

    If that's the case, I was thinking something along the lines of an INDEX/MATCH to convert month numbers to the text (Jan, Feb, Mar, etc...), a basic COUNTIFS to add the correct number to the starting date based on how far to the right it is, and then a basic IF statement to drive the year change portion based on the month of the previous cell.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Yep!

    Haha!

    My solution would be automatic! Add the structure once, and then you're off to the races! I've used this method for a client recently but for a daily task list. yes

    That said, I was thinking about an INDEX/MATCH set up as well but this was simpler because I just did something similar.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I see it now. I had to read your solution a few times to be able to visualize it. The screenshot was throwing me off because it was looking like you were just sending the dates down the column.

     

    Sorry about that.

     

    My first question would be... What if I am looking out 12 months, but I enter July 2019 for my start? How would that year change look when you need to go back to month 1 but year +1? Would you use an IFERROR on the DATE formula going down the Start Date column?

     

    Next question would be... How would you go about giving the users the ability to enter the start date in "mmm-yyyy" format instead of entering an actual date?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Haha! No worries!

    In my example, it's only for the current year. For the start date, they only need to input the month name.

    Best,

    Andrée

     

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah! Got it. I didn't know you could do that in a date column. I had read somewhere about enter Mon, Tue, etc for the current week. I don't know why I never tried it for the month.

     

    That's pretty slick.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Yes!

    Sometimes trial and error reveal some gems. wink

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Richard Heath
    Richard Heath ✭✭✭✭✭✭
    edited 06/20/19

    Thanks for all the ideas. After playing around, I thought your formula was a little too complex Andrée so I've looked to a simple LOOKUP formula which will probably sit at the bottom of the sheet (unless I can get it to be Referenced from its own sheet). Perhaps a LOOKUP IF?

    It looks at the adjacent cell (Row 1), then LOOKUP a table and references a cell next to it, and the pattern repeats itself.

    A user simply needs to input the Starting Month in the Green Cell (Month 1) and the formula runs automatically.

    Thanks again.

    Capture.JPG

    Capture 2.JPG

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    Nicely done! yes

    There's always more than one way to do things. I like yours because it makes it easy to keep it updated.

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!