Create a custom format for the cell as:
"dddd" => underlying value in the cell will still be the serial number of the date, but will display only as "Tuesday", etc.
or enter the following formula (assuming your date is in A1)
=weekday(A1,1) returns a number from 1 to 7 representing the day of the week. To convert the number to the name of the day (eg Tuesday) you'll need to create a lookup table of day numbers (say B1:B7) & names (C1:C7), then use