Working days

[wjb1976]

I am programming a new project which has 800 separate addresses. I have separated these onto 2 separate tabs one for each area and would like to programme 2 jobs a day for each area and I have been using this formula =IF(H2=H1,IF(WEEKDAY(H2,2)=5,H2+3,H2+1),H2) with the start date being in H2. This formula works great but doesn't take into account the public holidays, is there anything I can add to this formula for this or is there a different one to use? Also can I input on a separate tab when the operatives are on leave so no jobs are booked I for these dates?

Thanks

Will

[TMS]

See: http://office.microsoft.com/en-gb/ex...010342719.aspx


Regards, TMS

[gak67]

You can't loop within a function to add 1 until you get the correct number of working days though, so you probably need to do it via VBA.

[Saarang84]

Post a workbook with sample data to check whether we can formulate a solution

[wjb1976]

I have put a sample as there is 800 properties in total.

[Saarang84]

Is the first date always input manually or usually the start date is at the start of a particular month or so?

In general, you can list down the list of holiday dates in a separate column or another sheet and use the WORKDAY function to check whether the next date (out of your formula) is a holiday or not. If its not a holiday, then it can used, else ignored. The argument of the WORKDAY function would be : The start date, the number of days (always 1, i.e. next working day in your case) and the holiday list (referring to the list of holidays). You can refer Excel help for the syntax and example of the WORKDAY function.

[wjb1976]

The start date will be manually inputted.

[Saarang84]

Refer column I (titled New_Date) in the attached workbook. But, I've changed the dates which you had given initially. Is this what you need?

[wjb1976]

This works brilliant thanks but I am struggling to input this into the spreadsheet I need this for. I have tried copying and pasting and also manually copying the formula into the spreadsheet.

[Saarang84]

This works brilliant thanks but I am struggling to input this into the spreadsheet I need this for. I have tried copying and pasting and also manually copying the formula into the spreadsheet.

You can post (or PM me) the a replica of your workbook (with insensitive data, of course) in case if you have posted just a model workbook earlier. I'll recode the formulas as per your need and send it to you.

[gak67]

Just this will give you the same results as your own formula, but you'll need a list of the holiday/leave dates you want to skip.

Please Login or Register  to view this content.

[Saarang84]

Just this will give you the same results as your own formula, but you'll need a list of the holiday/leave dates you want to skip.

Please Login or Register  to view this content.

Hi Gak,

Your formula doesn't work as intended. Refer the screenshot below (highlighted in Red) comparing your formula's output with mine.
Working_Days.png

[gak67]

My formula works as the OP's original formula, but takes into account public holidays if the date in column H and up one row is the same as the date in column H and up 2 rows.

[wjb1976]

Would it be possible to allow for an operative to be made unavailable for half a day and to allow for 1 job job to be booked in instead of 2 this is to allow for any training this wont be for everyday only when necessary.

[wjb1976]

Hi

How can i adjust this to change the amount of appointments per day? This currently is allowing for 2 appointments per day.

[Saarang84]

Hi

How can i adjust this to change the amount of appointments per day? This currently is allowing for 2 appointments per day.

Hi,

Can you post a sample workbook?