Skip to main content
Solved

Audi Log TIME (ISO-8601) Conversion

  • October 28, 2024
  • 0 replies
  • 183 views
JimHarrison
Forum|alt.badge.img+18

Wondering if anyone has done the work to convert the Quickbase Audit Log Timestamp to local time? 

I export the logs via CSV and then am working towards a process to convert the "2024-10-28T15:53:25.956Z" timestamp to PST. I'm seeing it as a multistep process involving text to columns and then math. 

If you have a simpler solution, I would appreciate hearing how its done. 

Thanks,

Best answer by JimHarrison

ChatGPT to the rescue.
=TIMEVALUE(MID(A2,12,8)) - TIME(7,0,0)

=DATEVALUE(LEFT(A2,10))



Forum|alt.badge.img+15
  • DonLarson
  • Registered
  • October 28, 2024

Jim,

There is no time zones in the date/time function of Excel.  The math gets very tricky when the calculation will take you back across midnight.

1 AM GMT on the 5th is 6PM on the 4th assuming there is no Daylight Saving Time issues.   

I would import this back in to Quickbase and have a Pipeline update a new field with a conversion to the timezone that you want.

 

JimHarrison
Forum|alt.badge.img+18
  • JimHarrison
  • Author
  • Registered
  • Answer
  • October 28, 2024

ChatGPT to the rescue.
=TIMEVALUE(MID(A2,12,8)) - TIME(7,0,0)

=DATEVALUE(LEFT(A2,10))



Terms & ConditionsAccessibility statement