Suppose you want to return 1 = Sunday, 2 = Monday…..7 = Saturday
=TEXT(A1&"Jan2017","dddd")
To show only 3 characters of the Weekday Name
=TEXT(A1&"Jan2017","ddd")
You can add a number to A1 if you want to show some other Weekday Name
Say, if you want to show 1 = Monday, 2 = Tuesday…….7 = Sunday, just add 1 to A1
=TEXT(A1+1&"Jan2017","dddd")
Say, if you want to show 1 = Friday, 2 = Saturday…….7 = Thursday, just add 5 to A1
=TEXT(A1+5&"Jan2017","dddd")
Edit – 18-Aug-20
From Rick's comments
=TEXT(A1,"ddd") for 3 characters weekday name
=TEXT(A1,"dddd") for full weekday name
Actually, as long as cell A1 only contains the numbers 1 thru 7 and no other, there is a simpler formula available…
=TEXT(A1,"ddd")
or…
TEXT(A1,"dddd")
depending on whether you want an abbreviated day name or a full one. This works because Excel mistakenly thinks 1900 was a leap year, so while January 1, 1900 (date serial number offset 1) was a Monday, Excel thinks it was a Sunday.
Thank you Rick. I have updated the post as per your comment.