Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Work out date from hours (Excel 2000 >)

    Hi,

    I'm thinking in circles...help!

    Say I have a start date in a column, and then a couple of columns with work hours...adding up to say, 64 hours. I need a formula that will display an "End date" that corrosponds to the elapsed hours.
    EG: 24/8/2005, 24, 24, will equal 26/8/2005. (simplified)

    Any ideas of a function/formula to accurately display the appropriate date!
    Tx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Work out date from hours (Excel 2000 >)

    Dates are stored with 1 day as unit, so you must convert everything to days.
    Say the start date is in A2, and B2 through D2 contain hours. A formula for the end date could be

    =A2+SUM(B22)/24

    The result will still contain a time component, you can round to the nearest midnight by using

    =ROUND(A2+SUM(B22)/24,0)

    or round up to the next midnight by

    =CEILING(A2+SUM(B22)/24,1)

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Work out date from hours (Excel 2000 >)

    Fantastic Hans...this should do just grand!!
    Cheers
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Work out date from hours (Excel 2000 >)

    Thanks for the formula Hans. I got some feedback from the client who says: The problem that remains is this: What happens if a project begins on a Friday and continues for a few days. How will the formula be able to deal with weekends. If I start a 3-day project on Friday, the formula should return date of completion as the following Tuesday and not Sunday.

    Tx again for the help Hans. Hope this makes sense!
    Cheers
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Work out date from hours (Excel 2000 >)

    If you activate the Analysis ToolPak add-in that comes with Excel (in Tools | Add-Ins...), you can use two functions that deal with work day / weekend day arithmetic. The description of WORKDAY in the online help says
    <hr>Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.<hr>
    and that of NETWORKDAYS
    <hr>Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.<hr>
    Look up these functions in the Excel help for details and examples.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Work out date from hours (Excel 2000 >)

    Hans, tx for the feedback.
    Call me silly...but I just can't wrap myself around this!!! Please help as per the attachment!
    Cheers
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Work out date from hours (Excel 2000 >)

    I don't understand the calculation. Can you explain in detail how it fits together? For example, are Hours1, Hours2 and Hours3 contiguous?

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Work out date from hours (Excel 2000 >)

    The project starts on 26/08 at 12H00pm. The Hours1 represents 8 hours worked on the project after 12H00pm...say from 1pm to 9pm on 26/08. Then hours2 is a further 13 hours say on Saterday 27/08 and hours3 is 7 hours on Sunday 28/08. As all these hours are over a weekend and they total to 28 the project end date will be Monday and not Sunday. 28 hours is represented by 2 days, but weekends must not be taken into account...so the end date is Monday!
    Hope its a little clearer and I'm making sense?
    Regards,
    Rudi

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Work out date from hours (Excel 2000 >)

    Lik Hans, I don't understand your calculation, but does this formula do what you want:


    <code>
    =WORKDAY(A3,B3/24+C3/24+D3/24)
    </code>
    Legare Coleman

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Work out date from hours (Excel 2000 >)

    Does 8 hours represent one work day, or one third of a work day? What does 13 hours represent? I think that you have some real problems with this approach.
    Legare Coleman

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Work out date from hours (Excel 2000 >)

    Yes Legare,
    This is going what I need. Tx a million!!! All those calcs below the hours you must ignore. That was my futile approach to trying to solve the answer. Based on your approach:
    Say I have: 26/08 the 24hrs then 24hrs and another 24hrs. Your calc works out 31/08 which is correct. In other words starting on Fri 26/08 and adding 72 hours (3 days) it returns Wed 31/08 and not Mon 29/08 as it must ignore weekend days!

    Thanx...your solution looks great!
    Regards,
    Rudi

Posting Permissions

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