Results 1 to 3 of 3

Thread: Calendar dates not populating correctly

  1. #1
    Junior Member
    Join Date
    Oct 2013
    Posts
    4
    Rep Power
    0

    Calendar dates not populating correctly

    I have a calendar with 2 rows in between every row of dates for notes.
    The first row of dates are correct, but the following 5 rows are all repeat of the first row.
    I need help to modify the formula for the rest of the dates to populate correctly.

    Thank you
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    The formula you are using is an array formula. The reason why it was repeating the same dates for each row was because of the transposed numeric array {0;1;2;3;4;5} that was meant for each row.

    So in your case, you should split this formula in to 6 individual rows, each with seperate row index, with the first index starting with 0.

    =IF(MONTH(DATE(YEAR(I1),MONTH(I1),1))<>MONTH(DATE( YEAR(I1),MONTH(I1),1)-(WEEKDAY(DATE(YEAR(I1),MONTH(I1),1))-1)+{0}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(I1),MONTH(I1),1)-(WEEKDAY(DATE(YEAR(I1),MONTH(I1),1))-1)+{0}*7+{1,2,3,4,5,6,7}-1)
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Oct 2013
    Posts
    4
    Rep Power
    0
    Oh man, thank you very much.
    you are a genius Excel Fox....

Similar Threads

  1. Insert Missing Dates By Comparing Two Lists Of Dates
    By mahmoud-lee in forum Excel Help
    Replies: 24
    Last Post: 10-16-2013, 04:48 PM
  2. List of Dates In DropDown Starting From Today()
    By rich_cirillo in forum Excel Help
    Replies: 5
    Last Post: 04-10-2013, 05:58 PM
  3. How to Extracting dates and days between 2 dates.
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 9
    Last Post: 08-11-2012, 09:11 PM
  4. The Number of Years, Months and Days Between Two Dates
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 7
    Last Post: 06-08-2012, 10:35 PM
  5. Visual Plotter basis given dates and activity
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 2
    Last Post: 03-07-2012, 02:37 PM

Posting Permissions

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