How to Convert Weekday to a Number

Sometimes you need to convert weekdays and weekends into number. Weekdays in Excel can be a date, also can be a text string.

1. When Weekdays are Dates (Please check here to see how to convert a date to weekday)

If weekdays are in the data formats, you refer use the WEEKDAY function to convert them into a number.

2. When weekdays are text strings

In most situations, weekdays are the text strings in Excel, so we cannot use the weekday function. However, we can use the Match function with the following formula


Where A2 is the first cell of the data range, and Monday will be 1 in the above formumla.

In case you want Sunday as the first day, you can change the weekday positions in the formula as below, and Sunday will be 1.

=MATCH(A2, {"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0)

If you work with Google Sheets, and you want to highlight weekdays, please see here for details.

Leave a Reply