Can anyone help?
I have two columns, one containing previous time (eg 3.10.04, ie 3 minutes, 10 seconds and 04 milliseconds) and 2nd column containing new time (eg 3.12.89).
I need a formula to give me the difference between the 2 times (result may be positive or negative time). I formulated the columns to be "time" but result in #VALUE! error.
Don't worry, I've figured it out. Had my columns formulated to "time" but needed to custom it to mm:ss.0.
Thanks anyway
I used custom formatting mm.ss.00 and then subtracted them using a simple excel =B1-A1 and arrived at 00.02.85
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thanks alansidman, I figured the formatting part out. Now I have another problem. If the result ends in a negative amount (which I need to determine), I get ################ or #VALUE!
How do I rectify this?
=if(A1-B1>0,A1-B1,(B1-A1)*-1) This is untested and off the top of my head.
That won't work either.
Look at what MS has to say on the subject of negative times.
http://support.microsoft.com/kb/182247
Last edited by alansidman; 09-30-2013 at 07:39 PM.
welcome to the forum, LGT06. so what do you need shown when it's a negative? if it's always the later time minus the earlier, then:
=MAX(A1:B1)-MIN(A1:B1)
if the earlier time is supposed to be a day later:
=MOD(A1-B1,1)
you need to format to see the hours
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Thankyou everyone for your help. I was calculating swimming times and had to show whether the swimmer's latest time was faster or slower than previous time swum.
All it took was changing to the 1904 Date System.
Thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks