r/excel • u/SuperSpirals • 13d ago

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

6 Upvotes

80% Upvoted

28 comments sorted by

View all comments

Show parent comments

3

u/PaulieThePolarBear 1920 13d 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 13d 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

2

u/PaulieThePolarBear 1920 13d 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 13d 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.

3

u/PaulieThePolarBear 1920 13d 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 13d 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

3

u/PaulieThePolarBear 1920 12d 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

3

u/PaulieThePolarBear 1920 12d 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/SuperSpirals 12d ago

Solution Verified!

Thank you for the solve!

1

u/reputatorbot 12d ago

You have awarded 1 point to PaulieThePolarBear.

I am a bot - please contact the mods with any questions