Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    adding time (97)

    I simply need to add time.

    I'd like to be able to enter (e.g) "11.30" in A1 (i.e 11 hrs 30min) , "13.30" (ie 13 hrs 30 min) in A2 and in A3 get the result 25 (i.e. 25hrs) when I add them together.

    Seems impossible...

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: adding time (97)

    You need to use a custom format for the cell that contains the sum. The custom format should be [h]:mm (with the brackets around the h) if you want elapsed hours and minutes. If you also want seconds then use [h]:mm:hh. You should also probably be using a colon between the hour and the minute when you enter the time unless your separator has been changed to use the decimal point, otherwise, 11.30 is not 11 hours and 30 minutes, it is 11 days and a little less than 8 hours.
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: adding time (97)

    Adding a note to Legares' post: if you are going to calculate time differences across days, such as from 11:30 pm to 1:30 am next day, you will need to track the dates, or do some presumptive logic by adding 12 hours if start time is less than end time.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: adding time (97)

    thanks guys,

    it's a bast*rd to the data entry though - numpad with right hand, shift colon with left hand...

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: adding time (97)

    just realised I've got another problem.

    I've been given a ss to work on, with all the "times" already entered. Trouble is, I've got a feeling XL thinks it's text. For eg in A1 there's 1:30, in A2 1:45. However when I add them up in A3 I get 0, rather than 3.15. So I guess the originator of the ss tried(?) to enter the numbers in time format (hence the colon), but for some reason things have gone pear shaped.

    We're talking thousands of numbers here, so I hope there's some way of converting what I've got into calculable times.

    Many TIA

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: adding time (97)

    When you select one of these, exactly what shows in the formlua bar, and what is the cell format?
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: adding time (97)

    Pending your answer to my other question, and assuming the data is in columns, see if this works. You need to select any cells in each column or adjacent columns containing the text which should be time values:

    Sub Text2Time()
    Dim rngCell As Range
    Dim rngTemp As Range
    On Error Resume Next
    Set rngTemp = Selection.EntireColumn.SpecialCells(xlCellTypeCons tants, 2)
    If Err.Number = 1004 Then
    MsgBox "No text cells in Selected Column"
    Exit Sub
    End If
    rngTemp.NumberFormat = "h:mm"
    For Each rngCell In rngTemp
    rngCell.Value = rngCell.Value
    Next rngCell
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    Jun 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: adding time (97)

    One solution may be to "force" the cells to be numeric. I've recovered text (values, dates and times) this way many times: type a 1 in any cell; copy that cell; select the range to be fixed; from a right click or the full menu, choose Paste Special, then choose values and MULTIPLY. It's the multiplying by 1 (which doesn't change the value) that forces it to be a value and not text.

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: adding time (97)

    If the times have already been entered in hh.mm format, either as text or numbers, the following formula will convert them to time values in hh:mm format:
    =TIME(INT(A1),(A1-INT(A1))*100,)
    You'll need to be sure that what you've got is really in in hh.mm format and not a decimal hors format (eg 1.75 for 1:45).

    If the times have been entered as text in hh:mm format, , the following formula will convert them to time values in hh:mm format:
    =TIME(LEFT(A1,FIND(":",A1)-1),MID(A1,FIND(":",A1)+1,LEN(A1)-FIND(":",A1)),)

    Both could be coded as macros to replace the errant entries, but I don't have the time right now ...
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: adding time (97)

    Hi CJ,

    Checkout Chip Pearson's great site for details of quick entry of dates and times. I modified it slightly to use in Oz (Australia!) and to let those who don't know enter times and dates the slow way as well.

    Good Luck!

    Peter Moran

Posting Permissions

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