Tuesday, September 1, 2015

What Day Is It?

There are often times when making your data a bit more human is a desirable goal.  For instance, by including the Weekday, as well as the Date in your database, you can bring a social element into your information you are providing.  After all, the difference between Monday and Friday may have great significance (especially if you have the weekend off…).

By combining the WEEKDAY function and VLOOKUP function with a simple lookup table, you can easily provide this additional element of information in your reports or analysis.

The WEEKDAY function extracts the weekday out of the date as a number from 1 to 7, with options of which weekday to start on.  The syntax of the WEEKDAY function is quite elementary:


Without the optional second argument, the function will return a number 1 to 7 (the same if you had entered the number 1…) for the Weekday of the date in the first argument, (Sunday being 1). You can, however, change the results by using any of the following as the second argument:
 Numbers 1 (Sunday) through 7 (Saturday).
 Numbers 1 (Monday) through 7 (Sunday).
 Numbers 0 (Monday) through 6 (Sunday).
 Numbers 1 (Monday) through 7 (Sunday).
 Numbers 1 (Tuesday) through 7 (Monday).
 Numbers 1 (Wednesday) through 7 (Tuesday).
 Numbers 1 (Thursday) through 7 (Wednesday).
 Numbers 1 (Friday) through 7 (Thursday).
 Numbers 1 (Saturday) through 7 (Friday).

Now let’s look at a straightforward example by combining WEEKDAY and VLOOKUP.  In the illustration below, we have put the following formula in cell C3 and copied it down to cell C9:

=VLOOKUP(WEEKDAY(B3, 1),$F$4:$F$9, 2, FALSE)

This unpretentious combo formula can add the Weekday to your data, and that may just be Good Information!

1 comment:

Unknown said...


I have a quick way to do it:

You could also use a defined name to hold the weekday list to make the formula even shorter:
Use a define name Days with

Cheers, Archimedeus