Dates / excluding weekends etc...

[yoko]

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

 

[njimack]

Take a look at NETWORKDAYS (requires the analysis toolpak)

 

[yoko]

Ah nice one cheers

 

[MarkAndrews]

NB - Doesnt take into account Bank Holidays

 

[njimack]

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]

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]

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]

No doubt a shorter method, but (I think) the below formula does what you need...

=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]

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