+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : DateTime Difference with Military Date/time

  1. #1
    Registered User
    Join Date
    10-28-2010
    Location
    Richmond,V irginia
    MS-Off Ver
    Excel 2003
    Posts
    1

    DateTime Difference with Military Date/time

    Ok, so I admit I'm feeling a little stupid here . I have 2 columns that hold date/time in this format: 07/25/2010 14:07:19. I need to find the date/ time difference between the two columns, so column P with 07/25/2010 14:07:19 and column V with 7/25/2010 14:50:13 difference should go into column X with 7/25/2010 00:43:06.

    I have tried various formulas and formatting, but nothing has worked yet. HELP, Gurus!

    Thank you in advance!!

    Salem Poe

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: DateTime Difference with Military Date/time

    Just subtract the smaller from the larger. The result is 00:43:06, not 7/25/2010 00:43:06.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: DateTime Difference with Military Date/time

    12/01/2010 07:45
    13/01/2010 08:45

    using TEXT(D44-D43:D43,"d"" days ""h"" hours ""m"" mins """)

    yeilds

    1 days 1 hours 0 mins
    Windows 7 using Office 2007 & 2010

    Remember your [ code ] [ /code ] tags, makes reading soooo much easier

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: DateTime Difference with Military Date/time

    @scottylad2 - not that it's relevant to the question but using "d" in a custom number format is risky given the limitation of 31 cumulative days.

  5. #5
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: DateTime Difference with Military Date/time

    Quote Originally Posted by DonkeyOte View Post
    @scottylad2 - not that it's relevant to the question but using "d" in a custom number format is risky given the limitation of 31 cumulative days.
    true enough, how would one get by that?

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: DateTime Difference with Military Date/time

    Possibles:

    a) splitting the calc in two - first half returning the INT of the difference with "days" suffix and the second half as before though without the "d" element)

    Please Login or Register  to view this content.
    b) fudging the calc such that days become hours, hours minutes and so on and so forth - hardly worthwhile and obviously very risky in terms of latter calcs:

    Please Login or Register  to view this content.
    with option b) you could still store as a number and use standard Number Format of: [h]" day(s) "mm" hours "ss" minutes "

    but even then if you want to display seconds you have an issue and of course all units are in effect out of kilter

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1