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

[BarrieSax]

Firstly my thanks to Hangman for the assistance previously given with my attempts to create a monthly Calendar using Affinity Publisher Data Merge. Somehow I managed to succeed last year but I'm a year older and my attempts this year have resulted in utter disaster!
I am using two Data Merge Sources - one for the text ( a .csv file) and the other for the graphics ( an .XL file). I have established that the csv file is OK in that it consistently produces the correct configuration and that the XL file, used on its own, shows the graphics on separate pages. However, when I try to generate both on the same sheet, only the text shows correctly. The graphics go haywire! On occasion I have had the graphic for page 1 appearing correctly on page 1 but no other graphics anywhere. On other occasions the graphic for page 1 appears on an additional page - page 13! 
I have read through the "Data Merge Issue" as you suggested last year and have come to the conclusion that I have a layer problem. Consequently, I have tried moving the Picture Layer to every available place but nothing works. I just have no idea where next to turn, other than reverting to PagePlus - which I want to avoid, if possible.
Can I impose on your self yet again and ask for your assistance.

[wonderings]

What I have done in the past, doing a fair bit of calendars in print, is use online calendar generators that provide a PDF calendar for the year. I reuse these each. year for specific clients. So I just updated last years file with this years calendar. I also do this with the Calendar app in Mac OS, creating a PDF and edit accordingly. 

[Hangman]

Hi @BarrieSax,

2 hours ago, BarrieSax said:

I am using two Data Merge Sources - one for the text ( a .csv file) and the other for the graphics ( an .XL file). I have established that the csv file is OK in that it consistently produces the correct configuration and that the XL file, used on its own, shows the graphics on separate pages. However, when I try to generate both on the same sheet, only the text shows correctly. The graphics go haywire! On occasion I have had the graphic for page 1 appearing correctly on page 1 but no other graphics anywhere. On other occasions the graphic for page 1 appears on an additional page - page 13! 

You can only use one data source for Data Merge purposes so you would need to structure your data in a single .xlsx or .csv file using appropriately named headed columns, something along these lines depending on the data you wish to include...

2 hours ago, BarrieSax said:

Can I impose on your self yet again and ask for your assistance.

Could you perhaps upload a screenshot of one month (even mocked up manually) to show us the layout you're looking to achieve, we can then provide a much better idea of how to layout your Data file and Publisher file...

[kbaqel]

Hi, I'm new to data merge and I have some questions

1. If a month starts or ends in the middle of the week and I want those days from the previous or next month to be gray, do I need to duplicate those days in Sheets? Can't I just add them in the document so the records shift forward?

2. Why is the month and year not generated and only appears as an empty text box?

3. Can I set up formatting so that the color automatically changes to red for days with holidays?

I'm sorry if my questions are confusing, but I hope you understand what I mean.

Arkusz kalkulacyjny bez tytułu - Arkusz1(4).csv szablon.afpub

[EatMoreBacon]

3 hours ago, kbaqel said:

If a month starts or ends in the middle of the week and I want those days from the previous or next month to be gray, do I need to duplicate those days in Sheets?

Yes that has to be done in sheets

3 hours ago, kbaqel said:

Can I set up formatting so that the color automatically changes to red for days with holidays?

Has to be done in sheets

Take a look at:

There you will find templates for birthday calendars that range from very simple to extremely complex including red days for holidays, grey days for non-month days, moon phases, various emojis, pictures, etc, as well as options to have birthdays, anniversarys, etc be different colors.

[Hangman]

Hi @kbaqel,

See how you get on with the attached data files...

There's no real need to Data Merge the Days of the Week since these are fixed and could just be static text. I've left them as Data Merge fields in the attached file, so it's up to you whether you want to keep that field or make the Days of the Week static fields...

If you look at the .xlsx or .csv file to see how the data is structured you can then adapt the data as required, adding or correcting the holidays and adding URLs for the monthly photos...

If you also take a look at the Layer's Panel in Publisher to see how the days from the previous or next month are formatted to show as grey text and the holidays as red text and how this relates to the structure of the data file, hopefully, things will make sense. However, if you have any additional questions, feel free to ask.

Data Merged File

 
Sample Files

 Calendar 2025.zip

[kbaqel]

@Hangman Wow thank you very much, you just did what i've been trying to figure out throughout the whole day yesterday 🤦‍♀️Also thanks for looking up the holidays, that's very insightful of you 😄

[Hangman]

@kbaqel, That's no problem at all, I hope it helps...

Please do check the holidays as they may not all be 100% correct...

[Hilltop]

@Hangman Thanks so much for the 2025 calendar! I have a question. I noticed that the diacritics that you used in de .csv file carry over into the calendar after the data merge. However, I'm using Romanian diacritics but only some of them carry over. This happens when I use the font you used or even Arial. Any idea what's going on here?