#NUM! message when getting time difference

**stoey** · 07-23-2009, 02:34 PM

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

**DonkeyOte** · 07-23-2009, 03:57 PM

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.

**stoey** · 07-23-2009, 05:40 PM

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

**DonkeyOte** · 07-23-2009, 05:43 PM

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)

**stoey** · 07-23-2009, 05:48 PM

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

**stoey** · 07-23-2009, 06:02 PM

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