How to get only one particular Weekday in Excel

Last modified date

How to use Excel Weekday function

Click here to download the workbook which contains the below Example

Requirement

  • Want to see the list one particular WEEKDAY dates in a month. Suppose I want list dates which are Sunday in January month 2025 Year.
  • Option to change the WEEKDAY from Sunday to other days like Monday, Tuesday so on…
  • Option to give a specific month
  • Option to give a specific Year

How To create the Template for above requirement.

How to get one weekday dates list in Excel
How to get one weekday dates list in Excel

Below Excel Function used

  • DATE(year,month,day)
  • WEEKDAY(serial_number,[return_type])
  • IF(logical_test,[value_if_true], [value_if_false])
  • IFERROR(value,value_if_error)
  • VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Application of Functions

DATE Function: Reason for using DATE function is to calculate the first calendar DATE in the requested month. DATE Function uses the information updated by the user in cell C6 for Month and D6 for the year. The formula applied in cell B9.

Formula in B9=DATE(D6,C6,1) =>Result is 01/01/2025

WEEKDAY Function: The result of Date Function will be input to WEEKDAY function we can calculate weekday of First calendar DATE. Suppose we want to see Sunday dates and my first calendar DATE is Sunday then later dates I can simply add 7 days. If my First calendar date resulted in Monday then, I can add a few days. The Formula applied in Cell C9

Formula used C9=WEEKDAY(B9) => Result 4

VLOOKUP Function: Based upon the weekday name(Sunday) updated in Cell B6. Using VLOOKUP we get the weekday number from table updated in cells from F12 to G19 as per WEEKDAY function in return_type 1. The Formula applied in Cell D9.

Formula in Cell D9 =VLOOKUP(B6,$F$13:$G$19,2,FALSE) =>Result 1

In Cell “E9” will calculate the difference in requested weekday vs First calendar date. ( Requested Weekday No.(-) first calendar day Weekday No.)

Cell C9 =D9-C9 =>Result -3

IF Condition: Using IF condition in cell “B14” the first date of the requested weekday is calculated.

In Cell B14=IF(C9=D9,B9,IF(C9>D9,B9+(7-C9)+D9,B9+E9)) => 10/05/2025

If you look at the formula, first we are checking C9=D9 means if first calendar day is matching our request or not. if yes, will use it as the first date in our list, see B9 is updated after the comma.

If the First calendar is not matching with our request, then check whether weekday of the first calendar day is greater than our request (C9>D9).

Case 1: If Weekday of the first calendar is greater than our request,

  • Then we need to calculate the next weekend date, to get a fresh list of days from the beginning of the week.
  • In return_type1 it is Saturday because it is standing in 7th position.
  • Deduct first calendar date weekday number from 7 (7-C9) we should use brackets in this calculate first, if we miss using brackets then your result will be incorrect.
  • Then add the result to the first calendar date. The result will be Saturday date.
  • Now simply add the requested day weekday number which is in cell D9. Then we can get the first Sunday date as per the example.

Case 2: If Weekday of the first calendar is Less than our request,

  • Then simply add the difference between requested day and first
    calendar date, which is calculated in Cell E9=D9-C9.
  • As we know that the first calendar date is less than or request. we get the positive number in Cell E9.
  • Example: Suppose we want Friday dates to assume First calendar date is weekday number is 1 requested is 6 (return_type1). To get the first Friday date now simply add the 1+(6-1)=>1+5=6 see the result is 6 means 1st Friday date.

How to get remaining dates in the month

  • In Cells B15 to B19 simply add 7 days to the first requested date. For the Resulted date again add 7 more days until we get the complete list of dates for this requested month.
  • So to avoid seeing next month dates simply add IF condition refers to cell B15=IFERROR(IF(MONTH(B14+7)=$C$6,B14+7,“”),“”)
  • We are checking the month of the calculated date vs requested date in cell $C$6.
  • If the result is TRUE we can use resulted value. If it is not matching, resulting in a blank cell(” “).
  • We know that the number of weekdays may be different 4 or 5 days in a month. So, on a save side I have created the table for 6 days. IF we have less number of days will see the error in the formula, to avoid error in formula IFERROR is added to return an error into a blank cell(” “).

Now we are ready the template which will calculate and give the required weekdays list. Dynamically can be as per Weekday, Month and Year.

Leave a Reply

Your email address will not be published. Required fields are marked *

Post comment