ddd. E.g., in Microsoft Excel 2013, if I have dates in column A displayed in mm/dd/yyyy format, but I also want the day of the week corresponding to those days to appear in column B as shown below.
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
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,"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.
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
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:
|1 or omitted||Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.|
|2||Numbers 1 (Monday) through 7 (Sunday).|
|3||Numbers 0 (Monday) through 6 (Sunday).|
|11||Numbers 1 (Monday) through 7 (Sunday).|
|12||Numbers 1 (Tuesday) through 7 (Monday).|
|13||Numbers 1 (Wednesday) through 7 (Tuesday).|
|14||Numbers 1 (Thursday) through 7 (Wednesday).|
|15||Numbers 1 (Friday) through 7 (Thursday).|
|16||Numbers 1 (Saturday) through 7 (Friday).|
|17||Numbers 1 (Sunday) through 7 (Saturday).|
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(index_num, value1, [value2], ...)
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
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
and A2 has the date 1/2/2017, I'd see
01 displayed, not "Su"
and the same
01 result if I used