If time is 1:00 AM throws value if false

tblackwell

New Member
Joined
Oct 24, 2018
Messages
24
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>
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,018
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Oct 24, 2018
Messages
24
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
Joined
Mar 2, 2014
Messages
3,018
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Mar 2, 2014
Messages
3,018
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 2, 2014
Messages
3,018
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Oct 25, 2018
Messages
7
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
Joined
Oct 24, 2018
Messages
24
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:

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,101
Members
416,161
Latest member
David1966Lewis

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
Top