Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Date/Time HELP PLEASE!!!

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Dan
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a report that is given to me on a bi-weekly basis. In this report I need to calculate the time difference in time between cell A1: "1/2/02 2:45:00" & B1: "1/2/02 2:46:00" which in this matter is "00:1:00".

    I do not have the option of having these separated into date cell and time cell. As there are over 50,000 lines of this, I would like to automate with a formula.

    The closest I have got to solving is the following:
    =INT(SUM(A1:B2)/0.04166667)&":" &MINUTE(MOD(SUM(A1:B2),1))&":" &SECOND(MOD(SUM(A1:B2),1))

    Now this gives me a value of: 1788389:31:0

    I believe the date and time being in the same cell is effecting this. I have tried TIMEVALUE however I get a #VALUE error.

    Could someone help me out PLEASE?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Unless I'm missing something here?
    Put =B1-A1 in whatever cell and format it as
    such HH:MM:SS
    Tom

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If I understand you correctly, you can use
    =B1-A1 and Custom Format it as [h]:mm:ss

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tom:
    It is telepathy at work ...

    Regards!

  5. #5
    New Member
    Join Date
    May 2002
    Location
    Dan
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey,

    I have been staring at this too long, thanks for the extra set of eyes!!! This group RoCkS!! I will return the favor in the future.
    Thank you for the help!!!


  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I like yours better
    [h]:mm:ss
    Tom

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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