+ Reply to Thread
Results 1 to 15 of 15

Dates Excluding Sundays

  1. #1
    Registered User
    Join Date
    04-12-2006
    Posts
    30

    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

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    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

  3. #3
    Registered User
    Join Date
    04-12-2006
    Posts
    30
    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

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    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

  5. #5
    Registered User
    Join Date
    04-12-2006
    Posts
    30
    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

  6. #6
    Registered User
    Join Date
    04-12-2006
    Posts
    30

    Weekdays only

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

  7. #7
    Registered User
    Join Date
    04-12-2006
    Posts
    30

    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

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    =WORKDAY(A1, C1/24)

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

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Try:

    =WORKDAY(A1,C1/24)

    This is an Analysis toolpak function: Tools|Addins >> Analysis Toolpak
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  10. #10
    Registered User
    Join Date
    04-12-2006
    Posts
    30

    Workday

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

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Quote 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?

  12. #12
    Registered User
    Join Date
    04-12-2006
    Posts
    30
    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.

  13. #13
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    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)

  14. #14
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    see similar thread here

  15. #15
    Registered User
    Join Date
    04-12-2006
    Posts
    30

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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