hi everyone,
does anybody knows an idea how to get the time difference without getting the #NUM! error message?
Column A contains Start Time while Column B contains end time, if the time in column A is 11:45 PM and the time in column b is 12:15 AM, then I would get this error message...any idea how can i get the time difference without having to use the [hh]:mm format? i would like to use the hh:mm AM/PM format instead. I appreciate any response.
Thanks,
Stoey
Last edited by stoey; 07-24-2009 at 05:57 PM.
Hi,
How's this:
=IF(B1<A1,1+B1-A1,B1-A1)
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
or
=MOD(B1-A1,1)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
hi Dave and Donkey,
thanks for the quick response. Both formulas worked. However, i do still have a problem in incorporating it with other formula...column A contains start time while column B contains end time...column C contains the result value. how would be the formula in column C such that if the resulting value of the difference of start time and end time is less than 30 minutes then the statement "ON TIME should be displayed in Column C. On the other hand,if the time difference is more than 30 minutes then it should display the time difference instead.
Example:
A B C
11:00 PM 11:30 PM ON TIME
11:00 PM 12:00 AM 0:30
11:00 PM 12:30 AM 1:00
I appreciate any response...
Thanks,
Stoey
one way, avoiding repetition of calculation and/or any alteration to formula in C would be to use a Custom Format on C itself of:
[<=0.0208333333333333]"On Time";[hh]:mm
the underlying value where <= 30 mins will remain a time value but the text string "On Time" will mask the value.
hi donkey,
i appreciate your quick reply again...to put it in a formula will it be this way?
=IF(MOD(G91-F91,1) >30, "ONTIME", MOD(G91-F91,1)-"00:30")
please correct my coding because i am receiving values which are ########## of characters..
thanks,
Stoey
it should be < 0+"00:30" rather than > 30 ... 30 as far as XL is concerned equates to 30 days whereas "00:30" is 30 minutes.
Last edited by DonkeyOte; 07-23-2009 at 05:52 PM. Reason: noob error and omitted the 0+ coercion!
hi again Donkey,
just tried this formula with the help of the value you have given and it worked perfectly!
=IF(MOD(G91-F91,1) <=0.0208333333333333, "ONTIME", MOD(G91-F91,1)-"00:30")
just another question...is the value 0.0208333333333333 equivalent of 30 minutes? if so what would be the equivalent value if i use 15 minutes on the other hand?
i appreciate your help
regards,
stoey
That approach was for use in conjunction with Custom Format approach, if using formula I would opt for:
=IF(MOD(G91-F91,1)<=0+"00:30","ONTIME",MOD(G91-F91,1)-"00:30")
change the "00:30" to be whatever time span you want (but yes in essence the value provided was a rough representation of 30 mins in numerical terms, 24 hours = 1)
oh i see...thanks for letting me know about that..i was not aware of it...thanks again...i appreciate your timely response...
cheers!
stoey
oh by the way donkey last follow up question...sorry about this but how do i go with the formula such that if cell A and B are blank then cell C is blank also using the formula
=IF(MOD(B1-A1,1)<=0+"00:30","ONTIME",MOD(B1-A1,1)-"00:30")
...if there would be value on cell A but not on cell B then C is still blank.
if there would be value on cell B but not on cell A then C is still blank.
however if A and B have values then C values appears...
sorry for all the hassle...thanks a lot
regards,
stoey
You can use a COUNT test given both A & B should be numeric, ie:
=IF(COUNT(A1:B1)=2,IF(MOD(B1-A1,1)<=0+"00:30","ONTIME",MOD(B1-A1,1)-"00:30"),"")
hi donkey,
thanks a lot! worked like a charm! thanks again...
Regards,
stoey
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks