# Converting Log In times to local time

**URL:** <https://community.glideapps.com/t/converting-log-in-times-to-local-time/20513>\
**Category:** Ask for Help\
**Created:** [December 30, 2020, 5:35pm UTC](https://community.glideapps.com/t/converting-log-in-times-to-local-time/20513 "2020-12-30T17:35:45Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![James\_Boynton](https://avatars.discourse-cdn.com/v4/letter/j/ea666f/32.png) [@James\_Boynton](https://community.glideapps.com/u/James_Boynton)\
**Post date:** [December 30, 2020, 5:35pm UTC](https://community.glideapps.com/t/converting-log-in-times-to-local-time/20513/1 "2020-12-30T17:35:45Z")

</div>

Hi All,

Has anyone figured out the formula in Sheets to convert the zulu time that is captured in the App:Logins sheet when a user log into a Glide app into local time? I suppose splitting the column at T, stripping out the Z and converting the time manually will do it, but you have to repeat that every time someone logs in.

Thank you if anyone has already dug deep into this one!

James

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [December 30, 2020, 5:39pm UTC](https://community.glideapps.com/t/converting-log-in-times-to-local-time/20513/2 "2020-12-30T17:39:33Z")

</div>

I use a trigger function to do this:

```
function format_time_cols(sheetname, start_col, cols) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
  sheet.getRange(2,start_col,sheet.getLastRow(),cols).setNumberFormat('yyyy-mm-dd hh:mm:ss').setHorizontalAlignment('left');
}

```

The function accepts 3 parameters:

- `sheetname`: the name of the sheet to operate on
- `start_col`: the first column to apply the formatting to
- `cols`: how many columns to format

---

<div class="post-metadata">

**Author:** ![James\_Boynton](https://avatars.discourse-cdn.com/v4/letter/j/ea666f/32.png) [@James\_Boynton](https://community.glideapps.com/u/James_Boynton)\
**Post date:** [December 30, 2020, 5:44pm UTC](https://community.glideapps.com/t/converting-log-in-times-to-local-time/20513/4 "2020-12-30T17:44:55Z")

</div>

What do you run this function in?

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [December 30, 2020, 5:50pm UTC](https://community.glideapps.com/t/converting-log-in-times-to-local-time/20513/5 "2020-12-30T17:50:14Z")

</div>

I use it with an On Change trigger.  
If you’ve not used Apps Script before, I did a recent [tutorial](https://community.glideapps.com/t/using-app-script-with-a-json-api/20051) with a section on triggers (Part 4)

Have a look through that, and give a yell if you need any help setting it up.

---

<div class="post-metadata">

**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:** [December 31, 2020, 12:22am UTC](https://community.glideapps.com/t/converting-log-in-times-to-local-time/20513/6 "2020-12-31T00:22:59Z")

</div>

Just want to make sure, you’re trying to “format” the time only, and not trying to convert that to your local time, is it correct?

I don’t think the time that is written to the Login sheet carries any timezone information. Please correct me if I’m wrong.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [December 31, 2020, 1:01am UTC](https://community.glideapps.com/t/converting-log-in-times-to-local-time/20513/7 "2020-12-31T01:01:31Z")

</div>

if you don’t wanna deal with scripts, just put this formula in cell App: Logins D2

=arrayformula(if(A2:A="", ,TEXT(DATEVALUE(MID(A2:A,1,10)) + TIMEVALUE(MID(A2:A,12,8)), “mm/dd/YYYY h:mm:ss am/pm”)))

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [December 31, 2020, 2:02am UTC](https://community.glideapps.com/t/converting-log-in-times-to-local-time/20513/8 "2020-12-31T02:02:39Z")

</div>

ooh, I missed the “convert” bit.  
But yes, I believe you’re right - the format of the date/time strings that Glide puts in the GSheet is misleading, as they have nothing to do with GMT. I think I read somewhere that they represent the time on the users device?

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [December 31, 2020, 2:04am UTC](https://community.glideapps.com/t/converting-log-in-times-to-local-time/20513/9 "2020-12-31T02:04:19Z")

</div>

> [@Uzo](#):
>
> if you don’t wanna deal with scripts, just put this formula in cell App: Logins D2
> 
> =arrayformula(if(A2:A=“”, ,TEXT(DATEVALUE(MID(A2:A,1,10)) + TIMEVALUE(MID(A2:A,12,8)), “mm/dd/YYYY h:mm:ss am/pm”)))

yes, but use an unambiguous date format 😛