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

#### tblackwell

##### New Member
Situation: Simple nested IF statement that looks for time in one column and returns value in another column.
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

8puw close sun-thu_LYRow Labels01234567891011121314151617181920212223P4'18 - P9 '18
461:00 AM6052 \$ 431.34 \$ 1.86 \$ - \$ 0.82 \$ 644.10 \$ 2,807.94 \$ 3,999.49 \$ 3,048.39 \$ 2,120.80 \$ 1,724.30 \$ 1,655.59 \$ 2,231.40 \$ 2,610.06 \$ 2,370.69 \$ 1,995.78 \$ 1,541.64 \$ 1,062.71 \$ 800.28 \$ 1,541.64

</tbody>
weekday

Worksheet Formulas
CellFormula

</tbody>

<tbody>
</tbody>

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### joeu2004

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

##### Well-known Member

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

Last edited:

#### joeu2004

##### Well-known Member
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.

Last edited:

#### TWriter1

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

#### tblackwell

##### New Member
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!

Last edited:

Replies
2
Views
593
Replies
5
Views
549
Replies
10
Views
413
Replies
6
Views
320
Replies
1
Views
251

1,127,171
Messages
5,623,170
Members
415,955
Latest member
Footballtend

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back