Fantasy Calendar Creation stumbling..

[m26e4u]

Having issues with Creating a Dynamic Fantasy Calendar.

By no means am I good with Excel. I can regurgitate things I use often, as far as free-thinking and logical breakdown regarding Excel formulas to execute solutions to problems… those skills are nonexistent.
I have tried to use the Date/Month and realized it is limited to the Gregorian System. I'm unaware of how to create new controls for the formula to reference. I combed through this messageboard as well as the interwebs to no avail. Nevertheless, I post here in hopes of finding a solution. Thanks for the assistance.

I am creating a Fantasy world where the Calendar is based on this timekeeping protocol.

100 second in a minute
50 minutes in a hour
25 hours in a day
10 days in a week
5 weeks in a month
10 months in an year

I'm trying to create a dynamic calendar in Excel based on this information so I can plug in a date (DD/MM/YYYY) and it would go to that month and year. Very linear.

I would also like to overlay seasonality phases based on a 30-day period with 12 seasons also making this across the timekeeping calendar.

I'll try to give an example of the date formatting. I don't need to name the days of the week. Just need to name the months.

Months:
One
Two
Three
Four
Five
Six
Seven
Eight
Nine
Ten

Then overlay the seasons. 12 seasons lasting 30 days each

Seasons:
Boiling
Hot
Warm
Tempid
Crisp
Cold
Freezing
Chilly
Brisk
Thawing
Pleasant
Steamy

For example, the date could be Day 42, of Month 5, in Chilly season, 0007.
Or 42/05/0007-Chilly

Again thank you very much for your assistance!

 

[NateSC]

Let me know if this is anything like what you were looking for. Columns T-W are helper columns and allow you to rename things easily. I wonder if you want to show when the next season starts on the calendar as well. This will take a bit more thought, but I should have most of the pieces in place.

temp macro work2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1																					1	2	10
2	Date:	10/1/0			(use dd/mm/yyyy)															Index	Month	Seasons
3																				1	Starter	Boiling
4		Month	Starter			Year	0000		Season	Hot										2	After Start	Hot
5																				3	Sooner	Warm
6			Day																	4	Longer	Tepid
7			1	2	3	4	5	6	7	8	9	10								5	Slower	Crips
8	Week	1	1	2	3	4	5	6	7	8	9	10								6	Bender	Cold
9		2	11	12	13	14	15	16	17	18	19	20								7	Rebounder	Freezing
10		3	21	22	23	24	25	26	27	28	29	30								8	Eventually	Chilly
11		4	31	32	33	34	35	36	37	38	39	40								9	Later	Brisk
12		5	41	42	43	44	45	46	47	48	49	50								10	Ender	Thawing
13																				11		Pleasant
14																				12		Steamy
Calendar

Cell Formulas
Range	Formula
U1	U1	=INDEX(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(B2,"/", " ")," ", "</m><m>")&"</m></k>","//m"),2)
V1	V1	=ROUNDDOWN(($G$4*10+INDEX(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($B$2,"/"," ")," ","</m><m>")&"</m></k>","//m"),2)*50+W1)/360*12,0)
W1	W1	=INDEX(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(B2,"/", " ")," ", "</m><m>")&"</m></k>","//m"),1)
C4	C4	=XLOOKUP(U1,T3:T12,U3:U12, "Invalid month",0)
G4	G4	=INDEX(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(B2,"/", " ")," ", "</m><m>")&"</m></k>","//m"),3)
J4	J4	=XLOOKUP(V1,T3:T14,V3:V14,,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C8:L12	Expression	=IF(C8=$W$1,TRUE,FALSE)	text	NO

 

[m26e4u]

Thanks for the reply!

This is what I have so far https://docs.google.com/spreadsheet...ouid=103558217958093259383&rtpof=true&sd=true
There are still issues with mine as I am having difficulty in toward the end of the seasonality rotation. Day 29 of the rotation is correct, then Day 30 switches to the next season. Missing by one day.....

Then I would like the input from the sheet1 to roll over the entire first month and I can't seem to get that to work. The months further down on sheet1 are only for reference ( I plan on deleting them and only working from the one monthly calendar.)

I think your solution is more simple although I would need to separate the day from the month and year and figure out how to adapt it to my sheet.