Summary

To create a custom weekday abbreviation, you can use a formula based on the CHOOSE and WEEKDAY functions. With this approach, you can generate a custom one-letter abbreviation, two-letter abbreviation, or any weekday that makes sense in your particular situation.

In the example shown, the formula in C5 is:

=CHOOSE(WEEKDAY(B5),"S","M","T","W","T","F","S")

Generic formula

=CHOOSE(WEEKDAY(date),"S","M","T","W","T","F","S")

Explanation 

Working from the inside-out, the WEEKDAY function takes a date and returns a number between 1 and 7. With default settings, the number 1 corresponds to Sunday and the number 7 corresponds to Saturday.

The CHOOSE function simply maps numbers to values. The first argument is the number to map, and subsequent arguments represent associated values.

In this case, 7 values have been provided in the order required to work with WEEKDAY's Sunday through Saturday scheme.

With a date from column B, WEEKDAY returns a number which is fed to the CHOOSE function. CHOOSE returns the value at that position in the list of abbreviations.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.