Converting Log In times to local time

Ask for Help
1

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

2

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
4

What do you run this function in?

5

I use it with an On Change trigger.
If you’ve not used Apps Script before, I did a recent tutorial with a section on triggers (Part 4)

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

6

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.

7

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”)))

8

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?

1 Like
9

yes, but use an unambiguous date format :stuck_out_tongue: