Active Member
September 8, 2019
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
October 5, 2010
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
Active Member
September 8, 2019
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
Active Member
September 8, 2019
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
1 Guest(s)