# Formula to Calculate User Created Date from App:Logins? **URL:** \ **Category:** Ask for Help\ **Created:** [October 22, 2020, 2:11am UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432 "2020-10-22T02:11:20Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Mauronic](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mauronic/32/21726_2.png) [@Mauronic](https://community.glideapps.com/u/Mauronic)\ **Post date:** [October 22, 2020, 2:11am UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/1 "2020-10-22T02:11:20Z")
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? ---
**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\ **Post date:** [October 22, 2020, 2:21am UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/2 "2020-10-22T02:21:07Z")
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. ```auto ={"Joined";ArrayFormula(if(len(A2:A),text(VLOOKUP(A2:A,{'App: Logins'!B2:B,'App: Logins'!A2:A},2,0),"m/d/yyyy"),""))} ``` ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [October 22, 2020, 3:48am UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/3 "2020-10-22T03:48:05Z")
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. ---
**Author:** ![Mauronic](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mauronic/32/21726_2.png) [@Mauronic](https://community.glideapps.com/u/Mauronic)\ **Post date:** [October 22, 2020, 4:58pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/4 "2020-10-22T16:58:56Z")
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 🙂 ---
**Author:** ![Rosewebstudio](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/rosewebstudio/32/12261_2.png) [@Rosewebstudio](https://community.glideapps.com/u/Rosewebstudio)\ **Post date:** [October 22, 2020, 5:48pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/5 "2020-10-22T17:48:51Z")
@Robert_Petitto using this would be great for giving users a 7 or 14 day trial of an app before asking them to pay. ---
**Author:** ![Mauronic](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mauronic/32/21726_2.png) [@Mauronic](https://community.glideapps.com/u/Mauronic)\ **Post date:** [October 22, 2020, 5:52pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/6 "2020-10-22T17:52:14Z")
> [@Robert\_Petitto](#): > > ```auto > ={"Joined";ArrayFormula(if(len(A2:A),text(VLOOKUP(A2:A,{'App: Logins'!B2:B,'App: Logins'!A2:A},2,0),"m/d/yyyy"),""))} > > ``` 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? 🙂 ---
**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\ **Post date:** [October 22, 2020, 6:52pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/7 "2020-10-22T18:52:04Z")
As in the difference between today and the join? ---
**Author:** ![Mauronic](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mauronic/32/21726_2.png) [@Mauronic](https://community.glideapps.com/u/Mauronic)\ **Post date:** [October 22, 2020, 7:55pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/8 "2020-10-22T19:55:02Z")
Yes ---
**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\ **Post date:** [October 22, 2020, 7:56pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/9 "2020-10-22T19:56:34Z")
I’d use the glide data editor for that. Subtract joined date from today: > [@new Date-time Math](https://community.glideapps.com/t/date-time-math/15963): > > The Math column can now do math with date/times. In particular: Subtracting two date/times produces a duration, which is a number and is measured in days, like in Google Sheets. Adding a duration (number) to a date/time, or subtracting it from a date/time will add/subtract that many days to it. You can pick “Now” as a substitution for a variable. ---
**Author:** ![Mauronic](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mauronic/32/21726_2.png) [@Mauronic](https://community.glideapps.com/u/Mauronic)\ **Post date:** [October 22, 2020, 8:00pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/10 "2020-10-22T20:00:37Z")
I would do that but I would like to access the data externally to run a drip email campaign. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [October 22, 2020, 10:01pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/11 "2020-10-22T22:01:34Z")
``` ={"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. ---
**Author:** ![Mauronic](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mauronic/32/21726_2.png) [@Mauronic](https://community.glideapps.com/u/Mauronic)\ **Post date:** [October 22, 2020, 10:35pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/12 "2020-10-22T22:35:46Z")
Hmmm… I am getting the same “formula parse error” from my personal attempt at this. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/2/2b13498f920c4eb0c55c6bf314311f39ddc0a4af.png) ---
**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\ **Post date:** [October 22, 2020, 10:45pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/13 "2020-10-22T22:45:50Z")
You’re missing the } at the end of the formula. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [October 22, 2020, 11:46pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/14 "2020-10-22T23:46:06Z")
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. ---
**Author:** ![Mauronic](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mauronic/32/21726_2.png) [@Mauronic](https://community.glideapps.com/u/Mauronic)\ **Post date:** [October 23, 2020, 4:27pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/15 "2020-10-23T16:27:56Z")
Good catch @kyleheney but I am still getting a formula parse error ---
**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\ **Post date:** [October 23, 2020, 4:42pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/16 "2020-10-23T16:42:05Z")
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. ---
**Author:** ![Mauronic](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mauronic/32/21726_2.png) [@Mauronic](https://community.glideapps.com/u/Mauronic)\ **Post date:** [October 23, 2020, 5:14pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/17 "2020-10-23T17:14:31Z")
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. ---
**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\ **Post date:** [October 23, 2020, 5:21pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/18 "2020-10-23T17:21:52Z")
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. ---
**Author:** ![Mauronic](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/mauronic/32/21726_2.png) [@Mauronic](https://community.glideapps.com/u/Mauronic)\ **Post date:** [October 23, 2020, 5:56pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/19 "2020-10-23T17:56:32Z")
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! ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/f/f2df59ea153d6c4a2ddb4e2f452feba92205b81c.png) ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [October 23, 2020, 9:51pm UTC](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432/20 "2020-10-23T21:51:05Z")
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. [Next page](https://community.glideapps.com/t/formula-to-calculate-user-created-date-from-app-logins/17432.md?page=2)