+ Reply to Thread
Results 1 to 7 of 7

Man Day Calculation and Auto Sort

  1. #1
    Registered User
    Join Date
    07-06-2010
    Location
    Livonia, MI
    MS-Off Ver
    Excel 2003
    Posts
    4

    Question 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".

    Any advice is greatly appreciated!

    Happy New Year!

    ~ Brenda ~

  2. #2
    Forum Moderator DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,520

    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.
    Last edited by DonkeyOte; 12-30-2010 at 03:30 PM. Reason: typo

  3. #3
    Registered User
    Join Date
    07-06-2010
    Location
    Livonia, MI
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Man Day Calculation and Auto Sort

    Thank you for your response!

    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 ~
    Attached Files Attached Files

  4. #4
    Forum Moderator DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,520

    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. #5
    Registered User
    Join Date
    07-06-2010
    Location
    Livonia, MI
    MS-Off Ver
    Excel 2003
    Posts
    4

    Exclamation 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~
    Last edited by BAAllen; 12-30-2010 at 05:18 PM.

  6. #6
    Forum Moderator DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,520

    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. #7
    Registered User
    Join Date
    07-06-2010
    Location
    Livonia, MI
    MS-Off Ver
    Excel 2003
    Posts
    4

    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 ~

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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