Excel uses a number from 1 to 7 to refer to the days of the week. The WEEKDAY function returns the number based on a date. Excel doesn’t a have a function to return the day number based on a text day like Monday. Time for a custom function.
The standard WEEKDAY numbering starts with Sunday equaling 1 and Saturday equals 7. I will use that to create a custom function that takes a text day like Monday or Tuesday or Mon and Tue and converts it into the WEEKDAY number.
The solution isn’t what I call elegant, but it works.
The formula uses the SWITCH function, which you can use to build a lookup into your function. This makes it a standalone solution.
If you haven’t worked with the LAMBDA function, check out the blog post below which explains how to create custom functions using LAMBDA.
The LAMBDA formula to test in a cell is.
=LAMBDA(ref,SWITCH(LEFT(ref,3),"sun",1,"mon",2,"tue",3,"wed",4,"thu",5,"fri",6,"sat",7,0))(A2)
We extract the first three characters using the LEFT function and use that to apply a day number with the SWITCH function. The ,0 on the end of SWITCH function is returned if the text is not matched to the seven entries listed.
This means we can use either Saturday or Sat. The case doesn’t matter either. It also means that we may get a few false positives for words like wedding or sunny.
We can create the custom function. I use the prefix of fn for all my custom functions. this differentiates my functions from Excel’s.
We can then use the custom function.
Here is another way to write this LAMBDA…
=LAMBDA(ref,IFERROR(XMATCH(LEFT(ref,3),TEXT(SEQUENCE(7),”ddd”)),0))
Cool – you have taken that SEQUENCE idea and run with it.