Lucy has attended:
Excel Intermediate course
Excel Advanced course
Calculating weekdays
Hi there
Had an excellent training session but unfortunately have forgotten one of the most important bits!
I have a column of dates and next to it a column with the corresponding day of the week.
I've been able to create a column that displays the weekday but it also has a strange date next to it (1900s) using this formula:
=WEEKDAY([@Date])
Can someone remind me of the formula I need to use to display a weekday in a separate column to the date please
Thanks
RE: Calculating weekdays
To return the weekday of any date in Excel we can use the WEEKDAY formula. The day is given as an whole number, ranging from 1 (Sunday) to 7 (Saturday), by default.
The formula syntax for the WEEKDAY formula is;
WEEKDAY(serial_number,return_type)
Serial_num is any valid date. See how Excel stores dates & times .
Return_type is a number (between 1 and 3) that determines the type of return value. 1, or omitted = 1 (Sunday) through 7 (Saturday). 2 = 1 (Monday) through 7 (Sunday). 3 = 0 (Monday) through 6 (Sunday).
An example of WEEKNUM formula is as shown below
=WEEKDAY(A1)/WEEKDAY(A1,1)
RE: Calculating weekdays
Hi
Thanks very much for replying. Using the above I can get the numerical value but I now need to turn this into the weekday in words, which I can't seem to do with the above information.
Are you able to help with this please?
(I should point out I am only a beginner/intermidiate user of Excel so if you could possibly explain it in laymen's terms that would be great!)
Many Thanks
RE: Calculating weekdays
I would do the following;
lets say you have 20/01/12 in A1 and you want "Friday" in B1. (is that what you want to do?)
in this case go to B1, enter the formula =A1. Then format B1 from the "Numbers tab" in "format cells" (right click to get the format cells option), choose custom and manually enter DDDD.
Let me know if you have any issues.
