Same date on the heading and sheet name automatically except Sunday for full month

DIPASGL

New Member
Joined
Oct 26, 2022
Messages
39
Office Version
  1. 2003 or older
Platform
  1. Windows
Hi

Please see the attached file where I am having date Monday to Saturday Except Sunday for 1 month and same pattern for each sheet name. Problem is that I am manually adding date on top and date at bottom for sheet name .

Query - Could please tell me how I can just write 1 time on sheet 1 for Ist of every month starting Monday and all other sheets get next date itself except Sunday and same for Sheet name? Hope you understand my query. Thanks
 

Attachments

  • 1 Capture.PNG
    1 Capture.PNG
    50.3 KB · Views: 5
  • Capture.PNG
    Capture.PNG
    48.1 KB · Views: 5

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
N.B.
WORKDAY.INTL function
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel Web App Excel 2010 Excel for Mac 2011 Excel Starter 2010

Cell Formulas
RangeFormula
A2:A7A2=A1+IF(WEEKDAY(A1,2)=6,2,1)
B2:B7B2=WORKDAY.INTL(B1,1,11)
 
Upvote 0
Assume first sheet, with cell L1 = "01/01/2022" (or any 1st date of any month any year)
Stay in this sheet then run this code, to create sheets from 02/01/2022 to 31/01/2022 (30 sheets more):
VBA Code:
Option Explicit
Sub adddate()
Dim startD As Date, d As Date, wsname As String
Dim i&, numD&
startD = Range("L1").Value2
With WorksheetFunction
    numD = .NetworkDays_Intl(startD, .EoMonth(startD, 0), 11) - 1 'count of workdays of month
    For i = 1 To numD
        d = .WorkDay_Intl(startD, i, 11) ' each workday of month
        wsname = Format(d, "d.m.yy")
        If Not Evaluate("=ISREF(" & wsname & "!A1)") Then Sheets.Add after:=Sheets(Sheets.Count)
        With ActiveSheet
            .Name = Format(d, "d.m.yy")
            .Range("L1").Value = Format(d, "dddd, dd mmmm yyyy")
        End With
    Next
End With
End Sub
 
Upvote 0
or if you just want the first monday
T202210a.xlsm
DE
1Sat Oct 01, 2022Mon Oct 03, 2022
2
5a
Cell Formulas
RangeFormula
E1E1=EOMONTH(D1,-1)+8-WEEKDAY(EOMONTH(D1,-1)+1,12)
 
Upvote 0
Assume first sheet, with cell L1 = "01/01/2022" (or any 1st date of any month any year)
Stay in this sheet then run this code, to create sheets from 02/01/2022 to 31/01/2022 (30 sheets more):
VBA Code:
Option Explicit
Sub adddate()
Dim startD As Date, d As Date, wsname As String
Dim i&, numD&
startD = Range("L1").Value2
With WorksheetFunction
    numD = .NetworkDays_Intl(startD, .EoMonth(startD, 0), 11) - 1 'count of workdays of month
    For i = 1 To numD
        d = .WorkDay_Intl(startD, i, 11) ' each workday of month
        wsname = Format(d, "d.m.yy")
        If Not Evaluate("=ISREF(" & wsname & "!A1)") Then Sheets.Add after:=Sheets(Sheets.Count)
        With ActiveSheet
            .Name = Format(d, "d.m.yy")
            .Range("L1").Value = Format(d, "dddd, dd mmmm yyyy")
        End With
    Next
End With
End Sub
Thank you for your reply. Sorry I just didn't get where to enter VBA Code? Thanks
 
Upvote 0
N.B.
WORKDAY.INTL function
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel Web App Excel 2010 Excel for Mac 2011 Excel Starter 2010

Cell Formulas
RangeFormula
A2:A7A2=A1+IF(WEEKDAY(A1,2)=6,2,1)
B2:B7B2=WORKDAY.INTL(B1,1,11)



Thanks Dave for your reply.

Could you please see my attached sheet and see I have applied the formula but could you please tell me how can I can I apply this formula to work on each sheet date heading and each sheet name on 1 worksheet? Hope I am clear with my query.
 

Attachments

  • Capture.PNG
    Capture.PNG
    41.1 KB · Views: 6
Upvote 0
Thank you for your reply. Sorry I just didn't get where to enter VBA Code? Thanks
Assume you have 1st sheet with cell L1 = "01/01/2022"
Stay in this sheet, follow below steps:
1) Alt-F11 to open VBA editting window
2) Insert/module, then paste the code into
3) Hit F5 (or play button) to run code (or you can insert a button, then assign this code into)
Hope it helps.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top