Bespoke Microsoft Office Development 
 Consultancy|

Excel

|Word|PowerPoint|Outlook|VBA|Tools|Newsletter 
 Excel > Functions > Date and Time > WEEKDAY

 

WEEKDAY(serial_number, return_type)

 
 Returns the day of the week for a given date.

 serial_numberThe date as a serial number.
 return_typeThe number that specifies on what day the week starts:
1 = Sunday (1 to 7) (default)
2 = Monday (1 to 7)
3 = Monday (0 to 6)
11 = (Added in 2010) Monday (1 to 7)
12 = (Added in 2010) Tuesday (1 to 7)
13 = (Added in 2010) Wednesday (1 to 7)
14 = (Added in 2010) Thursday (1 to 7)
15 = (Added in 2010) Friday (1 to 7)
16 = (Added in 2010) Saturday (1 to 7)
17 = (Added in 2010) Sunday (1 to 7)

 REMARKS
 
  • The "serial_number" can be a date value, a serial number or a reference to a cell containing a date.
  •  
  • The "serial_number" cannot be a text string.
  •  
  • If "return_type" is left blank, the 1 is used.
  •  
  • If "return_type" = 1, then Sunday = 1, Monday = 2, Tuesday = 3, Wednesday = 4, Thursday = 5, Friday = 6 and Saturday = 7.
  •  
  • If "return_type" = 2, then Monday = 1, Tuesday = 2, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6 and Sunday = 7.
  •  
  • If "return_type" = 3, then Monday = 0, Tuesday = 1, Wednesday = 2, Thursday = 3, Friday = 4, Saturday = 5 and Sunday = 6.
  •  
  • In Excel 2010 the "return_type" numbers 11 to 17 were added.
  •  
  • If "return_type" = 11, then Monday = 1, Tuesday = 2, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6 and Sunday = 7.
  •  
  • Alternatively you could use a custom number format of "dddd" - Example 9.
  •  
  • You should try and enter your dates using the DATE function.
  •  
  • You can also use the TEXT function to convert a value to a specified number format.
  •  
  • You can also use the WEEKNUM function to return the week number in the year.
  •  
  • Example 1 - What day was the 1st July 1977. This returns 6 which represents Friday.
  •  
  • Example 2 - What day was the 1st July 1977 when submitted using the DATE function.
  •  
  • Example 3 - What day was the 1st July 1977 when the week starts on a Sunday. This returns 6 which represents Friday.
  •  
  • Example 4 - What day was the 1st July 1977 when the week starts on a Monday (starting at 1). This returns 5 which represents Friday.
  •  
  • Example 5 - What day was the 1st July 1977 when the week starts on a Monday (starting at 0). This returns 4 which represents Friday.
  •  
  • Example 6 -
  •  
  • Example 7 -
  •  
  • Example 8 -
  •  
  • Example 9 -
  •  
  • Eample 10 -

  •  EXAMPLES
     
     AB
    1=WEEKDAY(B1) = 601 July 1977 (Friday)
    2=WEEKDAY(DATE(1977,7,1)) = 602 July 1977 (Saturday)
    3=WEEKDAY(B1,1) = 603 July 1977 (Sunday)
    4=WEEKDAY(B1,2) = 5=NOW() = 31/08/2014
    5=WEEKDAY(B1,3) = 4=NOW() = 41883
    6=WEEKDAY(B2,2) = 6 
    7=WEEKDAY(DATE(1977,7,2),2) = 6 
    8=WEEKDAY(1/7/1977,2) = 6 
    9=WEEKDAY(NOW(),2) = 7 
    10=WEEKDAY(B4,2) = 7 
    11=WEEKDAY(38093,2) = 5 
    12=TEXT(WEEKDAY(DATE(1977,7,1)),"dddd") = Friday 
    13=TEXT(WEEKDAY(DATE(2004,7,1)),"dddd") = Thursday 
    14need to generate #NUM! 
     

     Functions - W | Index - W | Dates & Times | Office Online 2013 | 2010 | 2007 | 2003 

     © Better Solutions Limited 31-Aug-2014Top