Creating a Fantasy Calendar

Copper Contributor
I am working on a story based in a fantasy world and want to make a calendar for it in Excel (there is time travel and different eras in the world so I think having the months/days/years would be great for continuity). I am looking for a way to either create a new “date()” function or a simpler way to make the calendar than having to type out each month/week and copy paste till I get 10 years down the calendar. I most commonly use Excel on Mac, but I can get access to a Windows version if that makes it easier. I appreciate any advice that anyone can give me!
27 Replies


Download a calendar template:

Browse through the calendar templates (or create one), choose an Excel calendar template that is best for you. Click the Download button on the template page, open the template file in Excel, and then edit and save your calendar.

Note: By default, a template file download goes into the Downloads folder on your computer. You may want to copy the template file to a different location so that you can easily find it later.


Hoffe das ich Ihnen mit diesen Informationen weiterhelfen konnte.



I know I don't know anything (Socrates)

But will that make it so I can customize how many days are in a month? Because the fantasy world has varied days in a month compared to the Gregorian calendar.

@ChristianHauer wrote "But will that make it so I can customize how many days are in a month? Because the fantasy world has varied days in a month compared to the Gregorian calendar."


Ahh, Narnia meets Lord of the Rings meets Wrinkle in Time 


May I suggest you spell out a bit more fully how much the days in those fantasy months might vary. Are there twelve fantasy months, more or less, or does that number itself vary. Do the number of days in Januly vary each time Januly rolls around? Or is it just that Januly has 15 days whereas Febember has 44?


And so forth?


I think you're going to have to create a new function, which can be done in Mac or in Windows, but you need to spell the situation out a bit more before anybody can help.

There are 14 months (365/366 days in a year), the days per month are as follows: 26, 26, 22, 26, 27, 26, 30, 26, 25, 26, 26 (leap year 27 [every four years]), 26, 26, 27.



Well, here's a start. I'll keep noodling on this to see if I can turn it into something more functional. And let's invite others to do the same.


How do you plan to use this calendar IN your fantasy world? This includes such things as "Are there seven day weeks?" "Do the days of the week have names?"  Or some other span called a week? And so forth..... All of those kinds of questions would be important to think through in order to start using that matrix meaningfully.

The weeks are seven days long. I do have names for the individual days/months. However, I wasn’t sure that was important for the function. If we can use the place holders you came up with for now, @mathetes, that would be nice.
But HOW are you going to be using these in the course of the narrative?
Will a character be saying "I'll meet you three Xdays from now" and you'll need to know that will be the nth of Decidate, for example. Or that it'll be the fifth of Zunicorn?
And do you really need a Leap Year? That adds a wrinkle in time (not quite a tesseract) where I wonder if it is crucial to the story. I'm sure it can be accommodated if saving the universe depends on it......
I'm not at all sure I can resolve these, although it's fun fantasizing, but I do know that HOW you'll use your calendar will be important in the shaping of any calendar functions.
Yes, there will be references to what day/date it is and potentially how far it is from another day/date. The leap year is important for inner story, but not obvious, reasons also.



OK, Trusted and Respected Contributors, here's a challenge along the lines of the ones that @Twifoo has issued in the past.


Please refer to the full thread above.


Lets see if we can come up with a reasonable approach to the request that @ChristianHauer has issued: creating a workable fantasy calendar, 14 months to the year, months of varying lengths as specified in the table created above.


I'm assuming that we'll do what the "real calendar" does, begin with a day zero and that have each day increment that by 1, so that date math can easily be done. But how to set it up so Christian, in his fantasy story, can do such things as:

  • name two dates in his fictional/fantasy calendar and know how big the gap is between them, or
  • set a deadline of 2 1/2 of his fictional months from "now", and know what that deadline is, or
  • any/all of the other things we do with calendars?

@Sergei Baklan @mtarler @NikolinoDE @Peter Bartholomew @Hans Vogelaar @Riny_van_Eekelen 


@mathetes Contrary to @Twifoo 's challenges, this one has no clear description up front nor does it tell exactly what the required end result should be. Too vague for me and too much guess work involved. As you know I'm an accountant. Fantasy is not my strongest point. I'm gonna let this one go.

@Riny_van_Eekelen Your reaction is entirely understandable. 


@ChristianHauer : let Riny's response challenge you to give a more clear description of what you need to be able to do with this fantasy calendar beyond what has been said above.


In the meantime, I'm hopeful that at least one of the people I addressed in my last post may have an intimate understanding of how the "normal" calendar in Excel works, and how something like array math might be able to at least let you do basic calendar math (adding and subtracting dates and intervals). I'm still working on that myself, having a greater interest in fantasy literature than my accountant friend.

Sorry for the confusion. My pitfall has been that I thought it would be straight forward to describe what I need, but it’s not. I don’t know how I got that idea, it’s time travel for crying out loud. So I’ve tried to rethink how to explain what I’m looking for.

I am currently writing a fantasy story that takes place in another world. The current story has time travel, the characters travel back in time 200 years. To keep continuity between the dates of when they left and when they arrive in the past I want to make a calendar where it can format the days of the month to match the year selected. Just as one might for a regular calendar. So if I choose to look at the months of the year 2034, I’d just have to type in that year, and the calendar would arrange to that year’s set up. Then if I needed that date to correlate with something back in 1834, then I could type in that year and see that year’s date arrangement. Factoring in leap years of course. Having similar attributes to using the date() or month() functions in Excel to make a dynamic calendar.

Let me know if more information is needed.

Thank you!

I'm just realizing, based on what you wrote this last time, @ChristianHauer , that it sounds like your time travel is between THIS world (and THIS calendar) and THAT fantasy world and THAT fantasy calendar. Is that correct?


So this leads to some more questions. I don't know if you're aware that behind dates in Excel is a serial number. The number for today, 1/4/22 is 44,565. Day one, in Excel's universe (which I suppose qualifies as a fantasy world of its own), is Jan 1, 1900. Days before that would carry a negative number, but I think in reality (in Excel reality) they get tricky. So the questions:

  1. ARE your characters going back in time from our Julian calendar to your fantasy calendar, trips representing 200 years of tessering? (I assume you're familiar with L'Engle's classic and the very real phenomenon of the tesseract)
  2. Or are they in the fantasy world the whole time, so they go back from (Fantasy year) 4325 to the year 4125?
  3. If it's just the latter, that they're always in your Fantasy universe, then could you orient us to what the "current year" is, and we'll at least know to start year zero back four thousand fantasy years (or whatever) for the sake of "fantasy realism." 

And any other info that would help us picture/envision that fantasy universe will be, well, helpful.

Oh no, it’s not that complex, it’s just within the one fantasy world. Let’s use the year 1770 as a starting point (that’s when the technology starts to work successfully in their world), using the first month, first day from the table you constructed. The story itself takes place between their year 2068 (picture slightly more futuristic present day) and 1867 (Post US civil war/Prussian-German hybrid). Thank you so much for your help and patience!



Just wanted to drop a line saying I'm having fun with this, even making some progress. It'll likely take another couple of days, however, because I do have a life in the real world. 


By the way, have you read Time and Again


What little you've said of your story reminds me of this classic by Jack Finney. Weaves real history in with sci-fi with illustrations from actual news magazines of the past.... 

I’m glad you’re having fun! I was worried I was being too unclear and vague that it was annoying. I really appreciate all your help and your questions! And I’m sorry if I seem cagey about the story itself, we are in the last phases of development before my sister begins to illustrate for it so I’m trying to not get too many details out yet.

I know how that is! I have a job working sheet metal so take all the time you need.

I’ll have to look into “Time and Again” I have not heard of that! It sounds interesting.
so i just read this thread and what I think you are looking for is a way to either:
a) give a year, month, day (in the fictitious calendar) and find out what day of the week it is, and
b) given 2 dates know how many days happened between them (in that fictitious calendar)
I think all you need is a look-up that defines every day as what day # it is in the year (and adj for leap yrs). So given 2 "dates" you use that look up for each then convert them to our calendar and use the date functions in Excel. So for example if they travelled back in time from 2068-14-20 (day 359 in that year) back to 1968-01-22 (day 22) then you use excel
=DAYS( DATE(2068,1,1) + 359 , DATE(1968,1,1) + 22 )
But wouldn’t that show me the figures for the Julian calendar? I would like to have the figures for the fantasy calendar. Also, my preference would be if I could have all 14 months of a year displayed in block format on a worksheet, like we might have in a “planner” document for Excel with our 12 month calendar. And then be able to change what year I’m looking at by typing in a new one. I don’t know how feasible that is, but that’s what my ultimate goal would be.

I hope that makes sense. Thanks for your insights!
so I was trying to suggest if you have a lookup to go from day-of-year (i.e. 1-365/366) to fantasy date and back. Since both calendars are based on same #days/year (inc leap years) then you convert it to our calendar, use excel function, then convert it back to fantasy calendar all based on day#. I think it should work. My only concern is the days between the 2 leap days in leap years but like I said you need to account for that in the lookup table.
As for creating a 14 month planner that is done using various techniques. Depending on the technique used may depend on how you would adapt it for your fantasy calendar..
1 best response

Accepted Solutions
best response confirmed by ChristianHauer (Copper Contributor)

@ChristianHauer see if the attached does everything you want.  I locked the sheet except the 2 green cells where you enter the start & end dates but there is no password if you want to unlock it.

View solution in original post