# Man Day Calculation and Auto Sort

1. ## Man Day Calculation and Auto Sort

Good afternoon!

I'm working with Excel 2003 sp 3 on Windows xp.

I have been banging my head trying to jar loose the formula to calculate man days where 1 equals 8 hours. I have one that was given to me by a coworker:

=INT(Q3/8)+(((Q3/8)-(INT(Q3/8)))*0.8)

That calculates man days from total hours. I need a formula that will total man days and return the man day answer. Example:

weekending 12/25 = 16.2 which is 16 man days and 2 hours
weekending 1/1 = 24.7 which is 24 man days and 7 hours

Total = 41 man days and 1 hour

I have also been asked if there was a way for a number to be entered into a spreadsheet and automatically put in order without a macro or "pushing a button".

Happy New Year!

~ Brenda ~

2. ## Re: Man Day Calculation and Auto Sort

If we assume the hour integers are Q3:Q4 etc... then it will be simplest to work from those:

``Please Login or Register  to view this content.``
You can simplify your individual manday calcs along similar lines:

``Please Login or Register  to view this content.``
If you were to /or have already activate(d) the Analysis ToolPak Add-In you might consider using DOLLARFR:

for individual day:

``Please Login or Register  to view this content.``
For aggregate:

``Please Login or Register  to view this content.``
Regards your ordering question - can you explain with an example ?

You won't be able to sort the entry cells without intervention (ie sort command or use of VBA) - you could however sort the values in another range via formulae.

3. ## Re: Man Day Calculation and Auto Sort

I'm sorry but I tried keying in the first suggested formula to no avail. I've attached the spreadsheet that I'm working on in hopes that it will better show what I need. I do not have any addins unfortunately.

This is also the spreadsheet that I was asked if it could auto sort. Basically what they want is after inputting the job number, they want the spreadsheet to automatically sort by Job Number without having to push a button. I am pretty sure this cannot be done and have recommended putting a button macro on the form but I said I would check with the gurus.

Again, thank you so much for the help!

Happy New Year!

~ Brenda ~

4. ## Re: Man Day Calculation and Auto Sort

The formula for R3 would be as outlined:

``Please Login or Register  to view this content.``
If you then wanted to have total "Man Hours" hours & mandays in Q364:R364 respectively then

``Please Login or Register  to view this content.``
etc...

In terms of sorting - VBA required and not with Merged Cells.

5. ## Re: Man Day Calculation and Auto Sort

Actually I goofed. I should have removed the column for Total Hours as they want to input man days instead. The calculation for R3 would sum from D3 to P3 and return a man day total. For example:

D3 = 24.3 = 24 man days and 3 hours (man days = 8 hours)
E3 = 16.7 = 16 man days and 7 hours
F3 = 57.1 = 57 man days and 1 hour
R3 = 98.3 = 98 man days and 3 hours

I hope this isn't as confusing to you as it is to me.

Thank you again for your help!

~ Brenda~

6. ## Re: Man Day Calculation and Auto Sort

IMO even if you/they opt to enter D:P in terms of Mandays as described (as opposed to Hours) you would still be best served maintaining the Total Hours columns, you can calculate this figure off the manday values using:

``Please Login or Register  to view this content.``
at which point R3 remains as outlined previously:

``Please Login or Register  to view this content.``
If you remove the Total Hours columns and opt against Analysis ToolPak dependency (wise) then you're left with a certain amount of double evaluation.

7. ## Re: Man Day Calculation and Auto Sort

Thank you for your able assistance. I really need to get an Excel book to reference.

Happy New Year!

~ Brenda ~

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