# Dates Excluding Sundays

1. ## 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  Register To Reply

2. 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  Register To Reply

3. 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  Register To Reply

4. 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  Register To Reply

5. 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  Register To Reply

6. ## Weekdays only

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

7. ## Calculating Weekdays

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  Register To Reply

8. =WORKDAY(A1, C1/24)

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

9. Try:

=WORKDAY(A1,C1/24)

This is an Analysis toolpak function: Tools|Addins >> Analysis Toolpak  Register To Reply

10. ## Workday

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

11. Originally Posted by gailb14
Sorry, I did not mention that it was calculating hours not days.
Huh??

did you try the formula? The formula takes the start date and add the number of hours in your 3rd column to get the date in the 2nd column. What isn't right?  Register To Reply

12. 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.  Register To Reply

13. 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)  Register To Reply

14. see similar thread here  Register To Reply

15. ## workday

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.  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1