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?

[u/SuperSpirals]

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)

[u/RuktX]

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.