1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Calculatnig the difference between two dates and times

Discussion in 'Business Applications' started by Moll45, Apr 23, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. Moll45

    Moll45 Thread Starter

    Joined:
    Jun 2, 2003
    Messages:
    94
    Good afternoon knowledgeable people,

    Can someone please help and tell me how I can calculate the difference between two columns both formatted as dd/mm/yyy hh:mm.

    I do need to be specific to hours and minutes - is this possible.

    Many thanks as ever,

    Moll
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> I do need to be specific to hours and minutes ...

    ... and to which program you're using. Perhaps it's Excel ; if so, and assuming the data is in columns A & B, just use a formula in column C such as

    =B1-A1

    The only hitch is you will need to custom format the result cells. Hit CTRL+1, on the Number tab select the Custom category, create a type such as

    [h]:mm

    Once you've done this for one cell, use Format Painter to apply to the whole range or column.

    Rgds,
    Andy
     
  3. Moll45

    Moll45 Thread Starter

    Joined:
    Jun 2, 2003
    Messages:
    94
    Hi Andy,

    Yes sorry I should've said it is Excel :eek: .

    I had already done what you suggested but I am not getting the right answers.

    e.g.

    20/04/2004 11:00 - 21/04/2004 08:00 = 21:00
    20/04/2004 11:00 - 21/04/2004 10:30 = 23:30
    20/04/2004 11:00 - 21/04/2004 09:05 = 22:05
    20/04/2004 11:00 - 21/04/2004 11:15 = 00:15
    20/04/2004 11:00 - 21/04/2004 18:00 = 07:00

    As you can see if the second time is earlier than the first it works out ok, but if the first time is lower than the second I'm 24 hours out, unfortunately I don't know enough about Excel to be able to automatically fix this.

    Thanks,

    Moll
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Moll,

    works for me. Must be something to do with the format(s). Check your setting is as the picture. If no joy, zip & post your file.

    Rgds,
    Andy
     

    Attached Files:

  5. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    I get the same results as you posted if I don't have the "h" in [] brackets. These are used to force (display of) time calcs > 24 hours.

    Rgds,
    Andy
     
  6. Moll45

    Moll45 Thread Starter

    Joined:
    Jun 2, 2003
    Messages:
    94
    Success - thank you so much - :D :D :D


    Moll
     
  7. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Glad to help. Have a good weekend. :)

    Rgds,
    Andy
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/223154

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice