# Comparing numbers in columns that are formatted in time hh:mm:ss

1. ## Comparing numbers in columns that are formatted in time hh:mm:ss

I have a huge amount of time entries to compare. Column A would be the Original entry and column B will be either a matching entry or a modified entry which can be greater than or less than the Original entry. I was able to get an answer for the formula where the B entry was greater than the original entry by using "SUM", but cannot figure out how to get an answer where the B entry is less than A.

A B
2:07:39 PM 2:07:39 PM 0:00:00

4:31:24 PM 5:30:00 PM 0:58:36

11:31:16 PM 6:00:00 PM #VALUE!

2. ## Re: Comparing numbers in columns that are formatted in time hh:mm:ss

How are you using SUM?

Maybe, =IF(A>B, A-B, B-A)

Regards, TMS

3. ## Re: Comparing numbers in columns that are formatted in time hh:mm:ss

Hi,

One way
Formula:
`Please Login or Register  to view this content.`

4. ## Re: Comparing numbers in columns that are formatted in time hh:mm:ss

Ok, i must be brain dead after working with this all day. Both of your formula's give TRUE as the response but i need the the difference expressed in "time". I have attached a small sample to work with, hoping that is easier. My file is 25,000+ entries.

Thanks...

5. ## Re: Comparing numbers in columns that are formatted in time hh:mm:ss

unless i'm missing it, I dont see you're attachment?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

6. ## Re: Comparing numbers in columns that are formatted in time hh:mm:ss

Try:
=IF(B2<A2,B2+1-A2,B2-A2)
Cell format: h:mm:ss

7. ## Re: Comparing numbers in columns that are formatted in time hh:mm:ss

based on the info you provided, try this...
=IF(B2>A2,B2-A2,A2-B2)

edit: make sure you format the cell to time (i used the option for 37:30:55)

8. ## Re: Comparing numbers in columns that are formatted in time hh:mm:ss

Thanks for the rep.

Just to confirm: =IF(A2>B2,A2-B2,B2-A2) copied down and formatted as Time (whatever you want it to look like)

Regards, TMS

9. ## Re: Comparing numbers in columns that are formatted in time hh:mm:ss

Yes, this formula seemed to work best with the spreadsheet i am using. Some of the other solutions may have worked as well, but this worked for me.

This is an amazing help site. I was so impressed with the quick help from around the globe!

10. ## Re: Comparing numbers in columns that are formatted in time hh:mm:ss

You're welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

##### 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