Help me spreadsheet jedis. You’re my only hope

[John_Cabrera]

Guys I’m banging my head against a wall trying to figure out how to build a formula or set of formulas to create a streak mechanic in my app.

I have users uploading content that is timestamped in one column, and I need to figure out a way to calculate what that user’s daily posting streak is. Meaning how many days in a row back from the current day have they posted at least one thing on the app.

I have tried all kinds of things—helper columns, queries (which are the least ideal cause they’re so intensive on the sheet), vlookup, match, countifs.

I finally landed on something I thought worked, but I was forced to write the formula to the sheet per new user using a script (which isn’t ideal either… I was hoping to have it all work with an auto updating arrayformula) but even that formula has failed me.

Can anyone help here? This is the basic layout I need to figure out:

Sheet one: column of timestamps, column of content, column of userIDs.

Sheet two: column of corresponding userIDs, column of calculated number of days of their current daily streak from sheet one (broken and returning to 0 if they miss a day posting anything to Sheet one).

[John_Cabrera]

Okay, I figured it out. It isn’t pretty. I’m currently leaning on Google Script to write in two separate formulas into two separate sheets (content sheet and user sheet)… but it works. I’m sure I could figure out a way for all the calculation to happen in just the content sheet… but my brain is fried.

If anyone needs help with a streak mechanic, holler at me. Just maybe give me a few days for my brain to settle.

[Robert_Petitto]

I was able to do this, but it wasn’t pretty. Here’s what I did:

USER SUBMISSION SHEET (I’m counting consecutive posts):

STREAK SHEET:
Created Column D to transpose the posts per user and grab the date value

Created this wonky formula that sums the frequency of 1s (Date value - prev Date Value and returning 1 if the value is 1).

Would have been awesome if I didn’t have to copy down the formula in Column B…array column wouldn’t have worked.

[Jeff_Hager]

I didn’t get a chance to think too deeply on this before you came up with a solution, but the beginning of my thought process was to use a query to load the form response sheet into a new sheet, but ordered by user and date. Then probably some arrayformula IF statement that compares the current row to the prior row and if the user matches, then check if the current row date is within 1 day of the prior row date. The ultimate result would be a number. Either 0 if either condition is false, or 1 plus the prior row value. It would keep adding 1 to the prior number only if it matched user and the date is within 1 day. Once there is not a match, then it would set the result to zero and it would start over. I think this would work with an arrayformula. This isn’t a complete thought, but just an idea. I have used 2 sheet methods a few times to temporary resort data in another sheet to work with it. The use a vlookup or something like that to return the calculated value.

[Manan_Mehta]

I have tried this a while ago and was able to achieve it with formulas that need to be dragged down to calculate the streaks. That could be automated using scripts but in-spite of many different tries, I couldn’t figure out how to use an arrayformula to get the result.
From what I understand, the arrayformula will have a range where the row compares with its previous row but I am unable to set a dynamic range in array formula which keeps changing on every row.
My formula is =iferror(VLOOKUP(A3&(Q3-1),$R$1:S2,2,false)+1,0)
Here the range R1:S2 is dynamic which means it needs to change on every row. Any idea how to convert this into an arrayformula?

[John_Cabrera]

Hey @Manan_Mehta, @Jeff_Hager, @Robert_Petitto, I keep meaning to come back to this and show you what I did for streak mechanic. Warning, it’s not pretty. It’s calculation intensive and it relies on .gs to write these formulas into each new record added to the sheet:

Okay, so I have a content sheet. I have a date field, a creator field, and a streak field (among other fields of course, but these are the only ones relevant). In the streak field I have this formula:

=IFERROR(IF(ROW()<>COUNTA(A$1:A)+1,DATEDIF(TO_DATE(INT(VLOOKUP(INDIRECT(“E$”&ROW()),INDIRECT(“E$”&ROW()&":BS"),67,0))+0.9999999999),TO_DATE(INT(VLOOKUP(INDIRECT(“E$”&ROW()),INDIRECT(“E$”&ROW()+1&":BS"),67,0))+0.9999999999),“d”),1),IF(NOW()-TO_DATE(INT(VLOOKUP(INDIRECT(“E$”&ROW()),INDIRECT(“E$”&ROW()&":BS"),67,0))+0.9999999999)>1,2,1))

In a nutshell, this formula checks how many days between the current submission and the next one by the creator of that piece of content. It does so, by subtracting the last second of the current day of the submission from the last second of the next day that same user submits. That means if they submit on the same day, the result will be zero, and if they submit at any time the next day, it will be one (accumulating with each day away from that). I use Row() and Row()+1 to do two separate vlookups, the second of which excludes the current record from the search, and finds the next record instead. I have a few conditions to account for when it’s the last record in the sheet (in which case it will either be 1 or 2—the latter essentially substituting for however long past 1 day it’s been).

Then in the User’s sheet, I have this formula being manually written in for each user:

=IFNA(MATCH(TRUE,INDEX(1/ARRAYFORMULA(RIGHT(SORT(ARRAYFORMULA(FILTER(Lor!A$3:A, Lor!E$3:E = INDIRECT(“C”&ROW()), Lor!CA$3:CA <> 0)&"-"&FILTER(Lor!CA$3:CA, Lor!E$3:E = INDIRECT(“C”&ROW()), Lor!CA$3:CA <> 0)),1,0),LEN(SORT(ARRAYFORMULA(FILTER(Lor!A$3:A, Lor!E$3:E = INDIRECT(“C”&ROW()), Lor!CA$3:CA <> 0)&"-"&FILTER(Lor!CA$3:CA, Lor!E$3:E = INDIRECT(“C”&ROW()), Lor!CA$3:CA <> 0)),1,0))-FIND("-",SORT(ARRAYFORMULA(FILTER(Lor!A$3:A, Lor!E$3:E = INDIRECT(“C”&ROW()), Lor!CA$3:CA <> 0)&"-"&FILTER(Lor!CA$3:CA, Lor!E$3:E = INDIRECT(“C”&ROW()), Lor!CA$3:CA <> 0)),1,0))))<>1),0)-1,0)

In a nutshell, this filters that previous column from the first content sheet by the user of this row, it flips the whole filtered row (I had to actually ad add ID numbers to the data in each row so that SORT could reverse order the values. Then I do a count of every number 1 from that row (all pieces of content that are one day apart from the next) until I hit a value greater than 1, in which case I stop the count.

I know… it’s nasty looking. But it works. How many record before my sheet starts lagging from these two formulas? That I can’t say.

[John_Cabrera]

Doh! Today, I noticed the second of these two formulas stopped working. The count wasn’t increasing past the creator’s most recent streak. Just realized the bug. That formula is filtering an array made up of the ID of the post then “-” followed by the number of days until the user’s next post… so a string that looks like 1-1, 2-1, 3-2, 4-5, 5-1, etc. Then in order to count the number of 1s in the second half of those strings until the first non-1, it sorts the filtered list in reverse order using the ID in the first position of that string as the sort reference:
5-1
4-5
3-2
2-1
1-1

But sorting by strings means the sort will be in alphabetical order based on the first letter/number of the string. So this method worked fine until you get to content ID 10, which orders it right after content ID 1:
9-1
8-1
7-1
6-2
5-1
4-5
3-2
2-1
1-1
10-1

To correct for this, I simply wrapped all of the FILTER functions in an additional VALUE function, and switched the “-” to a “.” This created the same basic effect, with the difference that now this sort is on numeric values (with decimals), which will put 10.1 at the top of a reverse SORT of these 10 values.

For anyone looking to use this for a streak mechanic, I hope this is clear. Happy to further advise if you need it.

[John_Cabrera]

Also, I realize that the second of these two formulas could be much shorter and straight forward if I simply used a QUERY function to reverse sort the column of days by the column of content ID (then I wouldn’t have to do this weird creation of a new sortable set of values within this column). And I did have it as a QUERY originally. It works exactly the same. But I’ve just found that QUERY functions are crazy calculation intensive, even in comparison to a convoluted function like the one above. So this was my way of doing it without having to rely on QUERY.

[Manan_Mehta]

In my case, I am having users input data once every day only. I calculate streaks by checking how many consecutive days has a user created an entry. For this I use multiple columns and concatenate data and then use vlookup to match it with previous data to check if the streak is continuous.
This image would make it much clearer of how I do it.

This formula then needs to be copy pasted in each row when there’s a new entry. I do that using google scripts. Was wondering if this could be helpful for you @John_Cabrera
and if this could be converted to an arrayformula somehow to avoid scripts.

[John_Cabrera]

This is clever. Can I see the formula you have for “Days since…”? And I guess I’m unclear what is the streak for exactly? In my case, I am calculating since the last time a person created a piece of content in the app… and they only keep their streak if they create something new every day.

[Manan_Mehta]

This is the formula for days since start: =arrayformula(if(len(A3:A),DATEDIF(P3:P,B3:B,"D"),""))
Calculates the difference between today’s date and one starting date from when you want to calculate the streak.
My streak is similar to yours. It’s for logging in activity daily. It checks the number of consecutive days that a user has logged his activity. If in middle there’s a gap of 1 day also, the streak starts from 1 again.
It works on the dates and not on the difference between the time of two posts. So 1st entry could be at 8 AM on 1st may and 2nd entry could be on 7 PM on 2nd May and it still would continue the streak even if the time difference is >24 hours.

[John_Cabrera]

You know… now I’m looking at my own formula wondering why I decided to add in that extra fat to force their posting times to be .999999999 of that day. DATEDIF and TO_DATE already turn a time stamp into a singular date without hour:min:sec, so that part of my formula seems a bit superfluous. When comparing it to what you did.

But I was so tired by this point in my work that my brain may have just malfunctioned, and perhaps I thought without it, the formula would be calculating between times rather than days. But TO_DATE alone basically serves the same. DATEDIF too.

[Manan_Mehta]

I can understand these things get frustrating with time and we end up doing extra things instead. Infact I had given up on the streak mechanic but eventually found out ways to do it.
The effort you put in to find a workaround is commendable though!
I hope my formulae helped.