Excel Userform calculating dates

Question

Hi

Does anyone have a userform with VBA coding that allows me to do the following. It's dates relates.

1. To enter a start date
2. To enter an end date
3. Allows me to specify holiday dates.
4. Excludes weekends
5. Calculates the number of days between the start date/end date/exludes the holidays I have selected.
6. All the above in one userform with VBA coding

Answer 1

I would like to use these kind of sheets for data entry and report rather than userform.

raw data

full year days(here only display 7 rows total 365 rows)

i	current	wkd	holiday
1	2023-01-01	0	holiday
2	2023-01-02	1
3	2023-01-03	2
4	2023-01-04	3
5	2023-01-05	4
6	2023-01-06	5
7	2023-01-07	6	holiday

specify holiday dates

current
2023-01-02
2023-01-03

enter a start date an end date

start	end
2023-01-02	2023-02-02

all holidays generated

current	holiday
2023-01-01	holiday
2023-01-02	holiday
2023-01-03	holiday
2023-01-04
2023-01-05

expected result generated

number of days between the start date/end date/exludes the holidays

start	end	numberOfWorkDay	WorkDays
2023-01-02	2023-02-02	22	2023-01-04,2023-01-05,2023-01-06,2023-01-09,2023-01-10,2023-01-11,2023-01-12,2023-01-13,2023-01-16,2023-01-17,2023-01-18,2023-01-19,2023-01-20,2023-01-23,2023-01-24,2023-01-25,2023-01-26,2023-01-27,2023-01-30,2023-01-31,2023-02-01,2023-02-02

Answer 2

See https://www.dropbox.com/s/9v8bjbc3e619aa6/DateCalcUserForm.xlsm?dl=1

The code uses weekend code 1 to specify Saturday and Sunday as weekend days. See NETWORKDAYS.INTL function for the values you can use if you want different weekend days.