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!

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.

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:

=TIMEVALUE(LEFT(A4,2)&":"&MID(A4,3,2)&" "&RIGHT(A4,1)&"M")

and fill down, then format for display as desired