+ Reply to Thread
Results 1 to 6 of 6

Sum of hours and minutes over 1000hrs and converting to week days

  1. #1
    Registered User
    Join Date
    07-17-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    21

    Sum of hours and minutes over 1000hrs and converting to week days

    Hi,
    Please help to get a appropriate formula to sum below hours, I am getting the total as 537:11:27 (shown in A6) if I use =sum(A1:A5),
    • Need formula to sum these hours
    • Convert to weekdays considering only Mon-Fri (5days in a week), expecting in this format 5w 2d

    Provide these two formula by considering that i am going to use in two cells.

    Column h:mm
    A1 1101:58
    A2 2.59
    A3 2003:11
    A4 19.41
    A5 0.39
    A6 (Total) 537:11:27
    Last edited by Chito; 07-16-2013 at 06:22 AM.

  2. #2
    Registered User
    Join Date
    07-17-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Sum of hours and minutes over 1000hrs and converting to week days

    One more request, please consider 8hrs=1day when converting to week/day

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,270

    Re: Sum of hours and minutes over 1000hrs and converting to week days

    Please post a sample sheet so we can see real formatting

  4. #4
    Registered User
    Join Date
    07-17-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Sum of hours and minutes over 1000hrs and converting to week days

    Please find the below template...
    Attached Files Attached Files

  5. #5
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Sum of hours and minutes over 1000hrs and converting to week days

    Hi Chinto..

    Can you please check by changing below two formula..
    In E7 instead of =INT(((Calculation!N24+Calculation!N48)*0.02)/60)&":"&MOD(((Calculation!N24+Calculation!N48)*0.02),60)

    check with Below formula..
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and in E9, instead of =INT(((Calculation!N24+Calculation!N48)*0.15)/60)&":"&MOD(((Calculation!N24+Calculation!N48)*0.15),60) this formula check with below..
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Format the cell as required..

    Now.. hope your SUM formula is working..

    Above both formula returning TEXT.. not possible to use in SUM
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  6. #6
    Registered User
    Join Date
    07-17-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Sum of hours and minutes over 1000hrs and converting to week days

    Hi Debraj Roy,thanks for the response...

    I am still having some issue. E7 is showing 62.103 which I feel is incorrect, I am expecting in h:mm format, also it does not sum all the rows, see below image.

    It will be great if you could test in the attached template and provide an solution, thanks

    image.JPG

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. converting minutes to days, hours and minutes.
    By TheGrimm in forum Excel General
    Replies: 8
    Last Post: 11-23-2015, 02:23 AM
  2. Converting to Days, Hours and Minutes
    By StaceyB in forum Excel General
    Replies: 7
    Last Post: 02-29-2012, 02:42 PM
  3. Converting hours into days, hours & minutes
    By John754 in forum Excel General
    Replies: 3
    Last Post: 10-07-2009, 06:28 PM
  4. [SOLVED] converting Days Hours & minutes into just minutes in excel
    By Six Sigma Blackbelt in forum Excel General
    Replies: 5
    Last Post: 04-28-2006, 04:45 PM
  5. [SOLVED] Problem converting Hours to Days, Hours, Minutes
    By Zyzzx in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-24-2005, 12:05 PM

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