If time is 1:00 AM throws value if false

tblackwell

New Member
Joined
Oct 24, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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



EFGHIJKLMNOPQRSTUVWXYZAAABACADAF
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

<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>
</tbody>
weekday

Worksheet Formulas
CellFormula
AF46=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)))))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
I tried what you have here in a brand new blank worksheet and it worked fine for me -- showing your expected results.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top