How to Show 12 months dates on the basis of from today's date in

[arun]

Hi All,


I wanted to show 12 month dates.

The condition is that, the starting years should show one less i.e 2008 and
after jan the year should show the current year.
the column should look like this

'A1'-Aug-08
'A2'-Sep-08
'A3'-Oct-08
'A4'-Nov-08
'A5'-Dec-08
'A6'-Jan-09
--
--
'A12'-jul-09

the date should be based on current date.

I tried many ways, but not able to do this.

 

[Rick Rothstein]

Put this formula in A1 (or whatever cell you want) and copy it down...

=DATE(YEAR(TODAY())-1,MONTH(TODAY())+ROW(A1)-1,1)

then Custom Format those cells using this pattern...

mmm-yy

 

[Susan]

hi arun -
how do you want this to update itself? every time you open the
workbook? by button? only when it gets to the end of the current
month?
susan

 

[arun]

Hi Rick,

I wanted VB macro code for this, as i am doing other things also through
macro.

 

[Rick Rothstein]

Sorry, I forgot which newsgroup I was answering the question in. Give this a
try...

Sub InsertDates()
Dim X As Long
Const StartCell As String = "A1"
For X = 0 To 11
Range(StartCell).Offset(X).Value = DateSerial(Year(Now) - _
1, Month(Now) + X, 1)
Range(StartCell).Offset(X).NumberFormat = "mmm-yy"
Next
End Sub

 

[Susan]

this is what i came up with (probably can be done differently), but i
didn't know how you wanted to implement it.

'=============================
Option Explicit

Sub mydate()

Dim Ws As Worksheet
Dim c As Range

Set Ws = ActiveWorkbook.Worksheets("Sheet1")
Set c = Ws.Range("a1")

c.Value = Date - 365
c.Offset(1, 0).Value = Date - 335
c.Offset(2, 0).Value = Date - 305
c.Offset(3, 0).Value = Date - 275
c.Offset(4, 0).Value = Date - 245
c.Offset(5, 0).Value = Date - 215
c.Offset(6, 0).Value = Date - 185
c.Offset(7, 0).Value = Date - 155
c.Offset(8, 0).Value = Date - 125
c.Offset(9, 0).Value = Date - 95
c.Offset(10, 0).Value = Date - 65
c.Offset(11, 0).Value = Date - 30
c.Offset(12, 0).Value = Date

End Sub
'============================

the actual numbers might have to be adjusted somewhat. i just removed
30 from each larger date.
hope it helps

susan

 

[Rick Rothstein]

What about if the range crosses a February in a Leap Year?

--
Rick (MVP - Excel)


this is what i came up with (probably can be done differently), but i
didn't know how you wanted to implement it.

'=============================
Option Explicit

Sub mydate()

Dim Ws As Worksheet
Dim c As Range

Set Ws = ActiveWorkbook.Worksheets("Sheet1")
Set c = Ws.Range("a1")

c.Value = Date - 365
c.Offset(1, 0).Value = Date - 335
c.Offset(2, 0).Value = Date - 305
c.Offset(3, 0).Value = Date - 275
c.Offset(4, 0).Value = Date - 245
c.Offset(5, 0).Value = Date - 215
c.Offset(6, 0).Value = Date - 185
c.Offset(7, 0).Value = Date - 155
c.Offset(8, 0).Value = Date - 125
c.Offset(9, 0).Value = Date - 95
c.Offset(10, 0).Value = Date - 65
c.Offset(11, 0).Value = Date - 30
c.Offset(12, 0).Value = Date

End Sub
'============================

the actual numbers might have to be adjusted somewhat. i just removed
30 from each larger date.
hope it helps

susan

 

[Susan]

yeah, that's why i said the ranges might have to be adjusted. it
would probably be ok if you ran it from the 2nd thru the 27th of the
month, but if you ran it on the 1st or the 28-31st of the month it
might screw up. you could add an if statement to check for the last
day of the month. it was an idea................
maybe i should just stop answering posts - others' answers are usually
more sophisticated than mine...........
susan

 

[arun]

Hi Susan,

I have a button, by clicking we need to update that excel.

 

[Susan]

then i'd use rick's code in your button_click sub.
susan