Day of Week Routine

Suppose you wanted to get a number that represented a "day of the week" given any date. How would you do this in Lotus 1-2-3? How would you show the actual day of the week based on this number?

Here's one solution by way of an example. Start with an empty spreadsheet and in cell A1 put the function @DATE(98,7,19) and use [Menu] Range Format Date 1 and [Menu] Worksheet Column Set-width 12 to make the contents of the cell appear as 19-Jul-98.

July 19, 1998, occurs on a Sunday which for many people is the first day of the week: for others it's the last day of the week. If you want the day of the week number to be 1, put the function @MOD(A1-1,7)-1 in cell B1. You should see 1 appear in the cell when you press Enter. The @MOD() function returns the remainder when it divides the Lotus date-value by 7. Since the remainder can be any number between 0 and 6, we simply subtract one from the date-value and add 1 to the answer to get a number between 1 and 7.

If you prefer to have Sunday as the 7th day of the week and Monday as the first day, then put the formula @MOD(A1-2,7)+1 in any cell (e.g., cell D1). You should see 7 when you press Enter.

If you want to show the name of the day in a given cell, use the formula

@CHOOSE(@mod(@date(yy,mm,dd)-1,7)+1,"NULL","SUN","MON","TUE","WED","THU","FRI","SAT") or the formula @CHOOSE(@mod(@date(yy,mm,dd)-2,7)+1,"NULL","MON","TUE","WED","THU","FRI","SAT","SUN"). Use the first formula if you prefer Sunday as the first day of the week. Use the latter formula if you want Sunday as the seventh day of the week.

If you're not interested in the day-of-the-week number, you can shorten the formulas above to @CHOOSE(@MOD (@DATE(98,7,19),7),"SAT","SUN","MON","TUE","WED","THU","FRI") which will simply give you the day of the week.

By way of explanation, the @CHOOSE() function takes two arguments: an offset value then a comma followed by a list of values or strings. The values or strings are also separated by commas. The first item in the list always has the offset value of 0, the second item has the offset value of 1, etc. The function returns the value of the string at the given offset position.