Here, we will learn how to use the Excel date functions: WEEKDAY and WEEKNUM. Later, we will also learn how to create weekday abbreviation.
Excel WEEKDAY function
The Excel WEEKDAY function returns an integer between 1-7 representing the day of week for the given date. By default, WEEKDAY returns 1 for a Sunday date and 7 for Saturday. The formula in B4, copied down, is:
=WEEKDAY(A4)
Excel WEEKNUM function
The Excel WEEKNUM function returns an integer between 1-54 representing the week number of year for the given date. WEEKNUM returns 1 starting 1 January. By default, weeks start on Sunday. The formula in E4, copied down, is:
=WEEKNUM(D4)
Creating weekday abbreviation using WEEKDAY with CHOOSE
To create a weekday abbreviation, use the formula based on the WEEKDAY and CHOOSE functions.
The dataset (shown in the image) contains some dates in the "Date" header column. The goal is to find the week day from each date and create an abbreviation for each week day. The formula in B12, copied down, is:
=CHOOSE(WEEKDAY(A12),"S","M","T","W","T","F","S")
How this formula works
WEEKDAY finds the week day from the date 1-Jan-25, that is Wednesday and so returns 4. This number goes to the index_num argument of CHOOSE. Since the text "W" is at the fourth position in the list of abbreviations, CHOOSE returns "W" as the final output.
Tip: You can use the Evaluate Formula dialog box (Formulas > Evaluate formula) to watch how the formula works.
You have successfully learnt how to use the Excel date functions: WEEKDAY and WEEKNUM. You have also learnt how to create weekday abbreviation! I hope this post helped you.
