Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    387
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Working with hours and minutes

    I have a project that I have to work with hours:minutes.

    Is there an easy way of entering the data without me putting in the colon : symbol.

    Could I format the cells to accept the Hours and Minutes directly

    The next item is to add and subtract the data entered. I formated the cells with HR:MIN and then tried addition and then subtraction. That seem to work ok but the actual data is a time and not really Hrs and Minutes.

    Could Excel do the two items that I am trying to do? if so How do you do them.

    Thanks

    I am including a worksheet for illustrative purpose.
    Attached Files Attached Files

  2. #2
    2 Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    109
    Thanks
    12
    Thanked 3 Times in 3 Posts
    For the last question, if you mean you want the hours to add up over 24 then using your example, in cell B4 format that using Custom (I think its called that in English) and use [hh]:mm. For example if you have three days of 20 hrs and 25 mins it will display 61:15 the actual total of hours rather than convert it to days, hours and minutes.
    An easier way to add the cells together is to click on the cell where you want your total, in this case B4 then click the AutoSum button under the Formula tab. Its easier than typing out B1+B2+B3 etc.

  3. #3
    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
    Alan answered the formatting issue. Just to clarify:
    you had it formatted as h:mm which is a time of day so can only display a number <24 (23:59:59.99999 is the max). The [h]:mm gives the elapsed time and can be >24. Likewise [m]:ss will give elapsed min with secs and [s] will give elapsed seconds if those are desired.

    To enter without the colon you can use a macro to convert. See Chip Pearson's website at http://www.cpearson.com/excel/datetimeentry.htm for some code and explanation. If you do not want the worksheet change code (which disables the UNDO) a formula alternative is just to enter the hrs and mins in different cells and then use the time function to convert [=TIME(h,m,s)] before doing the arithmetic.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    387
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I'm not sure if I explained myself. I need only hours and minutes and to be able to key them in without inserting a colon each time by letting the format do that. is that a possibility?

  5. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    Steve answered that in the second part of his answer.

    cheers, Paul

  6. #6
    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 format does not change the number only the display of the number. You can format as:
    00":"00
    to get all 4 digits and add the colon. But you shouldn't do math directly with these numbers as the are still 4 digit numbers and aren't time. [for example 1130 + 35 = 1165 not 1205 as you would want]

    You would have to calculate a timevalue for each of them them with something like:
    =TIME(LEFT(RIGHT("0000"&A1,4),2),RIGHT(A1,2),0)
    and then do the math on the converted numbers. The macros on Pearson's page do the conversion, making them time after the values are entered

    Steve

Posting Permissions

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