Subtraction when including the MOD() function

[Gadgets]

Hi,

I am trying to do some simple math, but it will include the MO
function, and I can't get the result I need.

what I have is one column of datetime (dd/mm/yy hh:mm) and anothe
column of just time (hh:mm). I need to subtract the TIME ONLY in th
datetime column from the time column. This is what I thought was right
but it doesn't seem to be.

=(K3-(MOD(MARIE_STATS!A3,1)))

The answer I get is -0.82 if the cell is formatted as a "Number". If
try to format it as "Time" (hh:mm) I get all #########. The actual
value in this case should be 00:37 (37 minutes. So, somewhere I b
messing up, can someone straighten me out please?

FYI cell K3 is the higher value so I shouldn't bet a negative.

Thanks,
Bria

 

[Bob Phillips]

Brian,

Your formula works fine for me, where A3 is date and time, K3 is just time.
Is that what you have?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Harlan Grove]

Gadgets wrote...
....

what I have is one column of datetime (dd/mm/yy hh:mm) and another
column of just time (hh:mm). I need to subtract the TIME ONLY in the
datetime column from the time column. This is what I thought was right,
but it doesn't seem to be.

=(K3-(MOD(MARIE_STATS!A3,1)))

The answer I get is -0.82 if the cell is formatted as a "Number". If I
try to format it as "Time" (hh:mm) I get all #########. The actualy
value in this case should be 00:37 (37 minutes. So, somewhere I be
messing up, can someone straighten me out please?

FYI cell K3 is the higher value so I shouldn't bet a negative.

What are K3 and A3 exactly?

If your formula does return a negative number, then Excel won't display
it in Time formats unless you use the 1904 date system. [Why Excel
can't display negative times in the 1900 date system is one of the
universe's deeper mysteries.] Note that the absolute value of the
result, 0.82, is more than 19 hours rather than just 37 minutes, so it
looks like there's something wrong in either K3 or A3 or both.

 

[Gadgets]

Yup, here are my exact values in the cells in this example:

K3= 20:15 A3= 05/04/2005 19:38

I want to subtract the 19:38 portion of A3 from the 20:15 in K3 (jus
want to make sure we're on the same page here. )

Thanks for the assistance Bob!

Brian

 

[Gadgets]

D-O-H-!-

Got it working, stupid me forgot to add what sheet I was pulling the
information in K3 from. adding that, gives me exactly what it should
have... I can be so stupid sometimes! However, I am new at this excel
stuff.. that should count for some level of stupidity!

Thanks again for the help Bob,

Brian

 

[Harlan Grove]

Gadgets wrote...

Yup, here are my exact values in the cells in this example:

K3= 20:15 A3= 05/04/2005 19:38

....

You've got Transition Formula Evaluation enabled and the K3 value is
text. Either make the K3 value numeric, turn off Transition Formula
Evaluation, or use

=(TIMEVALUE(K3)-MOD(MARIE_STATS!A3,1))