r/excel • u/SuperSpirals • 8h ago

unsolved I have two Fantasy calendars that I am attempting to build a date converter for in a spreadsheet (I.E. "It is X day and time on Planet A, so it is Y day and time on Planet B") What functions can I use to accomplish this?

I have 2 fantasy planets, each with their own calendar:

Planet A has a calendar that is 128 days long. Each day is 38 hours in length.

  • Month1 is 32 days long
  • Month2 is 32 days long
  • Month3 is 32 days long
  • Month4 is 32 days long

Planet B has a calendar that is 365 days long. Each day is 24 hours long.

  • Intercalary Holiday 1
  • Month1 is 30 days long
  • Month2 is 30 days long
  • Month3 is 30 days long
  • Intercalary Holiday 2
  • Month4 is 30 days long
  • Month5 is 30 days long
  • Month6 is 30 days long
  • Intercalary Holiday 3
  • Month7 is 30 days long
  • Month8 is 30 days long
  • Intercalary Holiday 4
  • Month9 is 30 days long
  • Intercalary Holiday 5
  • Month10 is 30 days long
  • Month11 is 30 days long
  • Month12 is 30 days long

So I am trying to build a spreadsheet that can translate between the two by converting Planet A's date into hours, and then using that number to calculate Planet B's date using a common zero point(Year 0, Month 0, Day 0, Hour 0 being the same point in time on both calendars).

I have been told the MOD function can partially do this, dividing a number into equal sections and spitting out the leftovers, however this will not work for Planet B's calendar with the intercalary days between some of the months. What is the best way to build this out in a spreadsheet? Is there a way to convert a number into a month name (Example in earth terms: inputting 1488 hours, it spits out March 3rd, knowing January has 31 days, February has 28 days, and now currently in March with 3 days worth of hours)

7 Upvotes

100% Upvoted

26 comments sorted by

•

u/AutoModerator 8h ago

/u/SuperSpirals - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

3

u/doshka 2 8h ago

Please confirm that an hour on planet A is the same length as an hour on planet B.

What is the format for a date on each planet? Do the months on have names? If so, do they have abbreviations?

2

u/SuperSpirals 7h ago edited 7h ago

Yes, an Hour is my universal constant when designing these calendars, so they are the same on each planet.

Both Planets would use the same format - Hour:minute on Day:Month:Year
For intercalary days, it would just appear as Hour:Minute on IntercalaryHoliday:Year

The months (and intercalary holidays) do have names!

Risra - First Day of Spring

Waking Moon

Singing Moon

Blooming Moon

Hyra - First Day of Summer

Sister's Moon

Scorching Moon

Storming Moon

Falra - First Day of Fall

Harvest Moon

Crossing Moon

Crossroads - Day of the Dead

Sleeping Moon

Lora - First Day of Winter

Shivering Moon

Dreaming Moon

Melting Moon

EDIT: Here are planet A's month names as well
Summerforth
Springforth
Harvestforth
Winterforth

2

u/excelevator 3067 8h ago

How does the Intercalary Holiday relate to your question ?

1

u/SuperSpirals 7h ago

The intercalary holidays are days that happen between months, so a formula that converts a number of hours into a date would have to take that into account.

2

u/excelevator 3067 5h ago

this should be mentioned clearly in your post, along with updates of any other details you missed from questions presented so everyone does not have to read all the comments for all the details.

2

u/PaulieThePolarBear 1918 8h ago

Your post doesn't explicitly say this, but is an hour on Planet A and Planet B the same length of unit time?

Clearly state how dates and times are expressed on planet A and planet B with particular regard as to when it is in an intercalary day on Planet B.

2

u/SuperSpirals 7h ago

Yes, I used hours as a universal constant when building the calendars.

On both planets, the time and date would be read Hour:Minute on the Day:Month:Year

For the intercalary days, it is Hour:Minute on IntercalaryHoliday:Year, as they are not considered part of a month.

So using Earth calendar as an example, lets say New Years Day is an intercalary holiday.
So the first day of the year would be New Years Day, 2026, and the second day of the year would be 1st of January, 2026.

2

u/PaulieThePolarBear 1918 7h ago

On both planets, the time and date would be read Hour:Minute on the Day:Month:Year

Where did minutes come from? These are not mentioned once in your post. So are below EXACT examples of how a date-time would appear

 23:00 on the 12:3:2
 12:00 on the 31:2:7

For the intercalary days, it is Hour:Minute on IntercalaryHoliday:Year,

So, no spaces between intercalary and holiday and no holiday number? Give actual examples of how this should appear.

2

u/SuperSpirals 7h ago

Sorry, I should have just left minutes out for simplicity. They dont really matter. But yes, this is how they would be read.
I have noted the names of the months in another comment. The Intercalary holidays are named Risra, Hyra, Falra, Crossroads, and Lora

An example of an intercalary date reading would be 12oclock on Risra:2026

1

u/PaulieThePolarBear 1918 7h ago

Is your ask solely to convert a Planet A date/time to a Planet B date/time? If not, very clearly state your requirements.

How should midnight be expressed in your date/time system? For example, midnight between September 1st and 2nd can be expressed as September 1st 2026 24:00 or September 2nd 2026 00:00. What is your rule?

1

u/SuperSpirals 7h ago

My intention is to be able to convert both directions, apologies for the lapse in clarity.

midnight would be expressed as 00:00 on the new day.

2

u/PaulieThePolarBear 1918 7h ago

K, I want to circle on the question around the date format you are using as rereading your previous answer, I can see some ambiguity. While there is a mathematical element to your question, there is also a text manipulation part of your question. As such, the format of your data is important to know. Give 5 actual examples of EXACTLY how an INPUT date will appear in your spreadsheet. 3 of these examples should be "month" days and 2 should be "intercalary" days. Unless each planet has it's own rules around date formats, it shouldn't matter which planet months you use for your examples. As I noted earlier, this should be EXACTLY as your data will appear with all "filler" words, etc.

1

u/SuperSpirals 6h ago

Can do! I am also assuming you want me to use the month and intercalary names as I have expressed in another comment in this thread.

Example 1 (Planet A): 12:00 on 4 Summerforth, 1600
Example 2 (Planet B): 23:00 on 28 Storming Moon, 4042
Example 3 (Planet A): 31:00 on 16 Harvestforth, 1413
Example 4 (Planet B intercalary): 10:00 on Risra, 4043
Example 5 (Planet B intercalary): 13:00 on Crossroads, 4020

2

u/PaulieThePolarBear 1918 5h ago

Okay. I think I have something working. This requires Excel 2024, Excel 365, or Excel online. I'll reply across 2 comments to be able to include images in each comment.

Step 1 is to create a lookup table for the calendar for each planet.

Anything with orange background is an input, anything with a grey background is a formula.

So, you would need to enter the number of hours in a day in each planet as well as the calendar (names of months and number of days in month). The final formula column calculates the number of hours that will have elapsed at the very end of that month on a year to date basis.

I'll copy the formulas from cells L3 and Q3 respectively below should you need them, but they are relatively simple.

=SUM(K$3:K3)*$L$1
=SUM(P$3:P3)*$Q$1

You would copy these formulas down to all rows of each table as I have shown

1

u/PaulieThePolarBear 1918 5h ago

With a Planet A date and time in cell A2 formatted EXACTLY as you indicated in your previous comment, the below formula will return the date and time on Planet B

=LET(
inputCell, A2,
inputTable, $J$3:$L$6,
inputHours, $L$1,
outputTable, $O$3:$Q$19,
outputHours, $Q$1,
yearHours, TEXTAFTER(inputCell, ",") *MAX(CHOOSECOLS(inputTable,3)),
monthInfo, XLOOKUP(TEXTAFTER(TEXTBEFORE(inputCell, ",")," ",3),CHOOSECOLS(inputTable,1),inputTable),
dayMonthHours, INDEX(monthInfo,3)-(INDEX(monthInfo, 2)-INDEX(TEXTSPLIT(inputCell, " "),3)+1)*inputHours,
totalInputHours, TEXTBEFORE(inputCell, ":")+yearHours+dayMonthHours,
outputYear, QUOTIENT(totalInputHours, MAX(CHOOSECOLS(outputTable,3))),
outputHoursRemain, MOD(totalInputHours, MAX(CHOOSECOLS(outputTable,3))),
outputMonthInfo, XLOOKUP(outputHoursRemain, CHOOSECOLS(outputTable,3)-(CHOOSECOLS(outputTable,2)*outputHours), HSTACK(outputTable,CHOOSECOLS(outputTable,3)-(CHOOSECOLS(outputTable,2)*outputHours)),,-1),
outputDayNumber, QUOTIENT(outputHoursRemain - INDEX(outputMonthInfo, 4),outputHours)+1,
outputDayHours,  MOD(outputHoursRemain - INDEX(outputMonthInfo, 4),outputHours),
finalOutput, outputDayHours&":00 on "&IF(INDEX(outputMonthInfo, 2)=1, "", outputDayNumber&" ")&INDEX(outputMonthInfo, 1)&", "&outputYear,
finalOutput)

With a Planet B date and time in cell cell A9 formatted EXACTLY as you indicated in your previous comment, the below formula will return the date and time on Planet A.

=LET(
inputCell, A9,
inputTable, $O$3:$Q$19,
inputHours, $Q$1,
outputTable, $J$3:$L$6,
outputHours, $L$1,
isInputHolidayMonth, NOT(ISNUMBER(--INDEX(TEXTSPLIT(inputCell, " "),3))),
yearHours, TEXTAFTER(inputCell, ",") *MAX(CHOOSECOLS(inputTable,3)),
monthInfo, XLOOKUP(TEXTAFTER(TEXTBEFORE(inputCell, ",")," ",3-isInputHolidayMonth),CHOOSECOLS(inputTable,1),inputTable),
dayMonthHours, INDEX(monthInfo,3)-(INDEX(monthInfo, 2)-IF(isInputHolidayMonth, 1,INDEX(TEXTSPLIT(inputCell, " "),3))+1)*inputHours,
totalInputHours, TEXTBEFORE(inputCell, ":")+yearHours+dayMonthHours,
outputYear, QUOTIENT(totalInputHours, MAX(CHOOSECOLS(outputTable,3))),
outputHoursRemain, MOD(totalInputHours, MAX(CHOOSECOLS(outputTable,3))),
outputMonthInfo, XLOOKUP(outputHoursRemain, CHOOSECOLS(outputTable,3)-(CHOOSECOLS(outputTable,2)*outputHours), HSTACK(outputTable,CHOOSECOLS(outputTable,3)-(CHOOSECOLS(outputTable,2)*outputHours)),,-1),
outputDayNumber, QUOTIENT(outputHoursRemain - INDEX(outputMonthInfo, 4),outputHours)+1,
outputDayHours,  MOD(outputHoursRemain - INDEX(outputMonthInfo, 4),outputHours),
finalOutput, outputDayHours&":00 on "&IF(INDEX(outputMonthInfo, 2)=1, "", outputDayNumber&" ")&INDEX(outputMonthInfo, 1)&", "&outputYear,
finalOutput)

See example below. Column A is my input, column B is the output using the applicable formula from above, and column C is the alternative formula to convert output back to input as a check.

This is not a simple formula, so feel free to ask any questions.

1

u/Electronic_Chair6383 8h ago

Dude how did you think of that second planet!?

2

u/SuperSpirals 8h ago

Well, planet B is where my main story of my D&D campaign setting takes place, and I wanted it fairly similar to Earth.
Planet A (...and C, D, E, F, and G) all have longer or shorter years based on their distance from the sun. From there, i mostly just built the calendars on a whim and a little bit of logical thinking. Planet A is a short year because its closest to the sun, and Planet G has a year thats roughly 6,000 days long because it is very distant from the sun and thus takes a long ass time to get around it. The calendars for the planets were designed around this. Planet A essentially just has 4 months based on the 4 seasons, and Planet G has ten 600 day "periods" that are treated roughly like years (like, your birthday on the planet would be based on the number of periods youve lived instead of a year) and those periods are divided into months.

Fantasy worldbuilding is a hobby of mine! In this case, it is for a D&D campaign setting that I've been slowly building over a decade, and have a campaign thats actively been going for 3 years now. Some might see this level of detail torturous, but I absolutely love doing this and I think it makes for an immersive story for the players.

1

u/Electronic_Chair6383 8h ago

Haha I was joking based on its similarity to earth but that’s actually pretty goated! A D&D campaign that feels like a sci-fi, I’d be all over that!

1

u/Electronic_Chair6383 8h ago edited 8h ago

But for my two cents worth, why not just use the let function to set custom hours for a day? Arrange planet 2’s months, days, dated and cumulative hours in columns from A2 then use the below:

=LET(custom_hours, 38, total_hours, A2 * 24, days, INT(total_hours / custom_hours), rem_hours, MOD(total_hours, custom_hours), days & " days, " & rem_hours & " hours")

Every time you add a new planet you can repeat the formula in the next column, and you’ll be able to change the above to make columns for months and years too

1

u/Decronym 7h ago edited 2m ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
CHOOSECOLS Office 365+: Returns the specified columns from an array
EXACT Checks to see if two text values are identical
HSTACK Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF Specifies a logical test to perform
INDEX Uses an index to choose a value from a reference or array
INT Rounds a number down to the nearest integer
ISNUMBER Returns TRUE if the value is a number
LET Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAX Returns the maximum value in a list of arguments
MOD Returns the remainder from division
NOT Reverses the logic of its argument
QUOTIENT Returns the integer portion of a division
SUM Adds its arguments
TEXTAFTER Office 365+: Returns text that occurs after given character or string
TEXTBEFORE Office 365+: Returns text that occurs before a given character or string
TEXTSPLIT Office 365+: Splits text strings by using column and row delimiters
XLOOKUP Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
17 acronyms in this thread; the most compressed thread commented on today has 33 acronyms.
[Thread #49286 for this sub, first seen 2nd Sep 2026, 00:47] [FAQ] [Full list] [Contact] [Source code]

1

u/jb092555 7h ago

You divide the input down into seconds on the input planet, mulitply it by an established seconds:seconds ratio, then divide by the output planet's days, hours and minutes, then format the answer.

Add leap years if it gets too easy.

You're on the right track with MOD. Bear in mind it is kind of division under the hood.

"Days in a fortnight" 15 / 14 = 1.0714285714

"Remaining days / Offset / Day of the week" 0.0714285714 × 14 = 1 15 mod 14 = 1

1

u/RuktX 308 6h ago edited 3h ago

A few thoughts until I get back to a computer:

  • Following Excel's lead, I'd suggest using a "day" as your whole-number time unit, then fractions of a day for hours and smaller divisions.
  • Add a "day of year" column to each of your provided calendar tables, being 1 to 128 and 1 to 365
  • Starting with some number of days, and using your month length tables:
    • Divide days and year_length to get the decimal year; keep the integer part
    • XLOOKUP the integer part of the remainder (get this using MOD and INT) in the day_of_year column to get the month, and subtract the first day of that month then add 1 to get the day of the month
    • Multiply the fractional part of the remainder by hours_per_day to get decimal hours; keep the integer part
    • Multiply the non-integer part by 60 to get decimal minutes; keep the integer part
    • Multiply the non-integer part by 60 to get decimal seconds

---

Edit: Something like this

Admittedly I haven't accounted for edge cases, which might appear e.g. on the first or last day of a month if MOD returns 0. The fix is generally something like -1 inside the MOD, and +1 again outside.

1

u/excelevator 3067 5h ago

Each Planet A day is 2.851 days in Planet B. (365/128)

So to get B date from A date you multiply A by 2.851, and division for the reverse

divide the days by 38 hours for the other day...and divide for reverse

This is a math questoin, not an Excel question.

1

u/lolcrunchy 234 17m ago

I sent you a link to a google sheets that does the math for you. I'm not sharing it publicly as it may display my real name.

Remember that when you convert times between two planets, that's only accurate if there is a single time zone across the entire planet.

If the cultures across planet A like to align sunset with hour 28 of each day, then there must be time zones. The alternative is that one part of the planet is used to having sunset at hour 28 while the opposite side of the planet is used to having it at hour 9.

1

u/CompleteTangerine997 9m ago

I’d approach this with a lookup table rather than trying to do everything with MOD.

Create a table for each planet where every month/holiday has:

Month/Event | Start Hour | End Hour | Length

For example, Planet B could have Month 1 starting at hour 0, Month 2 at hour 720, etc., with the intercalary holidays included as their own rows.

Then the conversion becomes:

  1. Convert the Planet A date/time to a single number of elapsed hours from the common zero point.

  2. Use XLOOKUP with approximate match against Planet B's cumulative start-hour column to find which month/event contains that hour.

  3. Subtract that month's starting hour to get the day/hour within that month.

This also handles the intercalary days cleanly because they're simply additional rows in the calendar table rather than something MOD has to understand.

I'd also keep the calendar definitions in separate tables so you can change the month lengths or add/remove intercalary days without rewriting the conversion formulas.

If you're using a recent version of Excel, XLOOKUP with approximate matching should make this fairly straightforward.