Auto generating mmm-yy cell

Richard Heath · 06/14/19

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.

Andrée Starå · 06/14/19

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

Andrée Starå · 06/14/19

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

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

Paul Newcome · 06/14/19

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å · 06/14/19

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.

Paul Newcome · 06/14/19

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å · 06/14/19

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

Paul Newcome · 06/14/19

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å · 06/16/19

Yes!

Sometimes trial and error reveal some gems.

Richard Heath · 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 2.JPG

Andrée Starå · 06/20/19

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

Auto generating mmm-yy cell

Comments

Help Article Resources

Categories