ユーザー定義関数を作成し、ワークシート上で利用できるようにします。Excel2000からWeekdayName関数が用意されていますが、それ以前のバージョンでも使えるように配列を利用して曜日を求めます。

Function DayName(YearValue As Integer, MonthValue As Integer, _
                               DayValue As Integer, StringType As Byte) As String
  Application.Volatile
  Dim Days_Jap
  Dim Days_Eng
  Dim WeekdayValue As Date
  Days_Jap = Array("日曜", "月曜", "火曜", "水曜", "木曜", "金曜", "土曜")
  Days_Eng = Array("Sunday", "Monday", "Tuesday", _
                                      "Wednesday", "Thursday", "Friday", "Saturday")
  WeekdayValue = Weekday(DateSerial(YearValue, MonthValue, DayValue)) - 1
  Select Case StringType
    Case 0
      DayName = Days_Jap(WeekdayValue)
    Case 1
      DayName = Days_Jap(WeekdayValue) & "日"
    Case 2
      DayName = Left(Days_Eng(WeekdayValue), 3)
    Case 3
      DayName = Days_Eng(WeekdayValue)
    Case Else
      DayName = ""
  End Select
End Function