So this is going to be a messy post but I'll try to make sense of it all...

It's the start of a new year, and like many others I have all the intention to get my a\$\$ in gear this year. With the help of Medium, Twitter, Google, and a lot of trying, I built myself a habit tracker that allows me to track my top 10 habits that I want to improve on.

So far, so good right? Well, this is where I get lazy . As manually tracking these habits every day is a habit that I know that won't stick, I have built a Form that allows me to track from my phone.

Problem 1
After transposing the Form input and lifting the date from the time stamp, I tested it and it worked by using this formula
Formula:
`Please Login or Register  to view this content.`
(where L1 is the date on the tracker, Form Responses 1! O1 is the extrapolated date from the form timestamp and O4 is the input to one of the questions, Ja=Yes).

Again, so far, so good. The day after I broke that logic. I filled in the form for that day, then later on realised I made a mistake and filled it out again. Now, the logic of L1='Form Responses 1'O1 no longer works. I tried to correct it by including HLOOKUP (without knowing exactly what I was doing)
Formula:
`Please Login or Register  to view this content.`
. Using this formula, it ticked the box. That would have been great if it wasn't for the fact that I used the formula on a date in the future, so it should have given a FALSE return.

I know when I'm beaten, so I hope that better minds can help me. What I need from the formula is the following:
1. The date on the tracker needs to match the date on the 'Form Responses 1' tab. Where it does not, it should give a FALSE return
2. If the dates match, it should look for the value on the correct row (Ja=TRUE, Nee=FALSE) and feed this back to the tracker
3. Where there are multiple occurances of the same date, it should always look for the most recent one

Problem 2
Once the problem with the tracking is solved, I get to really see how I'm doing. And I don't know about you guys, but I'm a sucker for streaks (Duolingo anyone?!?). However, the streak count I currently have only looks at the streak for that month and forgets all about it once the month is over. Formula is
Formula:
`Please Login or Register  to view this content.`

Therefore what I would like to build is a continuous streak. So the "current streak" of the previous month is the starting value of the new month. However, it shouldn't show on the next month until it is actually there. Also, I would like the "Longest streak" to remember a streak value even if it has not occured in this month.

I hope this all makes sense and that there are GSheet Gurus out there that can help me track my habits.

If you made it this far, I thank you already and I look forward to any feedback/tips.

There are currently 1 users browsing this thread. (0 members and 1 guests)