Simple function made difficult
 

Bare with me on this one...I simply need to subtract A1 from A2 and have the
value appear in A3. All three cells are formatted as TIME.

Here comes the complicated part.

Before subtracting A1 from A2, I need to first calculate the values of A1
and A2 using the following functions within cell A3:

MOD(INT((A1+TIME(0,7,0))*24*4)/24/4,1)
MOD(INT((A2+TIME(0,7,0))*24*4)/24/4,1)

Then all I need to do is subtract A1 from A2.

Example

A1 = 5:33 AM
A2 = 3:09 PM

without the MOD function A2-A1 = 9:36
With the MOD function A2-A1 = 9:45

How can I incorporate the MOD function in cell A3 which already contains
=A2-A1 ?

Simple function made difficult
 

this, in a3, doesn't work?

=MOD(INT((A2+TIME(0,7,0))*24*4)/24/4,1)-MOD(INT((A1+TIME(0,7,0))*24*4)/24/4,1)

--


Gary


"GreenMonster" wrote in message
...
Bare with me on this one...I simply need to subtract A1 from A2 and have
the value appear in A3. All three cells are formatted as TIME.

Here comes the complicated part.

Before subtracting A1 from A2, I need to first calculate the values of A1
and A2 using the following functions within cell A3:

MOD(INT((A1+TIME(0,7,0))*24*4)/24/4,1)
MOD(INT((A2+TIME(0,7,0))*24*4)/24/4,1)

Then all I need to do is subtract A1 from A2.

Example

A1 = 5:33 AM
A2 = 3:09 PM

without the MOD function A2-A1 = 9:36
With the MOD function A2-A1 = 9:45

How can I incorporate the MOD function in cell A3 which already contains
=A2-A1 ?

Simple function made difficult
 

One way:

=FLOOR(A2+7/1440,1/96) - FLOOR(A1+7/1440,1/96)

or, with one less math operation

=(FLOOR(A2*1440+7,15) - FLOOR(A1*1440+7,15))/1440



In article ,
"GreenMonster" wrote:

Bare with me on this one...I simply need to subtract A1 from A2 and have the
value appear in A3. All three cells are formatted as TIME.

Here comes the complicated part.

Before subtracting A1 from A2, I need to first calculate the values of A1
and A2 using the following functions within cell A3:

MOD(INT((A1+TIME(0,7,0))*24*4)/24/4,1)
MOD(INT((A2+TIME(0,7,0))*24*4)/24/4,1)

Then all I need to do is subtract A1 from A2.

Example

A1 = 5:33 AM
A2 = 3:09 PM

without the MOD function A2-A1 = 9:36
With the MOD function A2-A1 = 9:45

How can I incorporate the MOD function in cell A3 which already contains
=A2-A1 ?