| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Show 12 months dates on the basis of from today's date in
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to Show 12 months dates on the basis of from today's date in
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 -- Rick (MVP - Excel) "arun" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to Show 12 months dates on the basis of from today's date in
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 On Aug 12, 9:54*am, arun wrote: 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to Show 12 months dates on the basis of from today's date
Hi Rick,
I wanted VB macro code for this, as i am doing other things also through macro. "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "arun" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to Show 12 months dates on the basis of from today's date
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 -- Rick (MVP - Excel) "arun" wrote in message ... Hi Rick, I wanted VB macro code for this, as i am doing other things also through macro. "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "arun" wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to Show 12 months dates on the basis of from today's date
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 On Aug 12, 10:44*am, arun wrote: Hi Rick, I wanted *VB macro code for this, as i am doing other things also through macro. "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "arun" wrote in message ... 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.- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to Show 12 months dates on the basis of from today's date
What about if the range crosses a February in a Leap Year?
-- Rick (MVP - Excel) "Susan" wrote in message ... 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 On Aug 12, 10:44 am, arun wrote: Hi Rick, I wanted VB macro code for this, as i am doing other things also through macro. "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "arun" wrote in message ... 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.- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to Show 12 months dates on the basis of from today's date
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 On Aug 12, 11:15*am, "Rick Rothstein" wrote: What about if the range crosses a February in a Leap Year? -- Rick (MVP - Excel) "Susan" wrote in message ... 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 On Aug 12, 10:44 am, arun wrote: Hi Rick, I wanted VB macro code for this, as i am doing other things also through macro. "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "arun" wrote in message ... 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to Show 12 months dates on the basis of from today's date
Hi Susan,
I have a button, by clicking we need to update that excel. "Susan" wrote: 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 On Aug 12, 9:54 am, arun wrote: 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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to Show 12 months dates on the basis of from today's date
then i'd use rick's code in your button_click sub.
susan On Aug 13, 12:37*am, arun wrote: Hi Susan, I have a button, by clicking we need to update that excel. "Susan" wrote: 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 On Aug 12, 9:54 am, arun wrote: 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.- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| show the date when it is later than today's date | Excel Worksheet Functions | |||
| Macro to locate today's date from a list of dates | Excel Programming | |||
| sum of 2 Months basis | Excel Worksheet Functions | |||
| how do I put today's date and other dates in macro condition? | Excel Programming | |||
| Are 2 dates between today's date | Excel Programming | |||
Linear Mode
