CODEWITHSUNDEEP
timegoogle-sheetsgoogle-sheets-formula
SophieD
SophieD

Reputation: 31

IF time range return "DAY AM", "DAY PM", "NIGHT"

Basic query but I keep getting #ERROR!. I want to auto generate "DAY AM", "DAY PM", "NIGHT", from the time. The failed formula I wrote is:

=IF(AND(D47>6,D47<18),”Day”,IF(AND(D47>18,D47<24),”NIGHT”,IF(AND(D47>0,D47<6),”NGHT”)))

In column D is the time, in 24 hr format e.g. 13:45 or 00:40. For the sake of this formula I am defining "day" as between 06:00 – 17:59. From 18:00 – 00:00 and 0:00 – 05:59 is "night".

Upvotes: 1

Views: 1841

Answers (3)

user2616155
user2616155

Reputation: 348

=let(inttime, int(substitute(mid(d47,1,5),":","")),
    if(inttime<559, "night",
        if(inttime<1759, "day",
            "night"
        )
    ) 
)

I find it easier to keep track of my nested function calls and parentheses by using indentation. This is kind of like a case statement or if-elseif-else-endif block.

I use the let() function to define a variable I call inttime. If d47 holds the datetime in exactly the format you provided, then mid() finds where the time is within that larger string, substitute() strips the colon out of it, then int() converts that into an integer, which I store to the variable inttime.

From there I start at midnight (0) and deal with every time period you define, earliest first. I can use that variable I defined earlier, instead of having to run functions to find the time again for every comparison. That "night" on its own line is in the else() section - what the time label defaults to if none of the other conditions are met.

Upvotes: 0

pnuts
pnuts

Reputation: 59495

In your formula, divide all hour numbers by 24. (06:00 is displayed but the underlying value is actually 0.25 (ie a quarter of a day) ).

Don't use smart quotes (“) where straight ones (") are required (ie throughout) (see).

Don't span noon if you want AM distinct from PM (ie break up (D47>6,D47<18) into something like (D47>6,D47<12) and (D47>12,D47<18)).

Decide what is to happen at the cusps. That is, for example, what happens when D47 is neither greater than 06:00 nor less than 06:00 (may want some equal signs alongside some angle brackets).

You are probably missing an I from the last word in your formula.

An OR in the right place might simplify your formula.

Upvotes: 0

TheMaster
TheMaster

Reputation: 50904

Try using straight quotes " instead of “

Upvotes: 1

Related Questions