Auto generating mmm-yy cell
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.
Comments
-
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.
-
Screenshot! (Couldn't add to the other post, got blocked by Cloudflare)
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.
-
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.
-
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.
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.
-
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?
-
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.
-
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.
-
Yes!
Sometimes trial and error reveal some gems.
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.
-
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.
-
Happy to help!
Nicely done!
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!