Day of the week for dates in Excel and Calc

In cell A2, I have 1/1/2017 and in cell A3 I have the formula =A2+1. I then highlight that cell by clicking in it and copy it downwards to subsequent cells by holding the left mouse button down while dragging down through subsequent cells to highlight them. I can then hit Ctrl-D to copy the formula downwards. I can then copy the contents of column A to column B then highlight all of the date cells in column B and then right click and choose Format. In the Format Cells window that appears, I can select Custom for Category and type ddd in the Type field and then click on OK to have all of the dates displayed as 3-character days of the week, i.e., Sun, Mon, Tue, Wed, Thu, Fri, Sat.

If, instead, I wanted the days of the week to be displayed spelled out to their full length, I could use four d's in the Type field, rather than ddd. You don't have to type in the number of d's corresponding to Wednesday, which has nine D's; four will suffice.

Note: if you are using OpenOffice Calc, rather than Microsoft Excel, when you right-click and choose Format Cells, you need to select User-defined for the Category and type ddd in the Format code field, if you want 3-letter abbreviations for the days of the week, and then click on OK.

If you are using Excel and think someone else may be opening the Excel spreadsheet with Calc, don't use this format method, since the person opening the spreadsheet with Calc will see the date displayed as the date, e.g. 1/1/2017, not the day of the week as you intend. So use one of the other two methods listed below, instead, if someone using Calc may need to use the workbook with a sheet with days of the week. Though when I tested the reverse process by saving a Microsoft Excel 97,2000, XP (.xlsx) file with the user-defined ddd format for dates from OpenOffice Calc 4.1.1 and then opened that file with Excel 2013, the days of the week were displayed as I expected.

If you use this method with Excel or Calc, you don't change the date value, only how it is formatted for display. Another mentod to obtain the day of the week for a given date is to use the TEXT function, e.g. TEXT(A2,"ddd") or TEXT(A2,"dddd"), if you prefer the day of the week to be spelled out completely.

This method will convert the date to text, so you can't convert it back into the original date. I.e., if I put the formula =B2 in cell C2 and then format C2 as date, I won't get a date in C2, but will simply have the text in B2 copied to C2.

A third method for obtaining the day of the week that matches a specific date is to use the choose function in the formula, e.g. =CHOOSE(WEEKDAY(A2),"Sun","Mon","Tue","Wed","Thu","Fri","Sat").

Or, you could spell out the complete day of the week, e.g., Sunday, Monday, etc., instead of the abbreviated Sun, Mon, etc. Or, if you preferred to use only two-character dates, you could use =CHOOSE(WEEKDAY(A2),"Su","Mo","Tu","We","Th","Fr","Sa").

The WEEKDAY function returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default. The syntax for the command is shown below:

The reason the first parameter for the function is a serial_number is that Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. So when you are storing values in cells as dates, they are actually serial numbers. The optional return type values are listed below:

In this case, since I'm using the default value and not specifying the optional "return type", if January 1 is a Sunday, the formula =WEEKDAY("1/1/2017") (put the date in quotes) returns "1".

The CHOOSE function has the following syntax:

Choose uses index_num to return a value from the list of value arguments. You can use CHOOSE to select one of up to 254 values based on the index number. If value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index_num. Putting the WEEKDAY function within the CHOOSE function as =CHOOSE(WEEKDAY(A2),"Sun","Mon","Tue","Wed","Thu","Fri","Sat") matches the number 1 with Sun, 2 with Mon, 3 with Tue, etc.

Though using CHOOSE as the solution for the date to day of the week conversion may seem more cumbersome than the other options, it can provide more flexibility in the values returned for a date. E.g., you can have it return custom values of your choice, fully spelled out days of the week or any number of letters for the abbreviation. E.g, if I tried dd for the format for a two-letter abbreviation in cell B2, if B2 has the formula =A2 and A2 has the date 1/2/2017, I'd see 01 displayed, not "Su" and the same 01 result if I used =TEXT(A2,"dd").