Formula for working days (exclude public holidays) | Excel forum

Resolved · Medium Priority · Version 365

Fri 24 Nov 2023, 13:08 Delegate Kay said...

Kay has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course

Formula for working days (exclude public holidays)

Hi Team,

I am using a formula (=WORKDAY(F10, 20)) to count 20 working days from 27 Nov 2023, however this is returning 25 Dec 2023.

Please can you advice how I can update this formula to exclude all the public holidays in Dec 2023?

Many Thanks

Fri 24 Nov 2023, 13:32 Trainer Jens said...

RE: Formula for working days (exclude public holidays)

Hi Kay,

Thank you for the forum question.

All date functions in Excel calculating working days (Workday, Networkdays, & Networkdays.intl) do not know which days are public holidays. You will unfortunately have to create a holiday list.

If you in a Excel worksheet type the dates for public holidays and let us just say in A1 to A10, then your formula should look like

=WORKDAY(F10, 20, A1:A10)

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 1 Dec 2023: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Adding up time greater than 24 hours

When you add up time if it exceeds 24 hours i.e 27 hours appears as 03:00. Go to Format / Cells / Number / Custom. The format is hh:mm but if change it to [hh]:mm it will add up to the correct amount of hours.

View all Excel hints and tips