+ Reply to Thread
Results 1 to 3 of 3

Repeating Dates in a column via Array

  1. #1
    Registered User
    Join Date
    08-15-2019
    Location
    CT, USA
    MS-Off Ver
    Excel 2016
    Posts
    11

    Repeating Dates in a column via Array

    Good day Excel geniuses!

    I'm attempting to create a weekly savings planner using a few pirated elements from other excel stuff I've found on the web. As you can see in col. J&K and P&Q you have the week number and a corresponding date. In this case the dates are the Thursdays within each month (huzzah payday!). The intent/end goal here is to input the year into N1 and T1, which would then flow down into the K and Q columns and change them according to the year, listing out the Thursdays ONLY.

    To the right of the columns you can see an example of what I would like the aforementioned columns to do (sorta).

    What I understand:
    'CalendarYear1' or 'CalendarYear2' link back to the date in N1 or T1 respectively (right now the example is set to T2) and their subsequent names in the Name Box.
    I understand that arrays need Ctrl+Shift+Enter to implement.

    The formula in the example on the dates (array):
    =DaysAndWeeks+DATE(CalendarYear2,1,1)

    Breakdown of said forumla as I understand it:
    =: duh. It's the start of the formula
    DaysAndWeeks: a defined name?
    +Date: addition of the date function as excel interprets the numbers (I understand how excel counts up from 1/1/1900)
    (CalendarYear2: Start of the DATE formula, looks at the cell with the applied Name of CalendarYear2.
    1,1): End of the formula that displays Year and Month (or Month and Day, but I get what it's kicking out)


    Million dollar Question:
    1) How do I adjust the formula/array to only yield the Thursdays found in each month?


    I've had a lot of luck in the past with my GoogleFu, but this question is fairly specific and I'm not sure how to approach it. I did find a function in my search of =SEQUENCE, however that seems only available to Excel 365 and I am running Excel 2013.
    Attached Files Attached Files
    Last edited by NikkuSan7; 08-17-2020 at 10:42 AM.

  2. #2
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Repeating Dates in a column via Array

    For your first set, put this in cell K3...

    =DATE(N1,1,8)-WEEKDAY(DATE(N1,1,3))

    then put this in cell K4 and copy it down...

    =K3+7

    So the same for the other set except use T1 in place of N1 in the first formula

  3. #3
    Registered User
    Join Date
    08-15-2019
    Location
    CT, USA
    MS-Off Ver
    Excel 2016
    Posts
    11

    Re: Repeating Dates in a column via Array

    That works too and is a lot simpler! Thanks!!

+ 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. Calculating Max and Minimum Dates from a Column Array
    By bernhardjoshuabudion in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-28-2016, 10:37 PM
  2. [SOLVED] Distribute given numbers in an array without repeating
    By safetboy in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-08-2016, 05:49 PM
  3. [SOLVED] Difficulties with repeating dates and numbers when extending column with formula
    By AtriosM3 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2013, 01:36 PM
  4. [SOLVED] Count number of days from column of repeating dates
    By Edwardbigfoot in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-28-2013, 04:22 PM
  5. Sort Array Of Dates Into Single Column
    By Eddiegnz1 in forum Excel General
    Replies: 3
    Last Post: 06-26-2012, 10:20 PM
  6. Excel 2007 : Repeating an Array
    By Ryan M in forum Excel General
    Replies: 6
    Last Post: 05-10-2011, 02:12 PM
  7. Trying to create repeating dates and then sort column by date
    By lizabright in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-10-2005, 09:06 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