1. 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. 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. 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. 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. 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. 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. 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. 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
•