NOON for 12:00 PM

**HuskerBronco** · 12-14-2008, 03:33 PM

How should the formula read to check the TIME value of a certain cell for 12:00 PM?
If the TIME in the referenced cell is 12:00 PM...return NOON.

Here is what I have...and it doesn't work:

Please Login or Register  to view this content.

**daddylonglegs** · 12-14-2008, 04:00 PM

That formula should work assuming Data!C12 contains just a time.

What's in C12, is it generated by a formula?

**HuskerBronco** · 12-14-2008, 04:59 PM

What's in C12, is it generated by a formula?

When I select the cell, this is what shows in the formula bar:

Please Login or Register  to view this content.

The cell (C12) is TIME formatted, and the time entered comes from a drop-down list containing all times from 10:00 AM to 11:00 PM...incremented every 30 minutes.
The NAMED RANGE that the drop-down list comes from DID contain formulas...but I overwrote them with TIME values...and it still didn't work.

**shg** · 12-14-2008, 05:11 PM

Format C12 as General and what do you see?

**daddylonglegs** · 12-14-2008, 05:12 PM

Excel sometimes behaves oddly when you increment times like that. I can't replicate the specific problem, though. What formula did you use to increase the time by 30 minutes?

Try using this version

=IF(TEXT(DATA!C12,"h:mm")="12:00","NOON",DATA!C12)

**protonLeah** · 12-14-2008, 05:18 PM

I just tried your formula and it worked for me. You said that "... it doesn't work ..." so what do you see instead of "NOON"? Also, if the time in C12 is supposedly 12:00:00 and you temporarily change the cell format to numeric is it 0.50000? If you change the formula to:

Please Login or Register  to view this content.

what does it display?

**HuskerBronco** · 12-14-2008, 05:21 PM

Format C12 as General and what do you see?

In the formula bar... 0.499999998666667

And...it still says 12:00 PM in the target cell. (Not NOON)

**shg** · 12-14-2008, 05:23 PM

Well -- that's not noon.

You could use DLL's suggestion, or fix the drop-down ...

**HuskerBronco** · 12-14-2008, 06:17 PM

What formula did you use to increase the time by 30 minutes?

In cell A1 of the NAMED RANGE "Times" I have 10:00 AM.
Cell A2 contains the following formula:

Please Login or Register  to view this content.

The formula in A2 was then copied down enough cells to display 11:00 PM as the last time.
I haven't got a clue why this wasn't giving me a .5 for 12:00 PM (but rather...0.499999998666667).

Try using this version

=IF(TEXT(DATA!C12,"h:mm")="12:00","NOON",DATA!C12)

This formula, on the other hand, worked perfectly....!!!
Thank you, so much for your help.

**shg** · 12-14-2008, 06:33 PM

I haven't got a clue why this wasn't giving me a .5 for 12:00 PM

Because repeating decimals can't be exactly represented.

**daddylonglegs** · 12-14-2008, 06:55 PM

Originally Posted by HuskerBronco

Cell A2 contains the following formula:

Please Login or Register  to view this content.

Better to use

=A1+"0:30"

or

=A1+TIME(0,30,0)

**HuskerBronco** · 12-15-2008, 08:30 AM

Better to use =A1+"0:30" or =A1+TIME(0,30,0)

So...that was my problem the whole time.

I switched the formula in Cell A2 to =A1+TIME(0,30,0)...and copied it down.
Then I used the new drop-down menu to enter the time in each cell from C1 down.

Finally, I reverted to my original formula in the target cells...and it worked exactly as I needed it to!!!

Didn't think this would be so difficult...