+ Reply to Thread
Results 1 to 8 of 8

Week and Month Drop Down List Populates Dates

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007, Excel 2010, Excel 2013, Office 365
    Posts
    21

    Week and Month Drop Down List Populates Dates

    I am looking to populate a date list with either the Friday of each successive week or the last Friday of Month. Example is attached.

    The period is either Weekly or Monthly and is in cell J4. Cell H6 is the date above Week 1 and is input. The If statement formula for a Week selection is above Period 2 is noted below the table and starts in I6, Period 3 is J6 etc.

    Period Weekly

    1/30 2/6 2/13 2/20
    1 2 3 4


    This works for each successive Friday.
    =IF($J4="Weekly",H6+7,DATE(YEAR(A1),MONTH(A1)+1,1)- WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,2)))


    On a separate tab for experimental purposes, I have the following formula that works for the last Friday of each month. The table below, with dates in Col A shows the correct last Friday for that month in Col C.
    =DATE(YEAR(A8),MONTH(A8)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A8),MONTH(A8)+1,0),2)-2,-7)

    2/27/15 2/27/2015
    2/28/15 2/27/2015
    3/1/15 3/27/2015
    3/2/15 3/27/2015

    This is what should be the result for a Monthly selection.

    Period Monthly

    1/30 2/27 3/27 4/24
    1 2 3 4


    How do I combine these so that given Weekly or Monthly selection that the dates are populated correctly for either choice? Use of a function would also be fine.
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Week and Month Drop Down List Populates Dates

    In H6 Cell


    =IF($J$4="Weekly",$R$4+((COLUMNS($A:A)-1)*7),EDATE($R$4,COLUMNS($A:A)-1)-WEEKDAY(EDATE($R$4,COLUMNS($A:A)-1),15)+1)


    Drag it right...


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Week and Month Drop Down List Populates Dates

    =IF(J4="Monthly",EOMONTH($R4,COLUMNS($H8:H8)-1)-MOD(WEEKDAY(EOMONTH($R4,COLUMNS($H8:H8)-1))+1,7))
    Try this and copy towards right
    This works for only months
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Week and Month Drop Down List Populates Dates

    or
    =IF($J4="Weekly",$R4-MOD(WEEKDAY($R4)+1,7)+(COLUMNS($H8:H8)-1)*7,EOMONTH($R4,COLUMNS($H8:H8)-1)-MOD(WEEKDAY(EOMONTH($R4,COLUMNS($H8:H8)-1))+1,7))
    Try this

  5. #5
    Registered User
    Join Date
    04-04-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007, Excel 2010, Excel 2013, Office 365
    Posts
    21

    Re: Week and Month Drop Down List Populates Dates

    Thanks for the responses. Of the three, the first one comes the closest per the attached. However, it does not seem to properly show the last Friday of the Month if an earlier start date is shown in J4.
    Attached Files Attached Files

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Week and Month Drop Down List Populates Dates

    Corrections made and the Revised H6 Cell formula


    =IF($J$4="Weekly",($R$4-WEEKDAY($R$4,15)+1)+((COLUMNS($A:A)-1)*7),EOMONTH($R$4,COLUMNS($A:A)-1)-WEEKDAY(EOMONTH($R$4,COLUMNS($A:A)-1),15)+1)


    Drag it right...

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Week and Month Drop Down List Populates Dates

    H33=IF($J31="Weekly",$R31-MOD(WEEKDAY($R31)+1,7)+(COLUMNS($H31:H31)-1)*7,EOMONTH($R31,COLUMNS($H31:H31)-1)-MOD(WEEKDAY(EOMONTH($R31,COLUMNS($H31:H31)-1))+1,7))

    Try in H33 and copy towards right
    i found some wrong references in your present formula in H33

  8. #8
    Registered User
    Join Date
    04-04-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007, Excel 2010, Excel 2013, Office 365
    Posts
    21

    Re: Week and Month Drop Down List Populates Dates

    Thanks for the solutions proposed. Took me a while to respond since my hard drive crashed two Saturdays ago and my time has been spent in reconstruction.

+ 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] Formula/Macro to list down the dates that are inside a week number of a month
    By michaljireht in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-22-2014, 08:16 AM
  2. Replies: 4
    Last Post: 02-18-2014, 07:58 PM
  3. Replies: 7
    Last Post: 02-06-2014, 07:40 AM
  4. [SOLVED] Drop down list that populates field
    By her.rockstar in forum Excel General
    Replies: 13
    Last Post: 01-21-2013, 01:36 PM
  5. Replies: 11
    Last Post: 04-07-2009, 04:30 PM

Tags for this Thread

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