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!
Last edited by FLHRguy; 10-31-2012 at 10:31 PM. Reason: add an attachment
How are you using SUM?
Maybe, =IF(A>B, A-B, B-A)
Regards, TMS
Last edited by TMS; 11-01-2012 at 01:39 AM.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hi,
One way
Formula:
Please Login or Register to view this content.
Last edited by Richard Buttrey; 10-31-2012 at 08:02 PM.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
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...
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.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Try:
=IF(B2<A2,B2+1-A2,B2-A2)
Cell format: h:mm:ss
Last edited by Armando Montes; 10-31-2012 at 11:23 PM. Reason: Typo
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)
Last edited by FDibbins; 10-31-2012 at 11:32 PM.
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
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!
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
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks