Subtraction when including the MOD() function

[Gadgets]

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

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

"Gadgets" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

Thanks for the assistance Bob!

Brian

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

DOH!

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

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

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

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