| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add another month to last month
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 -- Cosmo |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add another month to last month
Hi Cosmo
In cell C4 = A1, FormatcellsNumberCustom 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 -- Regards Roger Govier "Cosmo" wrote in message ... 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 -- Cosmo |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add another month to last month
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(YEA R($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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Cosmo" wrote: 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 -- Cosmo |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add another month to last month
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. -- Cosmo "Roger Govier" wrote: Hi Cosmo In cell C4 = A1, FormatcellsNumberCustom 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 -- Regards Roger Govier "Cosmo" wrote in message ... 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 -- Cosmo |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add another month to last month
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 -- Cosmo "Max" wrote: 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(YEA R($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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Cosmo" wrote: 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 -- Cosmo |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add another month to last month
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. -- Regards Roger Govier "Cosmo" wrote in message ... 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. -- Cosmo "Roger Govier" wrote: Hi Cosmo In cell C4 = A1, FormatcellsNumberCustom 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 -- Regards Roger Govier "Cosmo" wrote in message ... 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 -- Cosmo |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add another month to last month
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Cosmo" wrote in message ... 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 -- Cosmo |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add another month to last month
Thanks to all replies and yes have got it working.
Many thanks and please be assured, I'll be back for more help Cosmo -- Cosmo "Max" wrote: 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Cosmo" wrote in message ... 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 -- Cosmo |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add another month to last month
Good to hear that !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Cosmo" wrote in message ... Thanks to all replies and yes have got it working. Many thanks and please be assured, I'll be back for more help Cosmo |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match Last day of this month to last day of last month | Excel Discussion (Misc queries) | |||
| Finding values based on current month | Excel Worksheet Functions | |||
| How to create a rolling 6 month average? | Excel Discussion (Misc queries) | |||
| Working days left in the month compared to previous months | Excel Worksheet Functions | |||
| Month Year Date Format | Excel Worksheet Functions | |||
Linear Mode
