Dates Excluding Sundays

I have a worksheet which calculates dates a product will be manufactured based on how many hours the job will run.

The first column has the starting date of the job which I input. The second column contains the date the job will finish based on a third column which contains the hours the job will run. The calculated ending date is then put into the starting date of the 2nd job. It continues to calculate down to the last job.

This calculation is based on a 24 hour day, 7 days a week. I would like to take out Sunday's in the calculation. Is this possible

So when is the job deemed to start? If you have today's date in a1 and 20 hours in C1 will the job end today, because if you start at midnight it'll still be today when the job finishes?

If so, and assuming that the answer should be a date only and not date and time try this formula

=A1+CEILING(C1/24-1,1)+INT(C1/144+WEEKDAY(A1,3)/6)

format as date

where A1 contains start date and C1 the number of hours.

Note: I assume A1 won't be a Sunday

Thanks for the response. I was using times but it is not necessary as long as the date changes over.

I tried your formula and it is giving me an error. I formatted all my cells as dates, put 9/24/2007 in column A, the formula in column B, and 20 in column c.

An error comes up with "there is a problem with a number used in the formula.

START END
9/24/2007 #NUM! 20
#NUM! #NUM! 4
#NUM! #VALUE! 3
#VALUE! #NUM! 5
#NUM! #NUM! 2
#NUM! #NUM!
#NUM! #VALUE!

Am I doing something wrong

Sorry there was a problem with that formula - it gives an error if the hours are less than 24.

It's actually easier with times though, so to show a finish time and date where A1 has a date or date and time

=A1+C1/24+INT(C1/144+(WEEKDAY(A1,3)+MOD(A1,1))/6)

format as dd/mmm/yy hh:mm or similar

Thanks, It's fantastic.

Would it be possible for you to explain the formula to me. I understand some of it but not all.

Also, what would be the syntax if it was weekdays only.

Thanks again

I need a calculation as stated above, but this time I need only weekdays. How would I modify this formula?

I have a sheet that calculates a date based on the amount of hours that a task will take:
EX

column 1 column 2 column 3
12/4/05 12/5/05 24

The first column is the start date, the second is the end dates based on column 3 of 24 hours. how do I calculate column 2 based on a 5 day work week.

Thanks

=WORKDAY(A1, C1/24)

Requires the Analysis ToolPak add-in. See Help for how non-integer values are handled.

Sorry, I did not mention that it was calculating hours not days.

213 Total Hours 24 Hrs/day
Hrs START END
25 12/4/07 8:00 AM 12/5/07 9:00 AM
6 12/5/07 9:00 AM 12/8/07 3:00 AM

This is a representation of the problem,
Hours are how many hours a job will run
Start date
End date is the calculation.

I need to take the start date, add 25 hour (how many hours the job will run) divide it by the number of hours per day (24 in this case) and come up with an end date that excludes weekends.

I hope this explains it better.

For weekdays only try

=A1+C1/24+INT((C1/24+WEEKDAY(A1,3)+MOD(A1,1)+2)/7)*2

or, in this case you could use WORKDAY function from Analysis ToolPak add-in and use

=WORKDAY(A1,MOD(A1,1)+C1/24)+MOD(A1+C1/24,1)

see similar thread here

the weekday formula seems to work for 24 hours, however some of our schedules need to be changed when a machine only works one 8 hour shift or 2 eight hour shifts (16 hours).

I tried changing the 24 to refer to the cell that contains that information but it did not work.

Sorry for starting another thread but i figured everyone was so confused by all the changes i made that it would be better to start over.