Message based on day of the week in Excel
Q: I want to write a function on a worksheet that checks the day of the week and pops up a message. For example, if today is Tuesday, the message would read "Pop Quiz". I can't seem to get the day value to work within the IF statement.
A: Here is the formula you need:
=IF(WEEKDAY(TODAY())=3,"It's Tuesday!","It's some other day")
The weekday function has a second argument, too, which is optional, determining which day of the week you regard as the first. If you omit it - as in my example - then Sunday is 1, Monday is 2, etc.
If you only want the message to appear on Tuesday and nothing at all to appear other days, then replace the second string in my example with empty quotes ("").
If you actually want a different message to appear every day, you could nest several IF statements, but it would be more efficient to create a lookup table, like this:
The formula could then read:
=VLOOKUP(WEEKDAY(TODAY()),A1:B7,2,FALSE)
Or, if rather than picking up from today's date, you want to pick up from a date contained in a cell (let's say in A10) then use:
=VLOOKUP(WEEKDAY(A10),A1:B7,2,FALSE)
Incidentally, the 'FALSE' argument at the end isn't necessary here, but it's good practice to always put one there. What it means is, "Don't rely on the list of items in the first column of the table being in alphanumeric order - check every one of them until you get an exact match." Leaving it out is like saying, "The first column of the lookup table is definitely in alphanumeric order - if you get past the search phrase in the list and it's not there, don't keep looking, just use the nearest match". This would speed up your sheet if there were a lot of huge lookup tables in it, but here it's not even worth thinking about. Like I say, it's good practice always to include "FALSE" just in case it trips you up one day.