Calculating days

[Laguna11]

Could anybody help shed some light on the following please?

I need to calculate the number of days between 2 dates, however if the end date is a weekend or a public holiday I need it to count up to the previous working day. If the start date and end date straddle a weekend then it must count the weekend. I have a separate worksheet with all the public holidays listed.

Many thanks

 

[shg]

Counting all days or only weekdays?

 

[Laguna11]

Counting all days- except if the end date is a public holiday or weekend then it would count up to the previous working day.

 

[Hermanito]

I think you would need to use the NETWORKDAYS function (you need to activate the Analysis toolpak for this) in combination with some custom calculations, can't assist with the specifics though...

 

[barry houdini]

Try

=WORKDAY(B1+1,-1,holidays)-A1

 

[Sampat]

Hey!

We are using NETWORKDAYS in the similar scenario. The formula is =NETWORKDAYS(A2,today()-1),

Where A2 = The start date.

We are working with today() as we need date till today. You can use any cell reference here, or can put absolute reference if the date is fixed.

If you need to minus few other holidays apart from Sat-Sun, You need to put them in some column, say G4-G10. Formula will be- =NETWORKDAYS(A2, today(), $G$4:$G$10, -1)

Note: We need to activate the analysis tool pack (tools -> add in -> check 1st 2 -> OK) in Excel 2003.

Please let me know if this solution is helpful. If not, Please upload a sample file. (You can "doctor" your sensitive data, if any)

 

[Laguna11]

It almost works- using Networkdays eliminates any weekends in between which need to be counted. eg.

Start date: 01 June 2011 End date: 05 June 2011 Days counted: 3
Start date: 01 June 2011 End date: 12 June 2011 Days counted: 10

 

[Sampat]

It almost works- using Networkdays eliminates any weekends in between which need to be counted. eg.

Start date: 01 June 2011 End date: 05 June 2011 Days counted: 3
Start date: 01 June 2011 End date: 12 June 2011 Days counted: 10

Do you need to count weekend?

Can you please share an worksheet with me via mail? May be, that will be helpful for me to construct the correct formula!

 

[barry houdini]

Try

=WORKDAY(B1+1,-1,holidays)-A1

Did you try my suggestion? I assume that A1 is the start date and B1 the end date. In that formula WORKDAY is used to adjust the end date back to the previous workday.....and then you subtract the start date so that all weekends are counted as requested. Given your examples you need to add 1 to count all days, i.e.

=WORKDAY(B1+1,-1,holidays)-A1+1

where holidays is a named range including holiday dates - replace with actual range if required

 

[Laguna11]

Unfortunately it's not working Barry! It is counting 1 day as 0. I have replicated below some start and end dates and the answer I would expect in days. The dates are in dd/mm/yyyy format.



Year Start Date End Date Number of days taken
2009 07/01/2009 07/01/2009 1
2010 08/10/2010 10/10/2010 1
11/11/2010 12/11/2010 2
2011 20/04/2011 27/04/2011 8
27/05/2011 27/05/2011 1
03/06/2011 05/06/2011 1