Exceljet

Quick, clean, and to the point

Custom weekday abbreviation

Excel formula: Custom weekday abbreviation
Generic formula 
=CHOOSE(WEEKDAY(date),"S","M","T","W","T","F","S")
Explanation 

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")

How this formula works

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.