Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date & Time Diff (Excel 2000)

    I have a list with 2 columns. The one has the date in and the other the time of day. My first problem is how do I combine this?
    My second problem is how do I calculate the difference in time and days from different rows Refer attached sample

    Thanks
    Attached Files Attached Files

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

    Re: Date & Time Diff (Excel 2000)

    Mario,

    See attached. Your dates came out as text in my Dutch version of Excel, so I used a formula to convert them to real dates. This formula is dependent on local settings.
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date & Time Diff (Excel 2000)

    Mario
    In New Zealand I cannot use a "." in a date format and have Excel understand it.

    However, if the date were a valid date (e.g. 01/11/2002 is in NZ) it can be formatted as a date.
    The time is already formatted as a time

    The maths is then simply date + time into a cell formatted as "d.mm.yyy hh:mm:ss"
    e.g. C1 contains =A1 + B1
    The reason this works is that date serial numbers represent dates as whole numbers while times are represented as fractions of a day. e.g. 1 hour is stored as 1/24th.
    To get a days/time difference simply subtract two date or time entities. e.g. date1 - date2 gives a difference in days
    and, datetime1 - datetime2 gives a difference in days & hours and minutes and seconds.
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date & Time Diff (Excel 2000)

    Thanks everyone.

    The magic answer was the "+" sign between the 2 columns

Posting Permissions

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