Hi, Harlan Grove, ? about negative time formula
 

I found a post with your reply of,

=IF(TargetTime<=ActualTime,"+ ","-
")&TEXT(MAX(TargetTime,ActualTime)-MIN(TargetTime,ActualTime),"[m]:ss")

I made it work for me like this,

=IF(C3<=D3,"+ ","- ")&TEXT(MAX(D3,C3)-MIN(D3,C3),"[m]")

The display in the cells is -0. Is there a way to format so the cell looks
blank until a calculation is made?

Also can the cell display OT if the result = 0

Hi, Harlan Grove, ? about negative time formula
 

Try this formula

=IF(C3<=D3,"OT","")

does this do what you want?

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"Dale G" wrote:

I found a post with your reply of,

=IF(TargetTime<=ActualTime,"+ ","-
")&TEXT(MAX(TargetTime,ActualTime)-MIN(TargetTime,ActualTime),"[m]:ss")

I made it work for me like this,

=IF(C3<=D3,"+ ","- ")&TEXT(MAX(D3,C3)-MIN(D3,C3),"[m]")

The display in the cells is -0. Is there a way to format so the cell looks
blank until a calculation is made?

Also can the cell display OT if the result = 0

Hi, Harlan Grove, ? about negative time formula
 

=IF(MOD(D3-C3,1)=0,"OT",IF(C3<=D3,"+ ","-
")&TEXT(MAX(D3,C3)-MIN(D3,C3),"[m]"))

--
__________________________________
HTH

Bob

"Dale G" wrote in message
...
I found a post with your reply of,

=IF(TargetTime<=ActualTime,"+ ","-
")&TEXT(MAX(TargetTime,ActualTime)-MIN(TargetTime,ActualTime),"[m]:ss")

I made it work for me like this,

=IF(C3<=D3,"+ ","- ")&TEXT(MAX(D3,C3)-MIN(D3,C3),"[m]")

The display in the cells is -0. Is there a way to format so the cell looks
blank until a calculation is made?

Also can the cell display OT if the result = 0

Hi, Harlan Grove, ? about negative time formula
 

On Mon, 15 Dec 2008 21:11:00 -0800, Dale G
wrote:

I found a post with your reply of,

=IF(TargetTime<=ActualTime,"+ ","-
")&TEXT(MAX(TargetTime,ActualTime)-MIN(TargetTime,ActualTime),"[m]:ss")

I made it work for me like this,

=IF(C3<=D3,"+ ","- ")&TEXT(MAX(D3,C3)-MIN(D3,C3),"[m]")

The display in the cells is -0. Is there a way to format so the cell looks
blank until a calculation is made?

Also can the cell display OT if the result = 0

and another:

=CHOOSE(SIGN(A1-A2)+2,"-","","+") &TEXT(ABS(A1-A2),"[m]")

--ron

Hi, Harlan Grove, ? about negative time formula
 

Im using =IF(C3<=D3,"+ ","- ")&TEXT(MAX(D3,C3)-MIN(D3,C3),"[m]")

In column E to show a positive sign or a negative sign in front of the
calculated different of Column C & D.

Time Actual Diff
5:33 5:34 +1
5:43 5:39 - 4
5:46 5:48 +2
6:00 5:59 - 1
6:00 6:00 +0
6:12 6:11 - 1
6:15 6:15 +0
6:30 6:30 +0
6:30 - 0
6:40 - 0

Notice 6:15 & 6:30 are (ON Time) thats why I would like the OT to show.

Also if the last 2 could appear blank.

So far, Im not having any luck with the suggestion.

This one =IF(C3<=D3,"OT","") leaves cell blank, but I would need to
combined it with something else to make it work.

Hi, Harlan Grove, ? about negative time formula
 

Dale G wrote...
....
I made it work for me like this,

=IF(C3<=D3,"+ ","- ")&TEXT(MAX(D3,C3)-MIN(D3,C3),"[m]")

The display in the cells is -0. Is there a way to format so the cell looks
blank until a calculation is made?

=TEXT(MAX(C3:D3)-MIN(C3:D3),IF(C3<=D3,"\+ ","\- ")&"[m];;")

Also can the cell display OT if the result = 0

Something like

=TEXT(MAX(C3:D3)-MIN(C3:D3),IF(C3<=D3,"\+ ","\- ")&"[m];;\O\T")

? Or do you mean something other than = ?

Hi, Harlan Grove, ? about negative time formula
 

Dale G wrote...
....
In column E to show a positive sign or a negative sign in front of the
calculated different of Column C & D.

Time * *Actual *Diff
5:33 * *5:34 * *+1
5:43 * *5:39 * *- 4
5:46 * *5:48 * *+2
6:00 * *5:59 * *- 1
6:00 * *6:00 * *+0
6:12 * *6:11 * *- 1
6:15 * *6:15 * *+0
6:30 * *6:30 * *+0
6:30 * * * * * *- 0
6:40 * * * * * *- 0

Notice 6:15 & 6:30 are (ON Time) that’s why I would like the OT to show. *

Also if the last 2 could appear blank.
....

Details help.

=IF(COUNT(C3:D3)=2,TEXT(MAX(C3:D3)-MIN(C3:D3),
IF(C3<D3,"\+ ","\- ")&"[m];;\O\T"),"")

Hi, Harlan Grove, ? about negative time formula
 

Thank you, this
=TEXT(MAX(C3:D3)-MIN(C3:D3),IF(C3<=D3,"\+ ","\- ")&"[m];;\O\T")
works & looks like,

Time Actual Diff
5:33 5:33 + 0
5:43 5:43 OT
5:46 5:47 + 1
6:00 6:00 OT
6:00 6:01 + 1
6:12 6:12 OT
6:15 6:14 - 1
6:30 OT
6:30 OT
6:40 OT

Notice the first cell after the calculation shows +0.
The others work fine.

Also the cell shows OT until the Actual time & the calculation is made.

I thought the ;; would have the effect of the cell appearing blank? Did you?

Is it possible to have the cells appear blank?

Hi, Harlan Grove, ? about negative time formula
 

On Tue, 16 Dec 2008 08:01:04 -0800, Dale G
wrote:


I’m using =IF(C3<=D3,"+ ","- ")&TEXT(MAX(D3,C3)-MIN(D3,C3),"[m]")

In column E to show a positive sign or a negative sign in front of the
calculated different of Column C & D.

Time Actual Diff
5:33 5:34 +1
5:43 5:39 - 4
5:46 5:48 +2
6:00 5:59 - 1
6:00 6:00 +0
6:12 6:11 - 1
6:15 6:15 +0
6:30 6:30 +0
6:30 - 0
6:40 - 0

Notice 6:15 & 6:30 are (ON Time) that’s why I would like the OT to show.

Also if the last 2 could appear blank.

So far, I’m not having any luck with the suggestion.

This one =IF(C3<=D3,"OT","") leaves cell blank, but I would need to
combined it with something else to make it work.



If you just need to display the results, and not use them in subsequent
calculations, this seems to work OK he

=IF(COUNT(A2:B2)=2,TEXT((B2-A2)*1440,"0;-0;""OT"""),"")

--ron

Hi, Harlan Grove, ? about negative time formula
 

On Tue, 16 Dec 2008 12:59:46 -0500, Ron Rosenfeld
wrote:

If you just need to display the results, and not use them in subsequent
calculations, this seems to work OK he

=IF(COUNT(A2:B2)=2,TEXT((B2-A2)*1440,"0;-0;""OT"""),"")

--ron

That should be:

=IF(COUNT(A2:B2)=2,TEXT((B2-A2)*1440,"+0;-0;""OT"""),"")

--ron

Hi, Harlan Grove, ? about negative time formula
 

Yes that works. =IF(COUNT(C3:D3)=2,TEXT((D3-C3)*1440,"0;-0;""OT"""),"")


Time Actual Diff
5:33 5:33 -0
5:43 5:43 OT
5:46 5:47 1
6:00 5:58 -2
6:00 5:45 -15
6:12 6:12 OT
6:15 6:15 OT
6:30 6:28 -2
6:30 6:32 2
6:40 6:40 OT
6:45
6:55
7:00
7:06

The cell appears blank, very good.

The same trouble exists with the first cell.

Notice the first cell shows -0 instead of OT? Other than that its very good.

Thank you.

Hi, Harlan Grove, ? about negative time formula
 

Dale G wrote...
....
Notice the first cell shows -0 instead of OT? . . .
....

Probably there's a slight difference between the first 2 cells, e.g.,
one contains 5:33:02 and the other 5:33:07. Try

=IF(COUNT(C3:D3)=2,TEXT(ROUND((D3-C3)*1440,0),"+0;-0;""OT"""),"")

Hi, Harlan Grove, ? about negative time formula
 

=IF(COUNT(C3:D3)=2,TEXT(ROUND((D3-C3)*1440,0),"+ 0;- 0;""OT"""),"")

Yes that works, thank you.

(5:33:02 and the other 5:33:07)

I had that problem once before, it seems like it has something to do with
the computer internal clock?

Thanks again.

Hi, Harlan Grove, ? about negative time formula
 

On Tue, 16 Dec 2008 10:31:00 -0800, Dale G
wrote:

Yes that works. =IF(COUNT(C3:D3)=2,TEXT((D3-C3)*1440,"0;-0;""OT"""),"")


Time Actual Diff
5:33 5:33 -0
5:43 5:43 OT
5:46 5:47 1
6:00 5:58 -2
6:00 5:45 -15
6:12 6:12 OT
6:15 6:15 OT
6:30 6:28 -2
6:30 6:32 2
6:40 6:40 OT
6:45
6:55
7:00
7:06

The cell appears blank, very good.

The same trouble exists with the first cell.

Notice the first cell shows -0 instead of OT? Other than that it’s very good.

Thank you.



I see Harlan pointed out your problem in that the 5:33's were not exactly the
same, and also provided a solution.

Glad you've got it working.
--ron