+ Reply to Thread
Results 1 to 4 of 4

Time Formatting issue/finding time difference

  1. #1
    Registered User
    Join Date
    02-04-2022
    Location
    USA
    MS-Off Ver
    360
    Posts
    2

    Lightbulb Time Formatting issue/finding time difference

    Hello. I am trying to take time data in 0000A/P format and find out how much time between the earliest time and the latest time. For Example:

    0630A
    0645A
    0715A
    0745A
    1030A
    1230P I am looking for a formula to calculate the difference of 6 hours automatically.

    I am sure someone here will know how to do it. Please help me! Thank you all so much!
    Last edited by Lydiahelpplz; 02-04-2022 at 02:39 PM.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,829

    Re: Time Formatting issue/finding time difference

    That is really an awful format to use.

    Here is your formula, assuming your data is in column A:

    =MAX(TIMEVALUE(LEFT($A$1:$A$6,2)&":"&MID($A$1:$A$6,3,2)&" "&RIGHT($A$1:$A$6,1)&"M"))-MIN(TIMEVALUE(LEFT($A$1:$A$6,2)&":"&MID($A$1:$A$6,3,2)&" "&RIGHT($A$1:$A$6,1)&"M"))

    This is an array formula. After typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    02-04-2022
    Location
    USA
    MS-Off Ver
    360
    Posts
    2

    Re: Time Formatting issue/finding time difference

    Thank you so much for the reply. I agree the format is awful. I tried to use this formula but it is giving me a #VALUE error. The data is in column A, but there is a header and the data will be in range A4-A99.

    Even if you just knew of a way to convert this time format into any other time format that Excel likes, I can find a way to get the difference in time that I am looking for. It's this awful format that is making things difficult for me.

    Thanks again for your help!

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,829

    Re: Time Formatting issue/finding time difference

    Please see yellow banner at the top and provide your actual file.

    If you have any spaces in your data it will cause errors. Highlight the column and use Find and Replace to make sure there are no spaces.

    data will be in range A4-A99
    Always explain this sort of thing in your first post. Update the formula to change $A$1:$A$6 to $A$4:$A$99 everywhere it occurs in the formula. However, if you have blanks, it will give you errors.

    If you want to create a parallel column with true time then use this in any column
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and fill down, then format for display as desired

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Time/Date difference issue
    By BobHughes182 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2017, 05:57 PM
  2. [SOLVED] New to excel .. time difference issue can anyone help
    By Russski in forum Excel General
    Replies: 4
    Last Post: 04-14-2016, 01:53 PM
  3. [SOLVED] Time in Time format and text foramt - Finding the Hours difference
    By thilag in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2013, 07:14 AM
  4. Combobox & Time Difference issue
    By hecgroups in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-07-2012, 12:35 PM
  5. Time sheet(difference) issue. Help!!!
    By austyno in forum Excel General
    Replies: 4
    Last Post: 05-30-2012, 12:47 AM
  6. Excel 2007 : Finding the difference in time
    By peter_f in forum Excel General
    Replies: 15
    Last Post: 10-08-2010, 03:30 PM
  7. Finding Time difference
    By John Harris in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2005, 07:06 PM

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