+ Reply to Thread
Results 1 to 32 of 32

Auto-Populate Calendar Dates

  1. #1
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    10

    Auto-Populate Calendar Dates

    Hey there everyone,

    For my work, we are trying to make a calendar to send out to clients every month. Every calendar will be different for each client, and we would have a new calendar each month. I have found online a file I can download that is an excel file that has a calendar for each month of the year with the days in their respective spots. However, when 2013 comes and there's no download like this one, we have to make a new calendar each month. So what we want to do is make one calendar, or more, and have the days automatically fill in when the month is over/starting. I have tried the whole "put A1+1 in B1 and that will change the date". And that does work, until the end of the month comes around. Is there any type of coding or formula that could take care of this for me? I am moderately comfortable with Excel but certainly not a professional so easy to understand answers are appreciated.

    Thanks guys and I hope someone can help

  2. #2
    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: Auto-Populate Calendar Dates

    See my post #10 in this thread:

    http://www.excelforum.com/excel-prog...html?p=2768117

    for a universal calendar which is formula-based - just select the month and year of interest using the drop-downs on the Calendar sheet.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Auto-Populate Calendar Dates

    Pete,

    That looks great thank you very much. Just a question about it. There is a lot of formula in each box, obviously there needs to be for something like this. Am I still able to write in the day boxes or would I need to print them out before and hand write on them? I would try this myself but I don't want to mess up the format haha. This is extremely helpful though and greatly appreciated.

    Thanks again

    AJ

  4. #4
    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: Auto-Populate Calendar Dates

    Well, the original of this calendar sheet was designed to take the list of activities and place them in the appropriate cells of the calendar sheet, but if you don't want to have that facility you can delete most of the formulae (i.e. those that relate to the activities, on the rows marked 1 to 7). The other formulae are there to generate the calendar itself, so you shouldn't mess with those.

    You could then just type the entries in the blank areas before sending out to your clients.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Auto-Populate Calendar Dates

    Pete,

    I am not interested in the tab for the visits. So for the line of formula that reads: =IF(ISNA(MATCH(G20&"_"&$A25,Visits!$A:$A,0)),"",INDEX(Visits!$B:$B,MATCH(G20&"_"&$A25,Visits!$A:$A,0)))
    What part of that refers to the visits page? What can be deleted from that to only leave the calendar with the drop down menu? Also, what we would like to do with this calendar is to have a box of information and data on the side, which we can make, but we would need formula for that so that when we enter in data, such as when a tv commercial will air, it will place it on the calendar. For instance, if we have commercials running from May 1 through May 7, and we put that information in the side box, how does that get automatically placed in the calendar on the appropriate days?

    Thank you again. What you have provided is already invaluable. I'm sure this can be done too, I'm just not sure at all how to do it.

    AJ

  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: Auto-Populate Calendar Dates

    I've attached the original of this file which I first put together about 5 years ago for a poster on the old Newsgroups, as I think this is quite relevant to what you say you want. With this you can list any activity or event in the Events sheet. You can record these in any order, and I've put some data in which might be applicable to one of your clients, i.e dates when commercials are scheduled to run - I've set most of them for July 2012. On the Calendar sheet you can see that these have been placed automatically on the correct day, and the file is designed to allow up to 7 events per day to be shown.

    It strikes me from what you've said above that you could use a file like this for each of your clients and record the appropriate events - you don't need to remove earlier events as when you choose a new month from the calendar only those events will be shown.

    As this file is older, it does not include all the conditional formatting that the later file had, to remove the boxes for non-existent dates.

    Hope this helps.

    Pete
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Auto-Populate Calendar Dates

    This is absolutely perfect. Thanks so much for your help. I will reply again if I have any further questions.

  8. #8
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Auto-Populate Calendar Dates

    Okay, I have another question. This is great, first of all. But is there any way to input a range of dates such as July 4 - July 27 with a certain channel and have the information copy over to every day included in the range? Or would we have to put in the information separately for each day?

    Thanks again,

    AJ

  9. #9
    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: Auto-Populate Calendar Dates

    Well, the answers are No and Yes - you can't put in a range of dates as you have to put in the information separately for each day. However, this is easy to accomplish - enter the first date (let's say into B10) and then in B11 you can just put the formula =B10+1 and copy it down as far as you need to. Put the activity description into C10, and then copy that down as far as you need to. If the description changes slightly on one day (eg a different time), then just amend that entry.

    Hope this helps.

    Pete

  10. #10
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Auto-Populate Calendar Dates

    This does help. The thing is when I put that formula in to get the next date placed into the following cell, wouldn't that just repeat the same date? For instance, to go with the example you said in the post above, if in B11 I enter =B10+1 and copy it down, won't B11, B12, B13, etc...all be the same date?

  11. #11
    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: Auto-Populate Calendar Dates

    No, in the first case =B10+1 means add one day onto the date in B10. Then when it is copied down it becomes:

    =B11+1,
    =B12+1,
    =B13+1,

    and so on, which basically means add one day onto the previous date. Try it and see.

    Pete

  12. #12
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Auto-Populate Calendar Dates

    Got it. Thanks a ton!

    AJ

  13. #13
    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: Auto-Populate Calendar Dates

    Glad to be able to help.

    Please mark the thread as solved if you think it is.

    Pete

  14. #14
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Auto-Populate Calendar Dates

    I didn't mark it solved because I wasn't sure if I would need any other assistance which it turns out I do need.


    Pete,

    I have a few more questions. First, is it possible to get the events tab with the calendar formatting put with the calendar that has the conditional formatting? Just to make the calendar look a little more professional. Also, I copied the formula in column A down to 5,000. Is there any way to put more than 7 lines in each day on the calendar?

    Again thank you for your help

  15. #15
    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: Auto-Populate Calendar Dates

    Well, the answer is yes to all those questions. I agree that it looks more professional to remove the unwanted lines, but it took me quite a while to set up all those CF conditions in the other file so I don't fancy doing it again. It might be easier for you to start with the other file and remove the extra columns for each day and see if you can arrive at the same thing with just one column per day. As regards having more that 7 lines per day, this will distort what the calendar looks like, so you might have to arrange the printout to be in portrait rather than landscape. It's not so easy to insert a few extra rows per week and copy formulae down, as the formulae look at C2 (rather than C$2), and so the date reference cell will change when you copy it down - again it can be done, but it will take some time to edit each formula. How many rows per day were you thinking of?

    Hope this helps.

    Pete

  16. #16
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Auto-Populate Calendar Dates

    We can use the other calendar it's fine. Just wanted to see if it was possible and not too difficult but I can understand that it took a while and not wanting to do it again. We were thinking about more like 10 lines per day. I had a feeling it would mess up the formatting that's why I asked you before I went ahead to try it. Is there any way you can do that?

    As reparation, my company would like to send you a gift card for all the help you have given me and the group I am a part of. Is there a store in your area that you enjoy that we can purchase a gift card from to send to you? All this help is enormously appreciated.

  17. #17
    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: Auto-Populate Calendar Dates

    If you use the other calendar, then rather than deleting the unwanted columns, perhaps you can just hide them instead (and on the Activities sheet) - that seems like a simpler solution.

    As regards payment, all contributors to this Forum do so for free, so no payment is expected for the advice and help you've received so far.

    Pete

  18. #18
    Registered User
    Join Date
    07-12-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Auto-Populate Calendar Dates

    I am using the calendar that doesn't have all the CF. How do you recommend inserting 3 more rows as easily as possible?

  19. #19
    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: Auto-Populate Calendar Dates

    I would suggest that you insert the new rows between those marked 6 and 7 in column A, and then copy the formulae down from the row marked 6 into the new rows. Each cell of this block of 3 rows will need to be amended so that the formula is looking at the row containing the actual date (i.e. row 2 for the first block). You will also need to ensure that the sequence is 6, 7, 8, 9, 10 for the cells in column A.

    You will then need to repeat this for each of the 5 other weeks in the calendar sheet - time-consuming and tedious, but not too difficult.

    Hope this helps.

    Pete

  20. #20
    Registered User
    Join Date
    10-24-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Auto-Populate Calendar Dates

    This is brilliant! I've been looking for something exactly like this. Thank you so much!

  21. #21
    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: Auto-Populate Calendar Dates

    Glad I helped you, K2. In future if you come across other posts that you find helpful, click on the "star" icon in the bottom left corner to pass on your thanks more directly.

    Pete

  22. #22
    Registered User
    Join Date
    10-24-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Auto-Populate Calendar Dates

    Good to know, ta.

  23. #23
    Registered User
    Join Date
    07-14-2013
    Location
    Detroit, USA
    MS-Off Ver
    Excel 2013
    Posts
    1

    Re: Auto-Populate Calendar Dates

    @ Pete_UK

    Your calendar at POST #10 http://www.excelforum.com/excel-prog...html?p=2768117 is excellent! Is there a newer version? Are the 2 columns to the right with yellow header free-form or are they set up to be used programtically? Thanks for all of your contributions in this thread

  24. #24
    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: Auto-Populate Calendar Dates

    The format of the calendar generated in that thread was defined by the OP (Nancy) - presumably those yellow-headed columns were to be used as a manual tick-list following each visit that was made.

    The OP in this post persisted via PM and agreement was reached where I would develop the calendar further and he would send me a voucher. I never got paid for the extra work !!

    So, I don't mind posting it here to show how it has moved on. The calendar now accommodates up to 12 events per day, and conditional formatting removes the lines around non-existent boxes. Also, for 30-day months which begin on a Saturday and 31-day months which begin on either Friday or Saturday, the "orphan" days which would occur in a sixth week are actually shown on the first row of the calendar, so now it only occupies a maximum of 5 weeks (choose December 2012 to see the effect). The OP wanted this to support his invoices to clients, so there is a summary table above the calendar in which he could manually record the costs of advertising in various media for that client for that month. Above that is his logo and some general headers, but all of these could be deleted if you just wanted a standard calendar. To accommodate these extra rows, though, the calendar orientation is now portrait when printed.

    Hope you can make use of it - I'm sure the OP has done for the last year (not that I'm bitter !!).

    Pete
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    01-03-2014
    Location
    New Orleans, LA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Auto-Populate Calendar Dates

    Hi Pete,
    Sorry to bump an old thread, but I have a follow up question to build on this capability.

    How can we make the calendar view pull from multiple date columns? i.e. if an activity (column B) happens on multiple dates, which are listed in subsequent columns, how can we get that additional activity to appear in the calendar view?

    I've attached an example based on your work that illustrates my question. Right now, the calendar view pulls from the "Date 1" column. What if I wanted it to pull ALSO from Date 2, Date 3 etc.?

    My application won't allow Date 1, Date 2...etc to be combined into a single column. Otherwise, I could just make a long list of activities and dates.

    Thanks for your contributions! Your prior work on this subject has been an incredible help so far!

    -ArlynP
    Attached Files Attached Files

  26. #26
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Auto-Populate Calendar Dates

    ArePl,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  27. #27
    Registered User
    Join Date
    12-05-2017
    Location
    New York, NY
    MS-Off Ver
    MS Office 10
    Posts
    6

    Re: Auto-Populate Calendar Dates

    I need a "for dummies" version of this. I really don't know anything about formulas so I have no clue how to update this to 2017/2018 and forward, but the basic outline of this template is exactly what I'm looking for. How do I update that?

  28. #28
    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: Auto-Populate Calendar Dates

    The year is selected in the drop-down on cell K4 of the calendar sheet, so select that cell then click on Data | Data Validation (twice), and you will see in the Source box a list of years separated by commas. Delete the years you don't want and put your years in there, each separated by a comma, then click OK.

    Hope this helps.

    Pete

  29. #29
    Registered User
    Join Date
    12-05-2017
    Location
    New York, NY
    MS-Off Ver
    MS Office 10
    Posts
    6

    Re: Auto-Populate Calendar Dates

    Awesome! Thank you!

  30. #30
    Registered User
    Join Date
    12-05-2017
    Location
    New York, NY
    MS-Off Ver
    MS Office 10
    Posts
    6

    Re: Auto-Populate Calendar Dates

    Sorry, one more question - Is there a way to have the rows automatically resize to fit the text? I have set everything to wrap text and can of course manually do that for the worksheet, but is there a way to force it to always automatically size the cells to show the full content?
    Last edited by dijkdes; 12-05-2017 at 02:50 PM.

  31. #31
    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: Auto-Populate Calendar Dates

    When I first set it up it was specifically designed to print out a full-year on a single page in landscape mode, bearing in mind that a 30-day month starting on a Saturday or a 31-day month starting on either Friday or Saturday will require 6 weeks to show the calendar in full - that's how I ended up with up to seven entries for each day. If you were to adjust the row heights to accommodate long text entries, then it will mess up what that looks like on screen (or on the printed page).

    Over the years, though, I have made some amendments in line with what other posters have asked for. If Sunday and Saturday entries are not important to you, for example, then you can narrow those columns and widen the other days, so that you would have more space to display the entries. If you do not need to have up to 7 entries per day, then you could delete the final few rows for each week. You could also arrange for the printout to appear in Portrait mode.

    On Format Cells | Alignment, you can choose Shrink to fit, so that the font for each displayed entry will automatically adjust to suit the size on screen (although this may make the content unreadable if it is a very long text item.

    You can also set it up to Wrap text, in which case long text entries will wrap onto multiple lines, but you may need to adjust the row heights manually as Excel doesn't always get this right, and this may make the display look awkward if, say, Wednesday's entry for a particular row needs multiple lines but the other days do not.

    Hope this gives you some ideas.

    Pete

  32. #32
    Registered User
    Join Date
    12-05-2017
    Location
    New York, NY
    MS-Off Ver
    MS Office 10
    Posts
    6

    Re: Auto-Populate Calendar Dates

    Super helpful. Thanks Pete!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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