Inserting Date in worksteets

[Vic Abrahamian]

I have a work book which has 30 worksheets. Excel 2000
Is there a way with which I could add a date to a
specific cell and then increment the first day by one day
I want cell E8 to in each work sheet to show 1/1/04,
1/2/04, 1/3/04, 1/4/04, 1/5/04 etc..
Thank you

 

[Norman Harker]

Hi Vic!

Put the base date in E8
E9:
=E8+1
Format as a date to taste.

Excel stores dates as the number of days since 31-Dec-1900 so adding 1
to any date will return the next day.

For more on dates see:

Chip Pearson:
http://www.cpearson.com/excel/datetime.htm#AddingDates
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Andrea Jones]

The formula you will need to use in Sheet2 is =Sheet1!
E8+1, format the result as a date and you should get the
answer you want.

Andrea Jones
Microsoft Office Specialist Office 2000 Master Instructor

 

[Guest]

This doesen't cover the 30 worksheets in the workbook
I wanted date+next day on 30 worksheets in a workbook
1st work sheet 1/1/04 on cell E8
2nd work sheet 1/1/04 + 1 = 1/2/04 on Cell E8
3rd work sheet 1/2/04 + 1 = 1/3/04 on Cell E8

 

[Vic Abrahamian]

Here is the closest to what I got to but it just places
the same date accross worksheets in a workbook.
I typed the date in E8 then right clicked on the "Sheet1"
tab at the bottom of the page and selected "Select All
Sheets" then clicked on the "EDIT" menu, "FILL" and
selected "Accross Worksheets".
It places the date in E8 on the same cell accross all the
worksheets. I was hoping that there was a similar way to
do this quickly like above for different dates accross
worksheets.

 

[Dave Peterson]

You could use a macro:

Option Explicit
Sub testme()
Dim myDate As Date
Dim iCtr As Long

myDate = DateSerial(2004, 1, 1)

For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("e8")
.Value = myDate - 1 + iCtr
.NumberFormat = "mm/dd/yyyy"
End With
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[David McRitchie]

I would think you would want to add sheets to the workbook
and name them rather than changing the name of existing worksheets.
But then you answered in the manner that the poster asked.
I guess all of the sheets would have to have the same information
in them so it doesn't matter what gets named to a particular date.

 

[Gord Dibben]

David

Dave's code re-names nothing. It just increments the date in E8 across all
worksheets.

Gord

I would think you would want to add sheets to the workbook
and name them rather than changing the name of existing worksheets.
But then you answered in the manner that the poster asked.
I guess all of the sheets would have to have the same information
in them so it doesn't matter what gets named to a particular date.

 

[Dave Peterson]

A very good point. If the OP uses the macro approach, remember to exit without
saving if it didn't do what you wanted.

I would think you would want to add sheets to the workbook
and name them rather than changing the name of existing worksheets.
But then you answered in the manner that the poster asked.
I guess all of the sheets would have to have the same information
in them so it doesn't matter what gets named to a particular date.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

You could use a macro:

Option Explicit
Sub testme()
Dim myDate As Date
Dim iCtr As Long

myDate = DateSerial(2004, 1, 1)

For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("e8")
.Value = myDate - 1 + iCtr
.NumberFormat = "mm/dd/yyyy"
End With
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[David McRitchie]

You're correct Gord, I didn't read it very well, but the comment seems
just as valid because =it is done without regard to any preexisting content
of the worksheets.

David

Dave's code re-names nothing. It just increments the date in E8 across all
worksheets.

Gord

I would think you would want to add sheets to the workbook
and name them rather than changing the name of existing worksheets.
But then you answered in the manner that the poster asked.
I guess all of the sheets would have to have the same information
in them so it doesn't matter what gets named to a particular date.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

You could use a macro:

Option Explicit
Sub testme()
Dim myDate As Date
Dim iCtr As Long

myDate = DateSerial(2004, 1, 1)

For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("e8")
.Value = myDate - 1 + iCtr
.NumberFormat = "mm/dd/yyyy"
End With
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Vic Abrahamian wrote:

I have a work book which has 30 worksheets. Excel 2000
Is there a way with which I could add a date to a
specific cell and then increment the first day by one day
I want cell E8 to in each work sheet to show 1/1/04,
1/2/04, 1/3/04, 1/4/04, 1/5/04 etc..
Thank you

 

[Dave Peterson]

I read David's reply and assumed that his point (hidden by the renaming stuff)
was that if the sheets weren't in the correct order, then the OP may not get the
intended result.

But I've always read posts the way I want to--not the way they're written!

David

Dave's code re-names nothing. It just increments the date in E8 across all
worksheets.

Gord

I would think you would want to add sheets to the workbook
and name them rather than changing the name of existing worksheets.
But then you answered in the manner that the poster asked.
I guess all of the sheets would have to have the same information
in them so it doesn't matter what gets named to a particular date.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

You could use a macro:

Option Explicit
Sub testme()
Dim myDate As Date
Dim iCtr As Long

myDate = DateSerial(2004, 1, 1)

For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("e8")
.Value = myDate - 1 + iCtr
.NumberFormat = "mm/dd/yyyy"
End With
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Vic Abrahamian wrote:

I have a work book which has 30 worksheets. Excel 2000
Is there a way with which I could add a date to a
specific cell and then increment the first day by one day
I want cell E8 to in each work sheet to show 1/1/04,
1/2/04, 1/3/04, 1/4/04, 1/5/04 etc..
Thank you

 

[Gord Dibben]

Caveat Emptor<g>

OP wanted an incremented date in E8. Dave provided that. OP must/should know
that E8 is blank on each sheet before running the code.

Gord

You're correct Gord, I didn't read it very well, but the comment seems
just as valid because =it is done without regard to any preexisting content
of the worksheets.

David

Dave's code re-names nothing. It just increments the date in E8 across all
worksheets.

Gord

I would think you would want to add sheets to the workbook
and name them rather than changing the name of existing worksheets.
But then you answered in the manner that the poster asked.
I guess all of the sheets would have to have the same information
in them so it doesn't matter what gets named to a particular date.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

You could use a macro:

Option Explicit
Sub testme()
Dim myDate As Date
Dim iCtr As Long

myDate = DateSerial(2004, 1, 1)

For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("e8")
.Value = myDate - 1 + iCtr
.NumberFormat = "mm/dd/yyyy"
End With
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Vic Abrahamian wrote:

I have a work book which has 30 worksheets. Excel 2000
Is there a way with which I could add a date to a
specific cell and then increment the first day by one day
I want cell E8 to in each work sheet to show 1/1/04,
1/2/04, 1/3/04, 1/4/04, 1/5/04 etc..
Thank you