Hi, Harlan Grove, ? about negative time formula

[Dale G]

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

 

[xlm]

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,

 

[Bob Phillips]

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

 

[Ron Rosenfeld]

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

 

[Dale G]

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.

 

[Harlan Grove]

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(C33)-MIN(C33),IF(C3<=D3,"\+ ","\- ")&"[m];;")

Also can the cell display OT if the result = 0

Something like

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

? Or do you mean something other than = ?

 

[Harlan Grove]

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(C33)=2,TEXT(MAX(C33)-MIN(C33),
IF(C3<D3,"\+ ","\- ")&"[m];;\O\T"),"")

 

[Dale G]

Thank you, this
=TEXT(MAX(C33)-MIN(C33),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?

 

[Ron Rosenfeld]

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 here:

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

--ron

 

[Ron Rosenfeld]

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

=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

 

[Dale G]

Yes that works. =IF(COUNT(C33)=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.

 

[Harlan Grove]

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(C33)=2,TEXT(ROUND((D3-C3)*1440,0),"+0;-0;""OT"""),"")

 

[Dale G]

=IF(COUNT(C33)=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.

 

[Ron Rosenfeld]

Yes that works. =IF(COUNT(C33)=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