Greater than in if statements?

[IRMacGuyver]

It seems to want to reference a cell when I use ">"

I basically want it to say
=IF(calculate!K21=0, " ", if( calculate!K21>12:59:59, calculate!K21-12:00:00, calculate!K21))
but it says there's an error

That way I can have the cells always return 12 hour increments instead of military time and return nothing but a space if there's a zero value in the referenced cell.

[daddylonglegs]

You have to put the time in quotes or use TIME function, something like this

=IF(calculate!K21=0,"",calculate!K21-IF(calculate!K21>"12:59:59"+0,"12:00",0))

Note: I used a blank "", rather than a space " " - which is more usual practice

[IRMacGuyver]

ah thank you much

Is there any reason you added the "+0"?

[IRMacGuyver]

I used =IF(calculate!K21=0, " ", IF( calculate!K21>"12:59", calculate!K21-"12:00", calculate!K21))
and it stayed 13:00
Then I tried =IF(calculate!K21=0, " ", IF( calculate!K21>"12:59", calculate!K21, calculate!K21-"12:00"))
and that worked for times over 12:59 but broke when the times were under 12:59
The whole point is that I'm trying to get rid of military time

[JBeaucaire]

Military time is a formatting decision, not a cell value. If you select cell K21, press CTRL-1, you can change the display format to TIME > 1:30 PM and you'll have the same results without another cell.

[daddylonglegs]

Jerry is right, of course, you might be better off with some formatting rather than changing the values but to answer your question.........Yes, you need the +0 (or something similar)

"12:59:59" in quotes is just text unless you perform some sort of calculation on it (that doesn't change the value) like +0 or *1....that "coerces" it to a TIME value. The formula I suggested should do what you want........or you can just use numbers - a day in excel = 1 so 12 hours = 0.5, 13 hours = 13/24 etc. so.....

=IF(calculate!K21=0,"",calculate!K21-IF(calculate!K21>13/24,0.5,0))

[IRMacGuyver]

Military time is a formatting decision, not a cell value. If you select cell K21, press CTRL-1, you can change the display format to TIME > 1:30 PM and you'll have the same results without another cell.

But then it will display the AM/PM and I need to save room and simplify it visually by dropping that.

Okay I see why you added the +0 but when the reference cell has a word instead of a time that equation breaks. I also don't quiet get the ',"12:00",0' part.

EDIT oh I think I get why you're doing that now. It's part of the equation to add to the base reference.