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

 

[Laguna11]

How can I attach a spreadsheet? or an image of it?

 

[barry houdini]

Did you use the second formula I suggested......with +1 at the end?

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

Using that version I got the same results as you suggested......

 

[Laguna11]

It works!!!! Thank you, Thank you, Thank you!!!! You wouldn't believe that I awoke in the middle of the night dreaming of formulas would you!?!

Thank you all ever so much for your help!

 

[Laguna11]

Can I ask for some further help please?

I am now looking at a start date. If a start date is a weekend or a public holiday I need the next working date, but if the start date is a Monday to Friday (and is not a public holiday) then it counts that date as the start date.

I have been able to get the next working day after a weekend and public holiday but I can't get it to count a normal Monday to Friday as day 1.

=WORKDAY(E17,1,'Public Holidays'!A3:A14)

 

[barry houdini]

You can use the same technique that I employed in the other formula, subtract 1 from the date and then add 1 workday - that will give you an unchanged date for workdays....or the next working day for non-workdays, i.e.

=WORKDAY(E17-1,1,'Public Holidays'!A3:A14)

 

[Laguna11]

Once again- thank you! It's perfect!

I had been playing around with your formula, but since I did not understand how the +1,-1 bit worked it was very hit and miss!!!

 

[Laguna11]

If both the start and end date are on a weekend/public holiday with no working days in between could it return a 0 value?

eg. 28/05/2011 - 30/05/2011 (30/05/2011 being a public holiday)

 

[barry houdini]

So currently you are using something like this

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

..and you get a negative result?

You can use MAX to make the lowest value zero, i.e.

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

 

[Laguna11]

You're my knight in shining armour! Thanks!

I'm not using the formula as you suggest above as I also need to look at the start and end dates as they could contain text after the date such as AM or PM, if only a half day of sick leave has been taken, and I need to reflect this.

I think I have error trapped all my formulas now and I have tested it with all possible scenarios. This is going to make a huge difference to me time wise at work- and with your (and the others) help I have learnt a bit more in Excel!