Looking to auto populate a new column with month numbers using dates shown in another cell

[nutbolt]

In Excel I have cells in column C that have dates showing every month from the 05.12.13 through to 05.10.14 the fifth day of every month being the deadline date. I wanted to poplulate another column next to these dates (column D) as :

05.12.13 = month 8
05.01.14 = month 9
05.02.14 = month 10
05.03.14 = month 11
05.04.14 = month 12
05.05.14 = month 1
05.06.14 = month 2
eg...all the way to..
05.10.14 = month 6

month 1 starts from 05.05 and ends 05.04 every year.

how do I make the new column D populate with the month numbers in a macro??

[boopathiraja]

Hi, Welcome to the forum

Apply this in d1 and drag it down

=MONTH(EDATE(C1-4,-4))

[vogel997]

Month.xlsx

Try this see attached.

IF(MONTH(C6)-4<=0,MONTH(C6)-4+12,MONTH(C6)-4)

[nutbolt]

Attachment 357677

Try this see attached.

IF(MONTH(C6)-4<=0,MONTH(C6)-4+12,MONTH(C6)-4)

yes this worked well thanks. How do I get it to populate in cell range d2 to d50??

[FDibbins]

Based onthe samples you provided (and your location), I have a feeling those are not dates, but text looking like dates. Test with =isnumber(cell-ref) FALSE = text, and you will need to convert to dates befor you can do anything else

[nutbolt]

Your right fdibbens. The dates come from SAP netweaver so at the end of the macro adjusted to format columns in date format.

[nutbolt]

Will do some great googling

[newdoverman]

This might be what you are looking for. Enter in D1 or other column formatted as General and copy down.

="Month "&MONTH(EDATE(SUBSTITUTE(C1,".","/")*1,-4))

[boopathiraja]

it that case you can adopt the formula in post # 8 by Mr Newdoverman with a small tweak

="Month "&MONTH(EDATE(SUBSTITUTE(C1,".","/")*1-4,-4))

[nutbolt]

it that case you can adopt the formula in post # 8 by Mr Newdoverman with a small tweak

="Month "&MONTH(EDATE(SUBSTITUTE(C1,".","/")*1-4,-4))

okay thanks for all replies will give them a try

[boopathiraja]

In Excel I have cells in column C that have dates showing every month from the 05.12.13 through to 05.10.14 the fifth day of every month being the deadline date. I wanted to poplulate another column next to these dates (column D) as :

So if the give date is 04.12.2013 = you want the month number as 7 isn't it ?