Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel formula query (2003)

    Hi,
    I have an excel formula that works out from a budget, how many hours we can afford to pay a person at a specific hourly rate.
    This bits fines.
    Next the user enter the amount of hours that that person is to work a week.
    This returns a value of the amount of weeks we can afford to employ that person for based on the week being set hours.
    So for instance:
    Budget:
    10000 at

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

    Re: Excel formula query (2003)

    Say the 21.69 is in cell D10. To get the fractional part, use the formula =D10-INT(D10).
    I assume this means a fraction of the work week, so to get the number of hours: =(D10-INT(D10))*37 (or replace 37 with the appropriate cell reference)
    To display as hours:minutes, divide by 24 (the number of hours in a day: =(D10-INT(D10))*37/24
    Then format the result with the custom format [h]:mm (the square brackets allow for hours above 24)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel formula query (2003)

    Thanks Hans. The formula is perfect.
    I would have gone about it a much longer way once I'd found out how to get the fraction. Thanks for that!!!

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel formula query (2003)

    Sorry Hans another query.
    Is there an easy way to add on the amount of weeks to today?
    The figure I have is 26.82 weeks employment, with the last week being 30hrs 13 mins. This is based on a 37 hour week.
    Working week is Monday to Friday.
    What I need to do is add 26.82 weeks to the current date.

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

    Re: Excel formula query (2003)

    How are the 37 hours distributed over Mon-Fri? Is it exactly 37/5 = 7.4 hours per day, or ...?

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel formula query (2003)

    Each week is exactly 37 hours, this is divided into 5 days of 7 hrs 24, which I worked out as 7.4 hrs, precisely (hoping my calculations are correct). Each week is solely Monday to Friday.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel formula query (2003)

    Excellent, you wouldn't believe how long some of my formulas were getting.
    Thank you for all your help, I'll leave you in peace now!

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

    Re: Excel formula query (2003)

    Make sure the Analysis ToolPak add-in has been activated/installed (in Tools | Add-Ins...)
    The attached workbook uses the WORKDAY function to add the appropriate number of days, taking weekends into account. (You could even take holidays into account if you created a table of holidays)

Posting Permissions

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