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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,917
Messages
5,545,024
Members
410,647
Latest member
bernardazar
Top