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

  • Thread starter Thread starter arun
  • Start date Start date
A

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.
 
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
 
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
 
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
 
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
 
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
 
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
 
Back
Top