Jump to content
You must now use your email address to sign in [click for more info] ×

A Calendar Template using data merge for custom holidays/birthdays (2022)


buddingphotographer

Recommended Posts

Hi everyone, 

I make a calendar every year for my large extended family with all the birthdays and anniversaries printed on their respective dates. I started this custom several years ago, and at that time used Microsoft Publisher 2007 to whip up a calendar template for the year, and then added text boxes with all the holidays and special dates. Needless to say, that was not a very efficient way of doing things... Every year I had to manually move every single text box to the new calendar and make sure they all were more or less lined up and evenly spaced. This year I suddenly realized that I had Affinity Publisher at my disposal! So I went searching about these forums to find out a better, more automated way to publish calendars. A few posts, such as this one on calendar automation, and this one on data merges gave me some guidance in the right direction, and eventually I figured out a way to make a calendar in AfPub using the power of data merge to do the heavy lifting of inputting the dates and the power of text styles to make it all look right. Needless to say, this will save me a lot of time in the future! 

I'm attaching an AfPub template and a sample .csv file. Anyone is welcome to use/customize these if they would like. I'm sure there are more efficient ways of doing things that I might have missed, so I'd appreciate any constructive feedback you might have to offer! Enjoy! 

If you have questions about how (or why) I did something the way I did, feel free to ask! Downloading the samples and running the data merge correctly should give you results that look like this: 

Jan_1.thumb.jpg.68fa9a9fcd4cdb32bd53c06595e259c4.jpg

Other resources that were useful:

2022-afpub-calendar.csv 2022-afpub-calendar-template.afpub

 

For God loved the [whole] world so much that he gave his one and only Son,
so that everyone who believes in him will not perish but have eternal life.

 

 

 

 

Link to comment
Share on other sites

  • 9 months later...

This calendar uses Data Merge in Publisher. Download both files. Go to Document > Data Merge Manager, then hit Generate. This will populate the calendar with the information from the csv file. (NB – You can edit the information in the csv file with your own info.) It will probably help if you check out the resources mentioned in the original post!)

If you want a calendar for 2023, you will need to edit the dates in the csv file. eg: The first of January in 2022 fell on Saturday, in 2023 it will fall on Sunday; so all the dates need to move down by one row in the csv file. Then put the events etc you want inserted in the correct row for the date.

Acer XC-895 : Core i5-10400 Hexa-core 2.90 GHz :  32GB RAM : Intel UHD Graphics 630 : Windows 10 Home
Affinity Publisher 2 : Affinity Photo 2 : Affinity Designer 2 : (latest release versions) on desktop and iPad

Link to comment
Share on other sites

First of all, thank you for sharing this calendar template. I have been putting together photo calendars for our cottage association since 2006. I am now looking to put it together using Affinity Publisher. I knew there had to be a more sophisticated way of doing this, as you have shown. It will take time to follow the instructions, but my concern is printing the calendar. I cannot seem to print 2 sides landscape in horizontal mode. I am not sure what my document size should be or the paper size. ultimately, I would like to have the finished calendar about 11" x 17" folded to 8.5" x 11" or thereabouts, Thank you, again.

Uta

 

Link to comment
Share on other sites

On 10/13/2022 at 11:43 AM, Fuchsia said:

First of all, thank you for sharing this calendar template. I have been putting together photo calendars for our cottage association since 2006. I am now looking to put it together using Affinity Publisher. I knew there had to be a more sophisticated way of doing this, as you have shown. It will take time to follow the instructions, but my concern is printing the calendar. I cannot seem to print 2 sides landscape in horizontal mode. I am not sure what my document size should be or the paper size. ultimately, I would like to have the finished calendar about 11" x 17" folded to 8.5" x 11" or thereabouts, Thank you, again.

Uta

 

You're welcome, @Fuchsia

The calendar is currently set up for letter size paper (8.5x11). If you want to print 2 on a 11x17 page , then I'd suggest exporting the entire calendar from AfPub as a PDF file, and then use your print driver to set up printing "multiple on a sheet" or however your software does it. If you want to include the photos on the top, then... it gets a bit more tricky. :) I printed on regular 8.5x11 paper and then did a cheap coil binding. But if you want to do it all on 11x17, then I'd suggest making a new AfPub document at 11x17, and then drag-n-drop your exported PDF calendar file into the 11x17 doc. Make 12 pages, set the PDF page number for each one, and then add a picture frame on the top half. (See attachment). Keep in mind that if you're printing double-sided and folding that the photos will have to be on different pages than you might expect. It's actually kinda complicated to figure out, but I'm sure you can do it with some time and patience. (Look at another calendar to see what picture has to go with which month in order for it to come out right once it's all folded). 

Actually, Adobe Acrobat Reader (PDF software) has a "booklet" printing mode that might work for automatic collation. But in that case you'd need to export your photos as 8.5x11 PDFs mixed in between the month pages. (You can use PDFtk to mix/merge PDF files before printing).

Screenshot 2022-10-15 120618.jpg

Screenshot 2022-10-15 121624.jpg

 

For God loved the [whole] world so much that he gave his one and only Son,
so that everyone who believes in him will not perish but have eternal life.

 

 

 

 

Link to comment
Share on other sites

  • 4 weeks later...

@buddingphotographer, thanks for these resources. I also do family calendars as well as some for a few churches, and manually entered the info the first time. i thought there had to be an easier route and was researching it. Here's another simple method i discovered- 1. Create a table with the table tool in Publisher. 2. Go to the paragraph panel and get it to use numbering bullets. they promptly fill in your boxes with the correct numbers on the correct days and you can customize the settings as far as where the info gets placed, fonts, etc as desired.😊 I am delighted!

Link to comment
Share on other sites

On 11/9/2022 at 9:41 PM, Chul said:

@buddingphotographer, thanks for these resources. I also do family calendars as well as some for a few churches, and manually entered the info the first time. i thought there had to be an easier route and was researching it. Here's another simple method i discovered- 1. Create a table with the table tool in Publisher. 2. Go to the paragraph panel and get it to use numbering bullets. they promptly fill in your boxes with the correct numbers on the correct days and you can customize the settings as far as where the info gets placed, fonts, etc as desired.😊 I am delighted!

You're very welcome, so glad you found them useful @Chul! Thanks for the tip; I'm about to start working on a 2023 calendar, so I'll definitely check it out. 

On 11/10/2022 at 3:53 PM, StudioJason said:

Very nice resources, thanks for sharing!

It's my pleasure, @StudioJason!

 

For God loved the [whole] world so much that he gave his one and only Son,
so that everyone who believes in him will not perish but have eternal life.

 

 

 

 

Link to comment
Share on other sites

Just did a slight revamp to the Calendar originally created…for including images.

All you have to do is swap out the Master (Calendar) with Master B - Image.

With the new Autoflow…should be able to easily place multiple images at once, on several Pages.

Modifications:

Document Setup - 2page - Facing 11 x 17

Added — Master B - Image 

Added — Bleeds (for images) 3mm (.118in)

 

2022-afpub-calendar-template.afpub

Link to comment
Share on other sites

  • 4 weeks later...

Thanks to all for the valuable efforts. I am an old Turkish man, 66, and having difficulty to customize the files. I would like to change the month and day names to Turkish and make the first day of the week Monday. Could anybody help me?

Month names:

January= Ocak, February= Şubat, March= Mart, April= Nisan, May= Mayıs, June= Haziran, July= Temmuz, August= Ağustos, September= Eylül, November= Kasım, December= Aralık

Day names:

Monday= Pazartesi, Tuesday= Salı, Wednesday= Çarşamba, Thursday= Perşembe, Friday= Cuma, Saturday= Cumartesi, Sunday= Pazar

I need a Turkish dictionary also and I would like to change the color and thickness of the borders.

Thanks in advance, I will very much appreciate your help. 

 

Edited by Architector
Link to comment
Share on other sites

  • 11 months later...

I've followed the instruction and advice and created a calendar for 2024 which is spot on except one odd issue. If I preview the first row I see the month appear where it previously showed <MONTH>. As I expected. But when I tap 'generate' where the preview showed January, I see February instead, but I see the dates and holidays for January. The month title is out of step all the way down until December where nothing is showing bar the year (<MONTH> 2024).

Is this operator error? Or a bug?

Link to comment
Share on other sites

I haven't used Data Merge for a while, but I did create a calendar way back, using this template and csv file, with no problem. I very much doubt that there is a bug, it's much more likely that the csv file hasn't been edited correctly.

Acer XC-895 : Core i5-10400 Hexa-core 2.90 GHz :  32GB RAM : Intel UHD Graphics 630 : Windows 10 Home
Affinity Publisher 2 : Affinity Photo 2 : Affinity Designer 2 : (latest release versions) on desktop and iPad

Link to comment
Share on other sites

  • 3 weeks later...

I am a young 84-year old who, despite being no expert, still exercises his little grey cells by programming his PC, and I read AdrainLambert's comment on @buddingphotographer's calendar with interest because it has some similarity to my own experience. I have produced a macro enabled Excel file which automatically produces the necessary csv file (including the Public Holidays) for producing a monthly calendar for any given year. I am pleased to report that it appears to work very well. However, when I add the extra data to produce a suitable image for each page I get a strange problem. Whilst using the preview button on the Data Merge Manager the images (and all the other data - Birthdays, Holidays, Etc.) show up correctly, the generated copy had February's image on the January page, March image on February page, etc., and the December page had no image at all. I assumed this was caused by some fault in the amended csv file and I have tried every variation i can think of but so far, nothing has had any effect on the output. The images are still appearing on the wrong page even though everything else is correctly placed, and after weeks of experimentation,  I am beginning to suspect there is a bug somewhere. Either that or I am getting past it!

Link to comment
Share on other sites

Hi @BarrieSax and a very warm welcome to the forums,

I very much suspect the issue is simply a layer order problem... Take a read of this thread to see if you are able to fix the problem by re-ordering the layers in your layers panel as shown here...

If not, feel free to upload your Excel or CSV file and we can take a look for you... 🙂

Affinity Designer 2.3.1.2217 | Affinity Photo 2.3.1.2217 | Affinity Publisher 2.3.1.2217
Affinity Designer Beta 2.4.0.2294 | Affinity Photo Beta 2.4.0.2294 | Affinity Publisher Beta 2.4.0.2294

Affinity Designer 1.7.3 | Affinity Photo 1.7.3 | Affinity Publisher 1.10.8
MacBook Pro 16GB, macOS Monterey 12.6.8

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.