Subtraction when including the MOD() function
 

Hi,

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

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.

Thanks,
Brian


--
Gadgets
------------------------------------------------------------------------
Gadgets's Profile: http://www.excelforum.com/member.php...o&userid=36784
View this thread: http://www.excelforum.com/showthread...hreadid=565354

Subtraction when including the MOD() function
 

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)

"Gadgets" wrote in
message ...

Hi,

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

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.

Thanks,
Brian


--
Gadgets
------------------------------------------------------------------------
Gadgets's Profile:
http://www.excelforum.com/member.php...o&userid=36784
View this thread: http://www.excelforum.com/showthread...hreadid=565354

Subtraction when including the MOD() function
 

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.

Subtraction when including the MOD() function
 

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 (just
want to make sure we're on the same page here. :-) )

Thanks for the assistance Bob!

Brian

Bob Phillips Wrote:
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)

"Gadgets" wrote
in
message ...

Hi,

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

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.

Thanks,
Brian


--
Gadgets

------------------------------------------------------------------------
Gadgets's Profile:
http://www.excelforum.com/member.php...o&userid=36784
View this thread:
http://www.excelforum.com/showthread...hreadid=565354



--
Gadgets
------------------------------------------------------------------------
Gadgets's Profile: http://www.excelforum.com/member.php...o&userid=36784
View this thread: http://www.excelforum.com/showthread...hreadid=565354

Subtraction when including the MOD() function
 

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


Gadgets Wrote:
Hi,

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

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.

Thanks,
Brian


--
Gadgets
------------------------------------------------------------------------
Gadgets's Profile: http://www.excelforum.com/member.php...o&userid=36784
View this thread: http://www.excelforum.com/showthread...hreadid=565354

Subtraction when including the MOD() function
 

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))