VBA code to save the excel file to save 1st working day to last Friday.

[Niranjanrajrishi]

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\OV_KPI" & "_" & Format(Date - Day(Date), "mmm-yy"), FileFormat:= _
        51, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
            , CreateBackup:=False

I wanna save my formatted excel file with the 1st Working day to last Friday in the format "05-30 - March'18" (example of March month. The attached code helps me to save for the last week (Monday to Friday) only.

 

[Chihiro]

What do you define as first working day?
Do you need to consider stat holiday as well?
Is it just the file name you are concerned with, or some other filter criteria etc.

Typically speaking, it's much easier to store this calculation in some out of way cell (or sheet) and validate calculation. And have code read value from it.

 

[Niranjanrajrishi]

I wanted to start the 1st working day without considering weekend( Saturday and Sunday). Holidays need not to consider here. For example, If the 1st working day is Monday and it is Holiday, still we need to consider Monday as the first working day.

 

[Chihiro]

Here's sample code for calculating first Monday and last Friday in a month.

Sub Demo()
Dim firstMon As Date
Dim lastFri As Date
Dim mth As Integer
mth = 3
firstMon = DateSerial(2018, mth, 1) + (8 - Weekday(DateSerial(2018, mth, 1), 2)) Mod 7
lastFri = DateSerial(2018, mth + 1, 0) + (-Weekday(DateSerial(2018, mth + 1, 0), 2) - 2) Mod -7
Debug.Print Format(Day(firstMon), "00") & "-" & Format(Day(lastFri), "00")
End Sub