+ Reply to Thread
Results 1 to 6 of 6

How to group a list of dates to corresponding a specific weekday

Hybrid View

  1. #1
    Registered User
    Join Date
    11-26-2023
    Location
    Luanda
    MS-Off Ver
    Microsoft Office Home & Business 2016
    Posts
    11

    How to group a list of dates to corresponding a specific weekday

    I am wondering if someone could help me with this issue?
    I would like to have a display of dates to corresponding a specific weekday (e.g. Mondays), as shown below:
    Book1.xlsx
    01/01/2023 7 Sunday Desire result
    02/01/2023 1 Monday 02/01/2023 Monday
    03/01/2023 2 Tuesday 09/01/2023 Monday
    04/01/2023 3 Wednesday 16/01/2023 Monday
    05/01/2023 4 Thursday 23/01/2023 Monday
    06/01/2023 5 Friday 30/01/2023 Monday
    07/01/2023 6 Saturday 06/02/2023 Monday
    08/01/2023 7 Sunday
    09/01/2023 1 Monday
    10/01/2023 2 Tuesday
    11/01/2023 3 Wednesday
    12/01/2023 4 Thursday
    13/01/2023 5 Friday
    14/01/2023 6 Saturday
    15/01/2023 7 Sunday

    PS, I ma using Microsoft office 2016.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.96 for Mac MS 365
    Posts
    8,690

    Re: How to group a list of dates to corresponding a specific weekday

    have you thought of just applying a filter to columns B through D then filter on the day you want?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.96 for Mac MS 365
    Posts
    8,690

    Re: How to group a list of dates to corresponding a specific weekday

    here is an alternative formula if you don't want to go with the filter
    =IFERROR(INDEX(B:B,SMALL(IF($D$3:$D$41="Monday",ROW(D$3:$D$41)),ROWS($D$3:D3))),"")
    it may be an array function (it isn't for me in my version) but if it doesn't work for you try activating it as an array so that {} appear on both ends, you do this by clicking on ctrl+shift+return at the same time while your cursor is in the function window. (You cannot add them, they have to appear when you press those three keys at the same time.)
    oh, and based on your location you might need to swap the commas for semicolons.

  4. #4
    Registered User
    Join Date
    11-26-2023
    Location
    Luanda
    MS-Off Ver
    Microsoft Office Home & Business 2016
    Posts
    11

    Re: How to group a list of dates to corresponding a specific weekday

    Hi Sam,
    Thank you for your swift reply.
    Indeed, I didn't want to go with the filter. You made a very good and helpful suggestion on how to deal with the issue. Your formula works perfectly. I'm really appreciative of the help you've given me.

  5. #5
    Registered User
    Join Date
    05-25-2012
    Location
    Eagle River, Alaska
    MS-Off Ver
    MS Office 365 (Excel ver. 2304)
    Posts
    17

    Re: How to group a list of dates to corresponding a specific weekday

    I do not see any correlation between the first list of sequential daily dates and the second list of weekly dates on Monday. You could just use flash-fill to generate a list of as many Mondays as you need.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    91,006

    Re: How to group a list of dates to corresponding a specific weekday

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Textjoin, Concat a list of emails if they are in a specific Group. For Office 2016
    By MinisoftEggshell in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-16-2022, 12:39 PM
  2. [SOLVED] Textjoin, Concat a list of emails if they are in a specific Group.
    By MinisoftEggshell in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-15-2022, 10:41 AM
  3. [SOLVED] Weekday counting from list of dates, ignoring non date cells
    By Badvgood in forum Excel General
    Replies: 5
    Last Post: 03-27-2020, 08:38 AM
  4. [SOLVED] check previous weekday date based on a specific dates
    By k1dr0ck in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-26-2018, 03:24 AM
  5. [SOLVED] Highlight Group of Dates if Dates Match List of other Dates
    By martinpgibson in forum Excel General
    Replies: 5
    Last Post: 10-24-2012, 08:14 PM
  6. Round Down Dates to Specific Weekday
    By torresk in forum Excel General
    Replies: 7
    Last Post: 05-05-2010, 02:10 PM
  7. [SOLVED] Updating Dates to Next Specific Weekday Automatically
    By elraver@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-10-2006, 07:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1