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

[buddingphotographer]

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: 

Other resources that were useful:

• Ubuntu Font: https://design.ubuntu.com/font/
• Affinity Publisher Data Merge Tutorial: https://www.youtube.com/watch?v=aU98GmaQsFQ
• Deep Dive Data Merge in Affinity Publisher: https://www.youtube.com/watch?v=wav8wuucZUY

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

[Alex_V]

Thanks for sharing this. Not sure if I'm missing something though but upon opening in Publisher the calendar is not showing correctly as shown on the screenshot. Are there any other settings I have to change?

[PaulEC]

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.

[Fuchsia]

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

 

[buddingphotographer]

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).

[Chul]

@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!

[StudioJason]

Very nice resources, thanks for sharing!

[buddingphotographer]

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!

[StudioJason]

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

[Architector]

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 December 8, 2022 by Architector

[Architector]

OK I solved my problem. Thanks for all the tips.

[adrianlambert]

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?

[PaulEC]

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.

[EatMoreBacon]

On 11/20/2023 at 6:28 PM, adrianlambert said:

Is this operator error? Or a bug?

Check your data merge layout settings. i suspect there are two data merges. One is going to need a -(number) in the offset to merge properly.

 

Also check out this option...

 

[BarrieSax]

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!

[Hangman]

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... 🙂