If time is 1:00 AM throws value if false

[tblackwell]

Situation: Simple nested IF statement that looks for time in one column and returns value in another column.
Formula: =IF(E46=TIME(3,0,0),I46,IF(E46=TIME(2,0,0),H46,IF(E46=TIME(1,0,0),G46,IF(E46=TIME(0,0,0),AD46,IF(E46=TIME(23,0,0),AC46,AB46)))))
Hint: This formula is in column AF
Expected result: If time in E46 is 1:00 AM, then return value in G46 ($431.34)
Problem: If time in E46 is 1:00 AM, then returns the FALSE value in AB46 ($1,541.64). What am I doing wrong? *Bangs head against wall*
System: Windows 10 Pro

	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AF
puw close sun-thu_LY	Row Labels
6052

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]8[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]P4'18 - P9 '18[/TD]

[TD="align: center"]46[/TD]
[TD="align: right"]1:00 AM[/TD]

[TD="align: right"] $ 431.34 [/TD]
[TD="align: right"] $ 1.86 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"] $ 0.82 [/TD]
[TD="align: right"] $ 644.10 [/TD]
[TD="align: right"] $ 2,807.94 [/TD]
[TD="align: right"] $ 3,999.49 [/TD]
[TD="align: right"] $ 3,048.39 [/TD]
[TD="align: right"] $ 2,120.80 [/TD]
[TD="align: right"] $ 1,724.30 [/TD]
[TD="align: right"] $ 1,655.59 [/TD]
[TD="align: right"] $ 2,231.40 [/TD]
[TD="align: right"] $ 2,610.06 [/TD]
[TD="align: right"] $ 2,370.69 [/TD]
[TD="align: right"] $ 1,995.78 [/TD]
[TD="align: right"] $ 1,541.64 [/TD]
[TD="align: right"] $ 1,062.71 [/TD]
[TD="align: right"] $ 800.28 [/TD]
[TD="align: center"] $ 1,541.64 [/TD]

</tbody>

weekday​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AF46[/TH]
[TD="align: left"]=IF(E46=TIME(3,0,0),I46,IF(E46=TIME(2,0,0),H46,IF(E46=TIME(1,0,0),G46,IF(E46=TIME(0,0,0),AD46,IF(E46=TIME(23,0,0),AC46,AB46)))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

My guess: the value in E46 is text, not numeric time. Does =ISTEXT(E46) return TRUE?

Some possible reasons: (a) E46 cell was formatted as Text when you entered the value; (b) there are spaces before 1:00; or (c) there are non-breaking spaces.

Try re-entering the value, being certain that the cell is formatted as General or another numeric format before you re-enter.

 

[tblackwell]

My guess: the value in E46 is text, not numeric time. Does =ISTEXT(E46) return TRUE?

Some possible reasons: (a) E46 cell was formatted as Text when you entered the value; (b) there are spaces before 1:00; or (c) there are non-breaking spaces.

Try re-entering the value, being certain that the cell is formatted as General or another numeric format before you re-enter.

Good idea but it is already formatted as Time.

 

[joeu2004]

Try re-entering the value

That is just for triage purposes; that is, to demonstrate the root cause (text value).

Once you identify why E46 is text (if it is), we can offer methods of corrections, if you need them.

But as wild guess, try the following:

1. Enter =--TRIM(SUBSTITUTE(E46,CHAR(160),CHAR(32))) into another cell, say X1.

2. If X1 does not result in a #VALUE error, format E46 as Time, copy X1, and paste-value into E46; then clear X1.

 

[joeu2004]

Good idea but it is already formatted as Time.

Did you try =ISTEXT(E46)?

We can enter text (inadvertently?) into a cell that has a numeric format.

Moreover, if we enter a valid numeric representation (like 1:00 AM) into a cell when it is formatted as Text, then change the format to Time (e.g), the cell value remains type text unless and until we "re-enter" it (e.g. press f2, then Enter).

 

[joeu2004]

Just realized: I don't believe you explained the origin of the value in E46. I a.s.s-u-me-d you entered it directly.

But if it is a calculation, perhaps the decimal result is not identical to TIME(1,0,0) to 15 significant digits.

If that might be the problem, replace =expression in E46 to =--TEXT(expression,"[h]:mm"). That ensures that if the result appears to be 1:00, the binary representation will match TIME(1,0,0).

PS.... That might not be the best replacement for the formula in E46 (if there is one). Post the formula.

 

[TWriter1]

I tried what you have here in a brand new blank worksheet and it worked fine for me -- showing your expected results.

 

[tblackwell]

SOLVED: If time is 1:00 AM throws value if false

Did you try =ISTEXT(E46)?

We can enter text (inadvertently?) into a cell that has a numeric format.

Moreover, if we enter a valid numeric representation (like 1:00 AM) into a cell when it is formatted as Text, then change the format to Time (e.g), the cell value remains type text unless and until we "re-enter" it (e.g. press f2, then Enter).

Ok I think that is what happened. The 1:00 AM was from another sheet, formatted as text, then I changed the format to time. It remained text until I re-entered it.

That's a frustrating user error, but it is solved thanks to you! Much appreciated!