+ Reply to Thread
Results 1 to 8 of 8

Need a formula to show dates in their correct monthly column based on multiple cells data

  1. #1
    Registered User
    Join Date
    06-13-2012
    Location
    Leicester, England
    MS-Off Ver
    MS Office 365
    Posts
    61

    Need a formula to show dates in their correct monthly column based on multiple cells data

    Hi All

    I'm looking plan out my mouths for the year, but I can't seem to figure out how to make the dates automatically fall into their correct columns.

    I used a formula to show how many visits I need to complete, and that works, but of course, it just drops them in sequence. I've attached a example or what I have so far, I've tried several formula's but can't get my head around it.

    So in a nutshell, I want the outputs to be:

    Every 8 weeks should fall into an every other month from the initial visit date, then but only show the number of visits that I add, so if I have 6 listed, there should be 7 total dates, including the initial date which is always manual.

    I hope this makes sense.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Need a formula to show dates in their correct monthly column based on multiple cells d

    Hi,
    what are the results you are expecting to see and in which cells?
    can you add them manually to your file?

  3. #3
    Registered User
    Join Date
    06-13-2012
    Location
    Leicester, England
    MS-Off Ver
    MS Office 365
    Posts
    61

    Re: Need a formula to show dates in their correct monthly column based on multiple cells d

    Hi, This is the output I would like.

    So it only needs to show for the year, Jan to Dec. But I want to be able to add my initial visit date, then based on the frequency I need to go, and the number of visits I need, I want it to show in each month so I can easily plan the year better.

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Need a formula to show dates in their correct monthly column based on multiple cells d

    Hi,
    I don't quite understand your table.
    taking for example SW1V 4JB
    if the initial visit was on 01-Jan and the frequency is every 2 months then why is the next visit on 26-Feb-23? shouldn't it be on 01-Mar ?

    Is the initial date for all incidents is 01-Jan? or should I ignore column E?

  5. #5
    Registered User
    Join Date
    06-13-2012
    Location
    Leicester, England
    MS-Off Ver
    MS Office 365
    Posts
    61

    Re: Need a formula to show dates in their correct monthly column based on multiple cells d

    Well technically speaking, 8 weeks is 56 days, so the date in Feb is correct, and all the initial dates are just examples for now, there was no need to add the actual dates until I could get the function I wanted to work.
    So basically I just need it to show in the correct column for each month depending on what the months come up as after the 56 day count. Once it works, if it works, then column E will be the starting point for all of the visits, depending on when we have been asked to go OR when we are able to.

    I hope that clears up any confusion

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Need a formula to show dates in their correct monthly column based on multiple cells d

    You can use this formula in cell F2:

    =IF(COUNTIF($E2:E2,"<>")<=$C2,IF(TEXT($E2+$B2*7*COUNTIF($E2:E2,"<>"),"mmmm")=F$1,$E2+$B2*7*COUNTIF($E2:E2,"<>"),""),"")

    Ensure that the cell is formatted as a date in the style you prefer, then copy across and down as required.

    Hope this helps.

    Pete

  7. #7
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Need a formula to show dates in their correct monthly column based on multiple cells d

    Hi,
    I formatted your titles as dates to be able to use them for calculation,
    and used the Edate with few conditions

    F2 and across:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-13-2012
    Location
    Leicester, England
    MS-Off Ver
    MS Office 365
    Posts
    61

    Re: Need a formula to show dates in their correct monthly column based on multiple cells d

    Quote Originally Posted by Pete_UK View Post
    You can use this formula in cell F2:

    =IF(COUNTIF($E2:E2,"<>")<=$C2,IF(TEXT($E2+$B2*7*COUNTIF($E2:E2,"<>"),"mmmm")=F$1,$E2+$B2*7*COUNTIF($E2:E2,"<>"),""),"")

    Ensure that the cell is formatted as a date in the style you prefer, then copy across and down as required.

    Hope this helps.

    Pete
    Hi Pete

    This one has not worked, sorry for the late reply, I'm just getting back into this, I input the formula and nothing is showing up in any cells. I have it formatted into date and I have it looking att the dates in the headers.

    Not sure if I may have selected something incorrectly?

    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. [SOLVED] How to hide/show cells in a range based on multiple values in a column.
    By Hellion in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-30-2021, 01:01 PM
  2. Replies: 2
    Last Post: 11-20-2015, 12:42 PM
  3. [SOLVED] Formula to count based on dates and text in multiple cells - See Spreadsheet
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-16-2013, 11:40 AM
  4. How to get and automatically updated monthly average based on data from specific dates.
    By daveneedshelp123 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-04-2013, 03:41 AM
  5. Need formula help with automatic monthly column coloring based on flexible dates
    By excellearning in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2013, 04:27 AM
  6. Replies: 5
    Last Post: 09-24-2012, 05:14 PM
  7. consolidate monthly worksheet data into correct month's column on master sheet
    By Royzer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2012, 10:41 AM

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