Forum Discussion
If statement with time.
I'm trying to have a cell that will add text based on what time it is currently. The Nested statement that I have does not put in the text. I have A cell set to Now() I have Cell B with the valu...
As variant
=IF( MEDIAN($B$1:$C$1, NOW() ) = $B$1, "Low",
IF( MEDIAN($B$1:$C$1, NOW() ) = NOW(), "Med",
IF( MEDIAN($B$1:$C$1, NOW() ) = $C$1, "High"
)))
ok here is what is going on. The current time is 2:19pm
The if statement is =IF( MEDIAN($D$75:$E$75, NOW() ) =$D$75, "Low",IF( MEDIAN($D$75:$E$75, NOW() ) = NOW(), "Med",IF( MEDIAN($D$75:E$75, NOW() ) = $E$75, "High")))
This is what I'm getting
- I was struggling with this too. Your statement worked and solved my problem.
Just so that I understand things properly for next time: does the part of the formula LET(t, MOD(NOW(),1) convert the current time into a number value/format for the IF statement to compare against as opposed to being a date/time format?Excel stores dates and times as numbers, with 1 day as unit. In a date/time, the integer (whole number) part of the value represents the date, and the fractional part the time.
NOW() returns the current date AND time.
MOD(NOW(), 1) removes the integer part and leaves only the fractional part, i.e. the time. We can compare this with the times in D75 and E75.
- That worked!! thanks
