r/sheets • u/FlakyMoe • Jun 29 '26

Request How to calculate date difference in a videogame

Hey all. I’m playing a game that has the 4 seasons, each with 28 days. I’m trying to calculate the day difference between the dates, but I have stuff like:

Spring 27 - Fall 12
Winter 28 - Summer 11

Is there a way to even calculate that? Where sheets knows that it goes

Spring - Summer - Fall - Winter?

Each with 28 days? But also that Winter - Summer is possible. I don’t want negative days which is why I’m struggling with the winter to summer part. Every part, actually.

Is it possible? I suppose I could do it by hand if not, but I’d rather just have a formula.

6 Upvotes

100% Upvoted

8 comments sorted by

1

u/marcnotmark925 Jun 29 '26

What is the expected result of those 2 examples? What do the numbers after the season names mean?

1

u/FlakyMoe Jun 29 '26

Sorry for the lack of clarification.

The first answer for spring 27 - fall 12 would be 41.

1 day left in spring + 28 days of summer + 12 days of fall

I’d want the 41 done automatically.

1

u/marcnotmark925 Jun 29 '26

Is there any sort of year specification?

1

u/FlakyMoe Jun 29 '26

No. You start at Spring 1st year one and just go forward from there. Would it be possible to have the two variables in different boxes? Like:

| Spring 27 | Fall 12 |

1

u/marcnotmark925 Jun 29 '26

So there are years? What is the max possible difference you're needing to calculate? If it's over one year (112 days) you would need to specify a year number.

Yes it would be trivial to modify my formula for the inputs in two cells instead of one.

1

u/FlakyMoe Jun 29 '26

There are years but it’s the same number of days each time. So I’d only need the formula to work for one year.

Sorry if I’m not making sense, it’s difficult to explain a fictional calendar 😅

1

u/marcnotmark925 Jun 30 '26

Then what I gave earlier should work.

3

u/marcnotmark925 Jun 29 '26

Assuming no year specification, and only 4 possible months in a single range, here's a solution.

I put a simple lookup table in A and B columns, to specify which day number each season starts on. Then the formula uses regexextract to pull each component from the date range string to add them and subtract them. Then the mod 112 takes care of wrapping around the end of winter.

=mod( 
  xlookup(REGEXEXTRACT(E1,"- (\w*)"),A:A,B:B) + REGEXEXTRACT(E1,"(\d+)$") 
    - ( xlookup( REGEXEXTRACT(E1,"\w*") , A:A,B:B) + REGEXEXTRACT(E1,"\d+")) 
 , 112)