Login

r/sheets

▲ 4 r/sheets

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.

reddit.com
u/FlakyMoe — 6 days ago
▲ 3 r/sheets+1 crossposts

Help with multi-layer formula, please :)

I'm not sure what the correct syntaxis for this formula is – please help!

I need a cell that filters a range (AI5:AI25) for the highest number and shows the corresponding value in column B (B5:B25). I also need it to show no more than 3 results (in case there are more than 1 equally highest numbers) and shows them separated by ", " if there is more than 1 result. I ALSO need it to only take into consideration/check if column B (B5:B25) has a value...

Here is my failed attempt at this:

=IF(
  B10<>"",
  IF(
    COUNTIF(D5:AH25,TRUE)>0,
    TEXTJOIN(", ",TRUE,INDEX(SORT(FILTER({B5:B25,AI5:AI25},B5:B25<>""),2,FALSE),SEQUENCE(MIN(3,ROWS(FILTER(B5:B25,B5:B25<>"")))),1)),
    ""
  ),
  ""
)

Here's a link to the worksheet – please see the 2nd tab/sheet.

Thank you in advance!

docs.google.com
u/chuelaconarroz — 10 days ago
▲ 3 r/sheets+1 crossposts

Functions that check the first instance of some text

So I'm creating a GPA calculator and my college doesn't count the first instance of an F in a given class towards your GPA. I was wonder if there is either a function that can tell if something is the first instance of text, or a function that can identify which cell has certain text and name it (like if D2 has "F." then it would say "D2"), which would allow me to brute force the issue?
Sample Version: https://docs.google.com/spreadsheets/d/1k9FNMW8Ud9slBW1816ZtWyUDSSJ6qzaWdc9TPpFeT_c/edit?gid=2100307022#gid=2100307022

u/oubuob — 12 days ago