Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Adding times (Excel 2000 SP3)

    I have a simple spreadsheet which I am using to calculate elapsed times, which I then want to add together.

    The two source data columns (A & [img]/forums/images/smilies/cool.gif[/img] contain times on the 24 hour clock.

    Column C subtracts A from B to give the elapsed time in hours. Column C is in time format.

    I then want to add together all the hours in column C to give a total no of hours.

    The answer my sum formula is giving is wrong, and I have traced the error to some entries in Column C which I entered manually, because the normal B-A formula didn't work.

    The manual entries were for scenarios where, for example:-

    Start time = 1330

    End time = 0030

    Shift time total 11 hours, but the nomal A-B formula just results in a row of hash signs.

    So - 2 questions:-

    Can I reformat columns A and B so my normal B-A formula works? (I've tried the format which includes a date, but this just returns an error when subtracting one from the other).

    If that doesn't work, how can I add together my column C if it includes manual entries?

    Thanks in anticipation

    Neil

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding times (Excel 2000 SP3)

    Are the times entered as 1330 or as 13:30? If the latter, try this, assuming the data start in A1 and B1:

    =B1-A1+(B1<A1)

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

    Re: Adding times (Excel 2000 SP3)

    There should be no problem subtracting the times if the cell contains both the date and the time. The attached workbook shows how it should work.

    We would have to see what you are doing to tell you why it does not work. Could you upload a workbook that shows what you are doing?
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Adding times (Excel 2000 SP3)

    Legare / Hans

    Many thanks for your replies - helpful as ever!

    I have got it to work with the date + time format. It seems that as the only pre-defined format I could select was in American date order, I had assumed that I needed to enter my date in mm/dd/yy order, and it was that which was causing the error to appear.

    Once I entered my date in dd/mm/yy order, it worked! (Presumably by referencing my short date format in regional settings).

    I wonder if Microsoft are aware just how awkward they make it for Excel 2000 users wanting to use British date formats (mostly by the fact that every possible date format except the British one is listed in the standard format options)?

    Does it get any easier in versions later than 2000?

    Thanks again

    Neil

Posting Permissions

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