Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Lounger
    Join Date
    Oct 2008
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    39
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Hi to all

    I require a function in excel which will do the following:

    Add together in one cell; example 1 and a half hours + 2 hours + 45 minutes + and so on.

    I know how to do this in a column of cells but NOT a list of hours and minutes in a single cell, it always comes up with an error flag.

    any help on this one guys....

    regards

    Steve Gulliver - Southampton UK

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can put them all into hours:

    =1.5 +2 +.75+...

    or even (if desired)

    =1+30/60 + 2 + 45/60 +....

    Steve




  3. #3
    Lounger
    Join Date
    Oct 2008
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    39
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    You can put them all into hours:

    =1.5 +2 +.75+...

    or even (if desired)

    =1+30/60 + 2 + 45/60 +....

    Steve
    Thanks very much for the rapid response, that would certainly work up to a point. I think I may have phrased it incorrectly with a poor example.

    I would like to be able to add up in hours and minutes and set of number such as. 1 hr 32 mins + 3 hrs 19 mins + 19 hrs 13 mins, not just easily rounded numbers like .25 or .45 etc.. I am attempting to get the spreadsheet to work out the answer for me, I am aware that I can just make 3hrs 19 mins into 199mins then divide by 60. However I am looking for a way of just inserting the hours and minutes into the cell as I have them put in front of me by a work colleague.

    Hope I have made it a little clearer to all...

    kind regards

    Steve Gulliver - Southampton UK

  4. #4
    5 Star Lounger petesmst's Avatar
    Join Date
    Dec 2009
    Location
    Cape Town, South Africa
    Posts
    790
    Thanks
    38
    Thanked 43 Times in 33 Posts
    I am not sure what you want: If you type =1+30/60+2+23/60 into the formual bar, you will get the answer in the selected cell. If you type (for example) 1 into cell A1, =30/60 into cell A2, 2 into cell A3 and =23/60 into cell A4 and then type =sum(A1:A4) into cell A5 you will get the same answer. Either way, excel will calculate the total for you. To enter a whole hour and a number of minutes, just type in minutes/60 and add to the whole number hour wherever applicable, either in the formula bar; or in adjacent cells and then sum all cells.

    Alternatively: (Neater) Format your cells for the entry of degrees, minutes, seconds (slightly modified) - see below. You then enter your data in the format hh:mm:ss (hours:minutes:seconds). Adding (summing) such data will yield a correct answer (as opposed to formatting the cells in the "time" format which would always adjust the answer to fit in to the 24-hour clock). To do this, select: format, cells, time, custom. In the space beneath "type", enter the following: [hh] :mm :ss

    The [] tells excel not to roll-over the time to confirm to the 24-hour clock. For example, the time value 30:40:50 would normally be displayed as 06:40:50. To correct this, you can use a number format of [hh]:mm:ss. The square brackets [ ] instruct Excel not to roll over the displayed value as a "time of day" value.

    Your cells will now accept data entered as follows: 04:32:01 (4 hours 32 minutes 1 second). See attached example

    EDIT:
    You cannot do all the summing in the SAME cell "on the fly" which is what I think you wanted to do. Excel uses formula to add, subtract, etc, discrete data in cells and then provides the answer in another cell. You will need to enter each person's time sheet time in a different cell and then use the "sum" function to add the times together (see my attached example)
    Attached Files Attached Files
    (My Setup: Custom built: 4.00GHz Intel Core i7-6700K CPU; MSI Z170A Gaming Carbon Motherboard (Military Class III); Win 10 Pro (64 bit)-(UEFI-booted); 16GB RAM; 512GB SAMSUNG SD850 PRO SSD; 120GB SAMSUNG 840 SSD; Seagate 2TB Barracuda SATA6G HDD; 2 X GeForceGTX 1070 8GB Graphics Card (SLI); Office 2013 Prof (32-bit); MS Project 2013 (32-bit); Acronis TI 2017 Premium, Norton Internet Security, VMWare Workstation12 Pro). WD My Book 3 1TB USB External Backup Drive). Samsung 24" Curved HD Monitor.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Can I ask why you would want to do this in one cell? You're pretty much defeating Excel that way.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Lounger
    Join Date
    Oct 2008
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    39
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Can I ask why you would want to do this in one cell? You're pretty much defeating Excel that way.
    Hi again all..

    Okay I will attempt to make myself clearer...

    When I arrive in my office in the morning I am confronted with dozens of timesheets, on these timesheets it lists the drivers hours and ALSO their 'Periods of Availability'. Whilst inputting their 'POA' data I am trying to insert the hours and minutes into my spreadsheet in the format in which it is shown on their timesheets. ie:

    Monday - 1hr 42 minutes
    Tuesday - 2hr 32minutes
    Wednesday 3hr 49 minutes...etc

    I do not wish to put in the number as: =1+42/60+2+32/60..etc
    I would like to insert as: 1:42+2:32+3:49..etc

    This can partially be done in the cell formatting using [h]:ss command, but it will NOT allow me to use the same formatting to add them all together in the same cell..

    I hope you understand....

    kind regards

    Steve Gulliver - Southampton UK

  7. #7
    Lounger
    Join Date
    Oct 2008
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    39
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Peter S View Post
    I am not sure what you want: If you type =1+30/60+2+23/60 into the formual bar, you will get the answer in the selected cell. If you type (for example) 1 into cell A1, =30/60 into cell A2, 2 into cell A3 and =23/60 into cell A4 and then type =sum(A1:A4) into cell A5 you will get the same answer. Either way, excel will calculate the total for you. To enter a whole hour and a number of minutes, just type in minutes/60 and add to the whole number hour wherever applicable, either in the formula bar; or in adjacent cells and then sum all cells.

    Alternatively: (Neater) Format your cells for the entry of degrees, minutes, seconds (slightly modified) - see below. You then enter your data in the format hh:mm:ss (hours:minutes:seconds). Adding (summing) such data will yield a correct answer (as opposed to formatting the cells in the "time" format which would always adjust the answer to fit in to the 24-hour clock). To do this, select: format, cells, time, custom. In the space beneath "type", enter the following: [hh] mm' ss\"

    Your cells will now accept data entered as follows: 04:32 (4 hours 32 minutes). See attached example
    Thanks very much Peter for taking the timeout to look at my query, and thanks for the download, as I mentioned earlier I am familiar with adding them up in columns, the difficulty I face is trying to add up the hours and minutes in a single cell. Please have a look at my latest posting for more details...kind regards..Steve

  8. #8
    5 Star Lounger petesmst's Avatar
    Join Date
    Dec 2009
    Location
    Cape Town, South Africa
    Posts
    790
    Thanks
    38
    Thanked 43 Times in 33 Posts
    @hajankel: Please see the edit in my post.
    (My Setup: Custom built: 4.00GHz Intel Core i7-6700K CPU; MSI Z170A Gaming Carbon Motherboard (Military Class III); Win 10 Pro (64 bit)-(UEFI-booted); 16GB RAM; 512GB SAMSUNG SD850 PRO SSD; 120GB SAMSUNG 840 SSD; Seagate 2TB Barracuda SATA6G HDD; 2 X GeForceGTX 1070 8GB Graphics Card (SLI); Office 2013 Prof (32-bit); MS Project 2013 (32-bit); Acronis TI 2017 Premium, Norton Internet Security, VMWare Workstation12 Pro). WD My Book 3 1TB USB External Backup Drive). Samsung 24" Curved HD Monitor.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The direct way you want to do, can NOT be done.

    If entered into a cell direectly a number with colons ( is understood by excel to be part of a date. When enetered, excel will do the math and convert the time value to a number representing a number of days (all excel time is in units of days), and then format the cell to display similar to how it was entered. If the entry can not be understood by excel as a date it will be presumed to be text and will not be converted to a number.

    When you place several values in a cell as a FORMULA [starting with an equal (=), a plus (+), or a minus(-)], excel does not convert them but tries to understand what you enter as a formula. Colons in formulas represent ranges. Other than mathematical operations, and parentheses only a period (.) is used to represent a placeholder in a number. No commas, no colons: either will give an error in a formula (unless part of text or representing a range or parameter).

    The simplest way to do what you ask is to add the hours as I suggested:
    =1+30/60+2+45/60...

    It does not require only the "simple" fractions but will work with all the minutes. You can even include seconds by dividing them by 3600. But note the hours can not be formatted as hours since they are not days. If you do this way and want to format as time in excel they need to be days so you would need something more like:
    =(1+30/60+2+45/60+ ...)/24
    to convert the hours to days...

    Since you are entering the individual items anyway, I don't see the advantage of one cell entry. For faster data entry, having intermediate cells for hours and minutes for each day and then adding them up and putting the sum into the cell would be much faster:
    =(sum(hours) + sum(minutes)/60)/24
    will give the days and formatted as [h]:mm will display the elapsed time in hours and minutes...

    Steve

  10. #10
    Lounger
    Join Date
    Oct 2008
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    39
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Well first and foremost,

    thanks very much to all of you kind people for helping me out with this problem, your all very kind

    I would just like to add that wouldn't you guys think that a piece of software that is soooo sophisticated would indeed be able to do what I have requested it to do in the manner in which I asked it.... and to all the doubters out there who will come back with this can NEVER be done "remember NOTHING is impossible"...


    thanks to you all once again....Hare Krysna!!

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    No, because by using a different layout, it's a simple matter. Whilst you can often force Excel to work the way you think it should do, there's usually an easier way and more often than not, it's a better way.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    5 Star Lounger petesmst's Avatar
    Join Date
    Dec 2009
    Location
    Cape Town, South Africa
    Posts
    790
    Thanks
    38
    Thanked 43 Times in 33 Posts
    @rory: Agreed!
    (My Setup: Custom built: 4.00GHz Intel Core i7-6700K CPU; MSI Z170A Gaming Carbon Motherboard (Military Class III); Win 10 Pro (64 bit)-(UEFI-booted); 16GB RAM; 512GB SAMSUNG SD850 PRO SSD; 120GB SAMSUNG 840 SSD; Seagate 2TB Barracuda SATA6G HDD; 2 X GeForceGTX 1070 8GB Graphics Card (SLI); Office 2013 Prof (32-bit); MS Project 2013 (32-bit); Acronis TI 2017 Premium, Norton Internet Security, VMWare Workstation12 Pro). WD My Book 3 1TB USB External Backup Drive). Samsung 24" Curved HD Monitor.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It could be done, in some manner. If entered as text (not a formula) one could write a routine that parsed the text, then did the mathematical manipulations and replaced the text with the number. But as it has been pointed out there are easier ways...

    Steve

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is a method that is "almost" what you desire.

    In a module add the following function:
    [codebox]
    Option Explicit
    Function TimeSum(str As String)
    Dim AWF As WorksheetFunction
    Set AWF = Application.WorksheetFunction
    TimeSum = Evaluate("=timevalue(" & Chr(34) & _
    AWF.Substitute(str, "+", " AM" & Chr(34) & _
    ")+timevalue(" & Chr(34)) & " AM" & Chr(34) & ")")
    Set AWF = Nothing
    End Function
    [/codebox]

    Then in a cell you can use it to sum the times. Use it like:
    =Timesum("1:30+2+0:45")

    This is close to what you wanted only in addition to entering all the values you must preface the function name and enter the list of times as a string. If desired the function name can be shortened (change it both places in the module...)

    Steve

  15. #15
    5 Star Lounger petesmst's Avatar
    Join Date
    Dec 2009
    Location
    Cape Town, South Africa
    Posts
    790
    Thanks
    38
    Thanked 43 Times in 33 Posts
    @sdckapr: Neat! I have learned something new. That's the great benefit of this forum. Thanks.
    (My Setup: Custom built: 4.00GHz Intel Core i7-6700K CPU; MSI Z170A Gaming Carbon Motherboard (Military Class III); Win 10 Pro (64 bit)-(UEFI-booted); 16GB RAM; 512GB SAMSUNG SD850 PRO SSD; 120GB SAMSUNG 840 SSD; Seagate 2TB Barracuda SATA6G HDD; 2 X GeForceGTX 1070 8GB Graphics Card (SLI); Office 2013 Prof (32-bit); MS Project 2013 (32-bit); Acronis TI 2017 Premium, Norton Internet Security, VMWare Workstation12 Pro). WD My Book 3 1TB USB External Backup Drive). Samsung 24" Curved HD Monitor.

Page 1 of 2 12 LastLast

Posting Permissions

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