Do I need serial numbers to make Date by month columns?

**KathleenTurnis** · 12-05-2012, 03:22 PM

Hi guys! Ya'll got a newbie non-programer here, so first off, I SO appreicate your help, especially if it's in step-by-steps. : )

Ok, I have 40 datasheets that will need new Month columns for 2013. They only have to say Jan-13, Feb-13, etc. How do I add these new 12 columns to my (several years' old) datasheets? I don't know if this will matter, but these sheets were recently conversted to Sharepoint, which we hated, and converted back into Exel 2007.

Thanks!!
Kathleen

**Pete_UK** · 12-05-2012, 03:44 PM

Type the first date (1/1/2013) into the first cell (suppose this is M1, and you want the other dates to go across). In N1 you can have this formula:

=DATE(YEAR(M1),MONTH(M1)+1,1)

Apply a custom format to those cells of mmm-yy, and then you can copy cell N1 across in O1 onwards (even beyond Dec-2013).

Hope this helps.

Pete

**KathleenTurnis** · 12-05-2012, 03:48 PM

Do I insert the first blank column,type in 1/1/2013, and put in the formula, then insert 11 more columns?
Thanks!

**FDibbins** · 12-05-2012, 04:27 PM

if you have a ton o sheets to do this to, which it sounds like, try this approach...

click on the 1st sheet, click on where you want the new colums to be (the column letter).
then scroll (if you need to) to the last sheet whewre you want to insert the new columns
hold down the shift key and click on the last sheet.

they are now all grouped. whatever you do to 1, will be done on all those sheets. so if you insert 12 columns on 1, it gets done to them all. likewise if you enter Jan 13 in 1, bingo, they all get that in that same cell

a word of warning - make sure you remove "group" when you are done, else you could end up overwriting stuff you didnt want to. remove grouping by either clicking on a tab outside of the group, or by right-clicking on a tab inside the group and selecting ungroup

hope that helps

**KathleenTurnis** · 12-05-2012, 05:26 PM

I have 42 separate datasheets (almost all of the 48 states in the cont. US), but thanks for that tip!

Each state's sheet has between 130 and 500 rows. I can do each sheet once I get on a roll of what to do with the first few.
Thanks...

**KathleenTurnis** · 12-27-2012, 03:36 PM

IA DATASHEET - SHAREPOINT CONVERSION.xlsx
Pete's reply was helpful but when I do the steps, I get a message that says it can't do the specificed action, that it's attempting to shift cells...well yes. That's what I want, isn't it?
I've attached one of the sheets (I have 40 total by state, this one is Iowa). Maybe someone can insert the first set in for me and I can then cut and paste them into the other state's sheets?

There needs to be 12 additional columns, one for each month. Jan-13, Feb-13, etc.

Thanks for help!! : )
Kathleen

**KathleenTurnis** · 12-28-2012, 04:10 PM

IA DATASHEET - SHAREPOINT CONVERSION.xlsx
The replies were helpful but when I do the steps, I get a message that says it can't do the specificed action, that it's attempting to shift cells...well yes. That's what I want, isn't it? Or it makes the date but it says Jan-00. Any changes in the formatting does not seem to change, etiher.

I've attached one of the sheets -see below- (I have 40 total by state, this one is Iowa). Maybe someone can insert the first set in for me and I can then cut and paste them into the other state's sheets?
Time is getting crunched for me and your help would be SO appreicated. ; )
There needs to be 12 additional columns, one for each month. Jan-13, Feb-13, etc.

Thanks for help!! : )
Kathleen

**KathleenTurnis** · 01-03-2013, 03:58 PM

HELP!!!!! Why is no one helping me???? : (
I put the formula in and I get: 1/0/1900 as my result...

=DATE(YEAR(M1),MONTH(M1)+1,1) is NOT working...I added in to the column after after Jan-2013 and I get 1/0/1900.