Add another month to last month

[Guest]

Hi
Is it possible to add a formatted month to a preceding month with two
columns formatted for text between?

Eg: Cell A1= the full date (1st Jan 2007), C4= A1 and formats to "Jan",
Cells D4 & E4 formatted for text, cell F4 = "Feb" and so on....

It can be in VBA or Macro if necessary.
Many thanks

 

[Roger Govier]

Hi Cosmo

In cell C4 = A1, Format>cells>Number>Custom> mmm
In cell F4
=DATE(YEAR(C4),MONTH(C4)+1,1)
again format as mmm
Format cells D4:E4 as Text
Copy cells F4:H4
Select cells I4:AL4 >Paste

 

[Guest]

Not sure but you could try this formulas play ..

With the commencement date in A1,

Put in C4:
=IF($A1="","",IF(MOD(COLUMN(A1),3)=1,TEXT(DATE(YEAR($A1),MONTH($A1)+INT((COLUMN(A1)-1)/3),1),"mmm"),""))
Copy C4 across as far as required

The above should return the required consecutive text "months" in C4, F4,
I4, etc depending on the date input in A1. In-between cells eg: D4:E4, G4:H4,
etc will appear blank.

 

[Guest]

Hi Roger
Thanks, this worked ok but, did rather hope I could have clicked on F4 and
updated the month by a factor of one from cell C4.
Maybe I'm talking out of my hat. But thanks for trying.

 

[Guest]

Hello Max
Gave yours a whirl but no joy for the moment. As in my reply to Roger, did
hope I could update F4 from C4 by one month

 

[Roger Govier]

Hi Cosmo

F4 does read 1 month greater than C4, and I4 reads 1 month greater than
F4.
Max's solution is even better, his formula copied across does put the
succeeding months in the correct columns.

If you are saying you want all months to be updated by a month, then
just alter the date in A1 to 01 Feb 2007 and all the other months will
alter accordingly.

 

[Max]

Yes, that's what should be returned, with all months consecutive in C4, F4,
I4 ... as mentioned, and commencing from the date's month as input in A1.
Trust that you've got it working by now.

 

[Guest]

Thanks to all replies and yes have got it working.

Many thanks and please be assured, I'll be back for more help
Cosmo

 

[Max]

Good to hear that !