Software

Ask something
Discussion Locked

Automatic Dates in Excel

0 helpful
9 replies

by keepbelieving

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Is it possible to create a spreadsheet with each day of the month marked i.e. Aug 1, 2, 3 etc. I thought that a formula would allow this so that I would not have to create and save a worksheet for each day of the month.

Sincerely,
Hoping...

9 replies

by dklippert

This is crude, but insert as many sheets as will be needed then run this macro:

Sub DayTabs()
Dim i As Long

MonthIs = Application.InputBox("Enter a month ( 1-12)") & "/"
YearIs = "/" & Application.InputBox("Enter a Year")
DaysIs = Application.InputBox("Enter a days in month")

For i = 1 To DaysIs
Worksheets(i).Name = Format(DateValue(MonthIs & i & YearIs), "dddd dd mmmm ")
Next i

End Sub

by keepbelieving

Ok, my original question was not descriptive enough. This spreadsheet or database whichever has the capabilities to do this is going to be supplied to(17+ departments). I need one form for every day in every OR. Currently, I manually change the site (i.e. GOR 1, GOR 2 etc) and add 11/ /03, but manually have to put in the day. Is there a formula or macro in Excel so that I could have GOR 1 run 30, (11/1/03 through 11/30/03) and then change to GOR 2 and have that run (11/1/03 through 11/30/03)? Or would what I am trying to do work better in Access? By the way Dkippert, your first response was a revelation for me, I tried it and it worked as far as inserting the dates automatically on the sheet tabs that was wonderful. Then the second part was having the department names integrated into that synopsis someway somehow. Thanks for any input.

by dklippert

Are you trying to maintain a list or set up seperate worksheets?
You might be better off using Access. While Excel can contain any number of Worksheets, it gets clumsy.
To just produce a list/table/database would be easier. I would have to more clearly understand what you are trying to do.

by chitosunday

This code will create each GRO 1 & 2 with dates for the following month unless you are updating within 1-7th day Sub crtdate()
Dim xdy, xmn, xyr, ctr, ctrb, h, i As Integer
Dim xdate As Date
If Day(Date) <= 7 Then
xdate = DateSerial(Year(Date), Month(Date) + 1, 0)
Else
xdate = DateSerial(Year(Date), Month(Date) + 2, 0)
End If
xdy = Day(xdate)
xmn = Month(xdate)
xyr = Year(xdate)
ctrb = Sheets.Count
Do Until ctr >= ctrb - 1
ctr = ctr + 1
Application.DisplayAlerts = False
ActiveSheet.Delete
Loop
Application.DisplayAlerts = True
For h = 2 To 1 Step -1
For i = xdy To 1 Step -1
If ctr >= ctrb Then Sheets.Add
ActiveSheet.Name = "GOR" & h & " " & xmn & "-" & i & "-" & xyr
ctr = ctr + 1
Next i
Next h
End Sub

This discussion is closed to new replies.