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


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.
 
Ad

Advertisements

R

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
 
S

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
 
A

arun

Hi Rick,

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

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
 
S

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
 
Ad

Advertisements

R

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
 
S

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top