Dates / excluding weekends etc...

yoko

Active Member
Joined
Sep 5, 2006
Messages
349
Hi,

You know when you enter a couple of dates and then copy the cells down it increments the date e.g.

01/01/2006
02/01/2006
03/01/2006...

How can you do this and leave out weekend’s public holidays etc? Is there a formula or function that allows you to do this?

Cheers
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764

ADVERTISEMENT

NB - Doesnt take into account Bank Holidays

Yes it does.

From Excel Help:
NETWORKDAYS(start_date,end_date,holidays)

Start_date is a date that represents the start date. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

End_date is a date that represents the end date.

Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
NB - Doesnt take into account Bank Holidays

Yes it does.

From Excel Help:
NETWORKDAYS(start_date,end_date,holidays)

Start_date is a date that represents the start date. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

End_date is a date that represents the end date.

Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.

I was gonna add "unless you stipulate them"

Thanks Neil
 

yoko

Active Member
Joined
Sep 5, 2006
Messages
349

ADVERTISEMENT

Ah I just realised, although that is helpful it’s not what I'm looking for.

What I need is it to print the dates but leave out the weekends / public holidays.

A / B / C

1 / 22/09/2006 / Friday
2 / 25/09/2006 / Monday
3 / 26/09/2006 / Tuesday
4 / 27/09/2006 / Wednesday etc...

Is that any clearer?

Thanks.
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
No doubt a shorter method, but (I think) the below formula does what you need...

Code:
=IF(TEXT(A1,"DDDD")<>"Friday",A1+1+IF(ISERROR((VLOOKUP(A1+1,Holidays,1,FALSE))),0,1),A1+1+IF(ISERROR((VLOOKUP(A1+3,Holidays,1,FALSE))),0,1))

note: this assumes you have a named range called "Holidays", which houses a list of bank holidays,
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If your start date is in A1 try this formula in A2 copied down

=WORKDAY(A1,1,holidays)

Note: WORKDAY function is part of Analysis ToolPak add-in. If you don't have that installed then....

Tools > addins > tick "Analysis ToolPak" box
 

Forum statistics

Threads
1,141,931
Messages
5,709,403
Members
421,635
Latest member
mehdi hannechi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top