Sub ConvertUTCtoLocalTime()
    ' This macro converts ISO 8601 UTC timestamps (with T and Z) to local time zone
    ' Specifically handles format: 2025-04-05T04:09:44.580Z
    
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim utcTime As Date
    Dim localTime As Date
    Dim newCol As Long
    Dim timeColumn As Long
    Dim headerRow As Long
    Dim lastRow As Long
    Dim lastCol As Long
    Dim colLetter As String
    Dim conversionCount As Long
    
    ' Set to the active worksheet
    Set ws = ActiveSheet
    
    ' Ask user to select the column with UTC timestamps
    On Error Resume Next
    Set rng = Application.InputBox("Please select the column header of the UTC timestamps", "Select UTC Column", Type:=8)
    On Error GoTo 0
    
    ' Exit if user canceled
    If rng Is Nothing Then Exit Sub
    
    ' Get the column number and header row
    timeColumn = rng.Column
    headerRow = rng.Row
    
    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, timeColumn).End(xlUp).Row
    
    ' Find the last column with data
    lastCol = ws.Cells(headerRow, ws.Columns.Count).End(xlToLeft).Column
    
    ' Create new column for local time
    newCol = lastCol + 1
    colLetter = Split(Cells(1, newCol).Address, "$")(1)
    
    ' Add header for new column
    ws.Cells(headerRow, newCol).Value = ws.Cells(headerRow, timeColumn).Value & " (Local Time)"
    ws.Cells(headerRow, newCol).Font.Bold = True
    
    ' Format the new column to show date and time
    ws.Columns(colLetter & ":" & colLetter).NumberFormat = "yyyy-mm-dd hh:mm:ss.000"
    
    ' Track successful conversions
    conversionCount = 0
    
    ' Process each cell in the selected column
    For Each cell In ws.Range(ws.Cells(headerRow + 1, timeColumn), ws.Cells(lastRow, timeColumn))
        If Not IsEmpty(cell.Value) Then
            ' Convert ISO 8601 format to Excel date time
            utcTime = ISO8601ToExcelDate(cell.Value)
            
            ' If conversion was successful (not 0)
            If utcTime <> 0 Then
                ' Convert UTC to local time
                localTime = DateAdd("h", GetUTCOffset(), utcTime)
                
                ' Write the local time to the new column
                ws.Cells(cell.Row, newCol).Value = localTime
                conversionCount = conversionCount + 1
            End If
        End If
    Next cell
    
    ' Autofit the new column
    ws.Columns(colLetter & ":" & colLetter).AutoFit
    
    If conversionCount > 0 Then
        MsgBox "Conversion complete! " & conversionCount & " timestamps converted to local time in column " & colLetter, vbInformation
    Else
        MsgBox "No timestamps were converted. Please check that your data is in ISO 8601 format (e.g., 2025-04-05T04:09:44.580Z)", vbExclamation
    End If
End Sub

Function ISO8601ToExcelDate(isoString As String) As Date
    ' This function specifically converts ISO 8601 strings like "2025-04-05T04:09:44.580Z" to Excel date values
    
    Dim dateStr As String
    Dim timeStr As String
    Dim timeWithoutMilliseconds As String
    Dim resultDate As Date
    
    ' Clean the input string
    isoString = Trim(Replace(isoString, Chr(34), ""))
    
    ' Check for the expected format pattern (YYYY-MM-DDThh:mm:ss.sssZ)
    If Len(isoString) < 20 Or InStr(isoString, "T") = 0 Or Right(isoString, 1) <> "Z" Then
        ISO8601ToExcelDate = 0
        Exit Function
    End If
    
    ' Split the string at the 'T'
    dateStr = Split(isoString, "T")(0)
    
    ' Remove the 'Z' from the end
    timeStr = Left(Split(isoString, "T")(1), Len(Split(isoString, "T")(1)) - 1)
    
    ' Handle milliseconds - Excel can't display them but can store the value
    If InStr(timeStr, ".") > 0 Then
        timeWithoutMilliseconds = Left(timeStr, InStr(timeStr, ".") - 1)
    Else
        timeWithoutMilliseconds = timeStr
    End If
    
    ' Convert to Excel date/time
    On Error Resume Next
    resultDate = DateValue(dateStr) + TimeValue(timeWithoutMilliseconds)
    
    ' Add milliseconds if present (they don't affect the display but maintain precision)
    If InStr(timeStr, ".") > 0 Then
        Dim milliseconds As Double
        milliseconds = CDbl("0" & Mid(timeStr, InStr(timeStr, ".")))
        resultDate = resultDate + (milliseconds / 86400)  ' Convert milliseconds to days
    End If
    
    On Error GoTo 0
    
    ISO8601ToExcelDate = resultDate
End Function

Function GetUTCOffset() As Integer
    ' This function gets the local UTC offset in hours
    
    Dim localDate As Date
    Dim utcDate As Date
    
    localDate = Now
    utcDate = DateAdd("h", -DateDiff("h", Now, Now()), Now)
    
    GetUTCOffset = DateDiff("h", utcDate, localDate)
End Function