General discussion

Locked

Automatic Dates in Excel

By keepbelieving ·
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...

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by DKlippert In reply to Automatic Dates in Excel

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

Collapse -

by keepbelieving In reply to

Thank you for the macro...I will use this for now and work on the portion with the headings.

Collapse -

by keepbelieving In reply to Automatic Dates in Excel

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.

Collapse -

by keepbelieving In reply to Automatic Dates in Excel

Point value changed by question poster.

Collapse -

by DKlippert In reply to Automatic Dates in Excel

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.

Collapse -

by keepbelieving In reply to

Poster rated this answer.

Collapse -

by chitosunday In reply to Automatic Dates in Excel

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

Collapse -

by keepbelieving In reply to

Thanks so much for the input you gave.

Collapse -

by keepbelieving In reply to Automatic Dates in Excel

This question was closed by the author

Back to Software Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums