The design of my user onboarding flow does not allow me to capture the user created date through typical means. I need this so I can set up a drip email campaign for my users.
I think grabbing the first login data from App:Logins and stuffing it into a field in the profile sheet is the way to go. But I can’t figure out how to create a formula to do this.
Any ideas?
I use it all the time. Here you go. Add this to any column in your users sheet given that Column A is the email address column.
={"Joined";ArrayFormula(if(len(A2:A),text(VLOOKUP(A2:A,{'App: Logins'!B2:B,'App: Logins'!A2:A},2,0),"m/d/yyyy"),""))}To explain Robert’s formula, this takes all non-empty emails from your user profiles sheet, looks for it in the Logins sheet, then return the first date that matched the email. Since Glide writes new rows every time a user logs in, the first match will automatically be the value you’re looking for.
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 
@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? 
As in the difference between today and the join?
Yes
I’d use the glide data editor for that. Subtract joined date from today:
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.
Hmmm… I am getting the same “formula parse error” from my personal attempt at this.
You’re missing the } at the end of the formula.
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.
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.
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!
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.
