Formula to Calculate User Created Date from App:Logins?

Sweet! Thanks so much Robert and great explanation @ThinhDinh

Sometimes I just can’t wrap my head around complex formulas. I think part of it is that the “IDE” is a tiny little formula bar :slight_smile:

1 Like

@Robert_Petitto using this would be great for giving users a 7 or 14 day trial of an app before asking them to pay.

After creating the joined column, I tried to create a “Days Joined” column but I have been wrestling with errors, do you have a formula in your back pocket for that as well? :slight_smile:

As in the difference between today and the join?

Yes

I’d use the glide data editor for that. Subtract joined date from today:

1 Like

I would do that but I would like to access the data externally to run a drip email campaign.

={"Date Difference";ARRAYFORMULA(IF(A2:A<>"",DATEDIF(A2:A,TODAY(),"D"),"")}

This function checks if your A column, assuming that is where you store your dates from the previous step, is not empty. If it is indeed not empty then calculate the difference in days between that date and today.

1 Like

Hmmm… I am getting the same “formula parse error” from my personal attempt at this.

image

1 Like

You’re missing the } at the end of the formula.

1 Like

Thanks Kyle, sorry was typing from my phone so missed that obvious thing. You can try the edited version. Also noticed the " on phone is different, which makes the formula wrong.

1 Like

Good catch @kyleheney but I am still getting a formula parse error

I wonder if it’s a language settings issue — for me, that first bit of text where you give your column a name is always shown in green when I add the quotes. Your quotes look a bit more curly than mine. Try copying and pasting this " and use it instead of what you’ve got. Maybe if you’re using a different language keyboard, the double quotes isn’t registering the same in sheets.

I could be way off though haha

Actually, as I look closer, your other quotes look to be all closed quotes (as opposed to the first ones being open and the 2nd ones being closed). May not matter in sheets, but maybe it does. Mine (and it could just be a font thing) come up as the same whether they’re first or last quotes.

1 Like

WOW. You nailed it. I haven’t had this kind of quotes problem in like 15 years.

Now that it can parse the formula, it is throwing a new error:

Function DATEDIF parameter 1 expects number values. But ‘2020-09-03T22:27:56.451Z’ is a text and cannot be coerced to a number.

Can you change the format of that column to a date format and see if that helps?

If not, you may need to extract the date value and use that instead of your joined value.

Whew, OK that was a pain to troubleshoot. This is what works:

={"Days Since Join";ARRAYFORMULA(IF(A2:A<>"",DATEDIF(datevalue(left(A2:A,10)),TODAY(),"D"),""))}

For posterity, here is how the data & formulas behave in sheets and glide. I hope this is useful to someone!

1 Like

I believe if you format the original column in the Logins sheet as datetime then it would happen automatically, so we don’t need the left method.

I just tried it and it didn’t work

Hello, how would you do it directly from Glide data editor ? Thanks

You can’t. Glide Tables doesn’t have this App: Logins sheet. Best to create an onboarding flow where you calculate a user’s first login date by pushing a button at the end of onboarding.