Time Formatting issue/finding time difference

1. 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!

2. 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.

3. 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. 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:
`Please Login or Register  to view this content.`

and fill down, then format for display as desired

Thread Information

Users Browsing this Thread

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

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