+ Reply to Thread
Results 1 to 9 of 9

Move Calendar Events

  1. #1
    Banned User!
    Join Date
    12-17-2020
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    229

    Move Calendar Events

    Hello,

    This workbook is thanks to Pete_UK that I am editing to better suit my needs. (Thank you Pete)

    The Calendar sheet has Holidays and Important Events (by formulas) in the cells next to the days on the Calendar sheet. The problem is that the Important Events are the ones showing and the Holidays don't show.

    Instead I would like the Holidays only to be next to the days and the Important Events to be listed in the area G41:H46 on the Calendar sheet showing the day and the event.

    The formulas in the Calendar sheet next to the days also should be updated to not include the Important Events.

    Thank you very much whenever someone has the chance to help.

    BB
    Attached Files Attached Files

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

    Re: Move Calendar Events

    You can change the formula in B12 to this:

    =IFERROR(VLOOKUP(DATE($N$40,$M$40,A12),'Todo-Holiday-Event'!$H$7:$I$26,2,FALSE),"")

    Then you can copy this to all the other cells which are next to the days of the month. It will only display the holidays.

    To get the list of Important Events (given that there might be a few of them in any given month), I first of all set up a helper column on the Events sheet, with this formula in N7 of that sheet:

    =IF(TEXT(L7,"myyyy")=Calendar!$M$40&Calendar!$N$40,MAX(N$6:N6)+1,"-")

    Copy this down as far as you need (e.g. to N26). Then you can use this formula in cell H41 of the Calendar sheet:

    =IFERROR(INDEX('Todo-Holiday-Event'!M:M,MATCH(ROWS($1:1),'Todo-Holiday-Event'!N:N,0)),"")

    which can be copied down to H47. I'm not sure what you wanted in column G of that sheet, as it isn't wide enough for the full date - perhaps just the day?

    Hope this helps.

    Pete

    By the way, I like what you've done to this calendar file.

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

    Re: Move Calendar Events

    On re-reading your post, I see that you want the day in column G, so put this formula in G41:

    =IFERROR(TEXT(INDEX('Todo-Holiday-Event'!L:L,MATCH(ROWS($1:1),'Todo-Holiday-Event'!N:N,0)),"d"),"")

    then copy down.

    Hope this helps.

    Pete

  4. #4
    Banned User!
    Join Date
    12-17-2020
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    229

    Smile Re: Move Calendar Events

    Perfect thanks so much Pete everything works great!

  5. #5
    Banned User!
    Join Date
    12-17-2020
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    229

    Re: Move Calendar Events

    Sorry I did see a small issue. On the Calendar sheet in the month of June Memorial Day shows in cell F40 but should not be. Can you take a look when you get a chance?

    Thank you
    Attached Files Attached Files

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

    Re: Move Calendar Events

    F40 should be empty (as well as E40), as you will never need more than 2 days in that final (6th) week of any month, and this only occurs if a 31-day month starts on a Saturday. You just copied the formula too far.

    Hope this helps.

    Pete

  7. #7
    Banned User!
    Join Date
    12-17-2020
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    229

    Re: Move Calendar Events

    Thanks again Pete I didn't notice that being too many days.

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

    Re: Move Calendar Events

    To explain why it happened, with E40 being empty the formula was effectively were looking up DATE(2021,6,0), which is interpreted by Excel as being the last day of the previous month, i.e. 31st May 2021.

    Hope this helps.

    Pete

  9. #9
    Banned User!
    Join Date
    12-17-2020
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    229

    Re: Move Calendar Events

    Yes for sure that does help. I appreciate the education. I especially like the use of helper cells to make the formulas less complicated.

    Cheers!

+ 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. Calendar with auto-populating events (multiple events in a single date)
    By zankzank in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-20-2021, 09:41 AM
  2. Events in Pivot Calendar
    By Rewmer in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 01-09-2021, 03:26 PM
  3. Autopopulate Events into Calendar
    By kebushong in forum Excel General
    Replies: 0
    Last Post: 08-31-2020, 12:34 PM
  4. [SOLVED] How to Add Events to Calendar
    By ynab in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-06-2020, 09:18 AM
  5. Replies: 4
    Last Post: 11-11-2019, 01:42 PM
  6. Repeat Events Calendar
    By zplugger in forum Excel General
    Replies: 6
    Last Post: 12-23-2014, 12:27 PM
  7. Add recurring events to an excel calendar
    By Kaitken123 in forum Excel General
    Replies: 0
    Last Post: 09-28-2014, 08:55 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