Project Schedule, 12 hour day, no work on sundays

Hello,
I have read a number of posts that sort of address my question, but in general everyone posting solutions is a lot smarter about Ecel than I am. Here is my 'challenge?'. My company is to cheap to buy Microsoft Project, so as an Architect I am creating a Project Schedule in Excel. I use a start date and time of 11/20/12 6:00:00pm and assign a task that takes 3 hours to finish. I can add these together, but he lies the rub: our workday starts at 7am and ends at 7pm and we dont work on sunday. So i need my 'end date' to check that the assigned task does not go past 7pm and if it does the end date and time is for the next day, but if that next day is Sunday, go to Monday. Of course this is exactly what Project does, but oh well. My version of Excel is 2007. If someone writes a VBa solution thats great, but i dont have the slightest idea what or how to put that into a workbook. I think i have attached my workbook...
thank you for your help in advance

The solution I worked out for you would need a helper column rather than VBA. I inserted a column G in your worksheet "Project". It may be formatted as Date so that you can see what it does, but you may like to hide the column once that has been achieved. This column shows the intended end date. Note that the calculated date will be checked against your holiday list and the date you see in column G is already adjusted for holidays, but if the day happens to be a Sunday it will be changed again in column H. Here is the formula for cell G11,

Formula:

Please Login or Register  to view this content.

Place the next formula in cell H11,

Formula:

Please Login or Register  to view this content.

Both formulas a array formulas. That means that you must paste them in the formula bar and then confirm the entry with SHIFT+CONTROL+ENTER (in place of the singular Enter that is sufficient for normal formulas). You can copy array formulas to other cells as usual. When entered correctly the formula will be displayed between curly braces in the formula bar else they won't throw an error but quietly produce the wrong result.
Testing has been done perfunctorily. I suggest that you try out a few things before you trust this monster completely.

I suppose I owe an apology for the functions I posted yesterday. It was such a marvelous idea and it does seem to work. However, that is all it does - seem. I have failed to create an array formula that will look up incremental dates in a set range and tell me which, if any, of the dates was first found.
Meanwhile, for some one who professes to be less smart in things Excel than those who frequent these pages joeinck does appear to have had the better sense. So I took his advice and stuck with VBA.
You need to install this little procedure in the code module of the worksheet on which you want the action,

Please Login or Register  to view this content.

This procedure will check whenever a change is made in the worksheet whether it is relevant to the calculation of time. It will decide that it is relevant if the change occurred in one of the Start date or Hours columns AND the row of change has a number with a decimal in the Test column, 3 columns to the left from the Start Date column. Remember to save your workbook as macro-enabled now.

Next please insert a standard code module ("Module1" by default) and past this code into it.

Please Login or Register  to view this content.

At the top of the code you have a lot of Enums and Constants which are all set to match the worksheet Project that you posted. You can change those values in future if you modify your design. Note in particular that the 3 cells that make up a set, Start, Hours and End, are treated intentionally as independent units, meaning you don't need to keep them adjacent to each other.
I hope this will compensate for my errors of last night.

Hi,
I wanted to thank you for your VBa module and associated efforts, greatly appreciated. Come to find out, my company will not allow Excel to run macros (3rd party or new). Do not really understand what is going on but i can not save my file as macro enabled...so as a result i can not solve the problem with VBa.

thanks again
j