Hi,
Please if somebody can help me with this. I have googled a lot but was unable to find a correct response to my query.
- I have a command button in excel in Sheet 1 of the excel workbook.
- When I click on it, the macro runs to give me the previous business date (dd/mm/yyyy - format) in Cell (7, 6)
I have used the below code to exclude weekends:
Sub tempDate ()
Dim tempDate As Date
tempDate= DateAdd(“d", -1, Date) ‘Today’s date – 1
While Weekday (tempDate) = 1 Or Weeday (tempDate) = 7
‘If tempDate is a Sunday or a Saturday, keep on substracting one day until we get a weekday
tempDate = DateAdd(“d”, -1, tempDate)
Wend
Sheets(“Sheet1”).Select
Cells (7, 6).Value = tempDate
End Sub
Need to create:
Adding to this code I want to also exclude the Melbourne Public holidays.
For eg. 19th April 2019 (Good Friday) and 22nd Monday (Easter Monday)being public holidays if I run the report on Tuesday my previous business date will be 18th April 2019. (So excluding weekend and holidays)
Truly appreciate your assistance.
Thanks and regards,
SC
Hi Sharmita,
You can use this
=WorksheetFunction.WorkDay(Now(), -1, Range("Holidays"))
where Holidays is a named range containing the dates of all holidays.
Regards
Phil
Hi Philip,
Thanks for your response.
I have tried the above, it is giving me an error
I have defined a range as "Holidays" in sheet 1 should I have to name the Sheet when I am writing the code?
Sorry being new to vba I am having difficulty in understanding how to include the Range in the code.
Will greatly appreciate if you can help me understand it.
Regards,
SC
Hi SC,
If your named range is Holidays then using the code I wrote should work. The sheet name doesn't matter.
Please attach your workbook so I can see your code.
Regards
Phil
Hi Phil,
Thanks for your help.
I have attached the macro file for your reference. The excel version is 2003 though I will be able to convert the same in the excel 2016.
When clicked on the update button it populates the previous date in Cell (7, 6) in Sheet 1 of the worksheet excluding the weekends. I have created a separate sheet with the holiday list.
Please can you help me exclude these holidays from the previous date.
Thanks,
SC
Hi SC,
You need at least Excel 2007 to use the WORKDAY function I'm afraid. Can you not get that?
Phil
Hi Phil,
I do have Excel 2016 at my office.
If you are able to create the Workday function please can you forward the entire code.
I can run the code in the office to check if it works fine.
Regards,
SC
Hi SC,
Just use the formula I typed out above. See attached for examples in the worksheet and in VBA.
regards
Phil
