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

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

=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

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

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.

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 = ?

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

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?

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

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

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.

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

=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.

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