I have date related data scribbled on paper. It is a record of readout
data taken from an elapsed time meter that is connected to my oil
burner. I want to enter these data into XL and then do some number
crunching. The DATEDIF function is certainly going to be used. The
elapsed time data was not collected daily, so there will also be a lot
of Row deletions necessary.

So, I must have in a column every date of each year going back to JAN
of 1992. I have built such a listing from yr 2004 to present using
the autofill feature. I am now looking for a template, perhaps, or a
more elegant method. I will probably want to put each year onto a
separate worksheet, and then use a summary sheet to do calculations.

Is there a template available that would put 1 Jan 1992 through 31 Dec
2003 into say column A ?

Is there a function that will accomplish this task more elegantly and
more easily than the drag and auto fill method to fill in each year's
column of dates?

TNX.

Vince

What I would do is set up a kind of template sheet to create other sheets
from. In that sheet you need to create a series 'manually' by entering Jan 1
of a year into a cell (A2 ?) and filling the series down the sheet. Be sure
to allow for leap years <g.

Once you've done that once, things get easier. Lets say you set it up
starting with Jan 1, 2007. Now you need a sheet for Jan 1, 2008. Copy the
sheet, change the year in the first cell to 2008 and choose that cell again,
move the cursor toward its lower right corner until it becomes a small + sign
and double-click, the rest of the cells will be updated to the new year's
dates.

This allows you to delete rows on the sheet later on without disrupting the
date series, which would happen if you had formulas in those cells to create
the date based on something like the date in the cell above it.


Or, if you want to put this code into a workbook and use it, it will create
sheets for the years specified in the FOR command line:
Sub CreateYearSheets()
Dim theYears As Integer
For theYears = 1992 To 2008
ThisWorkbook.Worksheets.Add
ActiveSheet.Name = theYears
Range("A1").Select
ActiveCell.FormulaR1C1 = "1/1/" & Trim(Str(theYears))
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A366")
Range("A1").Select
Next
End Sub


The sheets will end up with latest year 'on top' - if you would rather that
the earliest year be the top/left-most sheet, then change the For line to read

For theYear = 2008 to 1992 Step -1

No changes to the rest are needed. You can change the years as you choose.

To insert the code, use [Alt]+[F11] to get into the VB Editor. Just cut and
paste the code from above into the module provided there. If you don't see a
big code sheet to paste it into, here's a page with very detailed
instructions on getting in to the VB Editor and bending it to your will so
that you can put code like this into it (creating a Module and making it
visible so you can paste into it)
http://www.jlathamsite.com/Teach/Excel_GP_Code.htm

You can always ask for HelpFrom @ jlathamsite.com (remove spaces) - but that
doesn't mean it's always available <g



"Vince" wrote:



I have date related data scribbled on paper. It is a record of readout
data taken from an elapsed time meter that is connected to my oil
burner. I want to enter these data into XL and then do some number
crunching. The DATEDIF function is certainly going to be used. The
elapsed time data was not collected daily, so there will also be a lot
of Row deletions necessary.

So, I must have in a column every date of each year going back to JAN
of 1992. I have built such a listing from yr 2004 to present using
the autofill feature. I am now looking for a template, perhaps, or a
more elegant method. I will probably want to put each year onto a
separate worksheet, and then use a summary sheet to do calculations.

Is there a template available that would put 1 Jan 1992 through 31 Dec
2003 into say column A ?

Is there a function that will accomplish this task more elegantly and
more easily than the drag and auto fill method to fill in each year's
column of dates?

TNX.

Vince

That double click trick did the job very nicely. TNX.

As for poking in your suggested code; I will give it a try, but it
will be my first time ever !

TNX very much.

Regards,
de ~ Vince ~


On Sun, 18 Feb 2007 18:16:02 -0800, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote:

What I would do is set up a kind of template sheet to create other sheets
from. In that sheet you need to create a series 'manually' by entering Jan 1
of a year into a cell (A2 ?) and filling the series down the sheet. Be sure
to allow for leap years <g.

Once you've done that once, things get easier. Lets say you set it up
starting with Jan 1, 2007. Now you need a sheet for Jan 1, 2008. Copy the
sheet, change the year in the first cell to 2008 and choose that cell again,
move the cursor toward its lower right corner until it becomes a small + sign
and double-click, the rest of the cells will be updated to the new year's
dates.

This allows you to delete rows on the sheet later on without disrupting the
date series, which would happen if you had formulas in those cells to create
the date based on something like the date in the cell above it.


Or, if you want to put this code into a workbook and use it, it will create
sheets for the years specified in the FOR command line:
Sub CreateYearSheets()
Dim theYears As Integer
For theYears = 1992 To 2008
ThisWorkbook.Worksheets.Add
ActiveSheet.Name = theYears
Range("A1").Select
ActiveCell.FormulaR1C1 = "1/1/" & Trim(Str(theYears))
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A366")
Range("A1").Select
Next
End Sub


The sheets will end up with latest year 'on top' - if you would rather that
the earliest year be the top/left-most sheet, then change the For line to read

For theYear = 2008 to 1992 Step -1

No changes to the rest are needed. You can change the years as you choose.

To insert the code, use [Alt]+[F11] to get into the VB Editor. Just cut and
paste the code from above into the module provided there. If you don't see a
big code sheet to paste it into, here's a page with very detailed
instructions on getting in to the VB Editor and bending it to your will so
that you can put code like this into it (creating a Module and making it
visible so you can paste into it)
http://www.jlathamsite.com/Teach/Excel_GP_Code.htm

You can always ask for HelpFrom @ jlathamsite.com (remove spaces) - but that
doesn't mean it's always available <g



"Vince" wrote:



I have date related data scribbled on paper. It is a record of readout
data taken from an elapsed time meter that is connected to my oil
burner. I want to enter these data into XL and then do some number
crunching. The DATEDIF function is certainly going to be used. The
elapsed time data was not collected daily, so there will also be a lot
of Row deletions necessary.

So, I must have in a column every date of each year going back to JAN
of 1992. I have built such a listing from yr 2004 to present using
the autofill feature. I am now looking for a template, perhaps, or a
more elegant method. I will probably want to put each year onto a
separate worksheet, and then use a summary sheet to do calculations.

Is there a template available that would put 1 Jan 1992 through 31 Dec
2003 into say column A ?

Is there a function that will accomplish this task more elegantly and
more easily than the drag and auto fill method to fill in each year's
column of dates?

TNX.

Vince