CODEWITHSUNDEEP
exceltimetimezonecell
tjo
tjo

Reputation: 19

Show London current time in one cell for users in all locations

I would like one cell in my spreadsheet to always show the current time at GMT+0, London. The sheet will be used by people in multiple time zones, but I want this one cell to show the time in London always, no matter where the user is located. How can I do this?

I've tried random things but nothing is working.

Upvotes: 1

Views: 156

Answers (3)

rotabor
rotabor

Reputation: 4698

You can get the current time from the Internet which gives you the precision time regardless of computer's time zone settings which can be wrong.

"worldtimeapi.org" provides API to get the current time at the defined location:

"http://worldtimeapi.org/api/timezone/Europe/London" returns

{ "utc_offset": "+01:00",
"timezone": "Europe/London",
"day_of_week": 6,
"day_of_year": 244,
"datetime": "2024-08-31T20:16:55.059987+01:00",
"utc_datetime": "2024-08-31T19:16:55.059987+00:00",
"unixtime": 1725131815,
"raw_offset": 0,
"week_number": 35,
"dst": true,
"abbreviation": "BST",
"dst_offset": 3600,
"dst_from": "2024-03-31T01:00:00+00:00",
"dst_until": "2024-10-27T01:00:00+00:00",
"client_ip": "176.37.162.106"
}

Then you need to parse this JSON:

=LET(
  recalc,NOW(),
  t,INDEX(
    TEXTSPLIT(
      WEBSERVICE("http://worldtimeapi.org/api/timezone/Europe/London"),
      ","),
    1,5),
  DATEVALUE(MID(t,13,10))+TIMEVALUE(MID(t,24,8))
)

"recalc,NOW()" is needed to update the value on worksheet calculation.

UPD

"www.timeapi.io" provides more stable response:

{
"year": 2024,
"month": 8,
"day": 31,
"hour": 19,
"minute": 31,
"seconds": 14,
"milliSeconds": 696,
"dateTime": "2024-08-31T19:31:14.6962264",
"date": "08/31/2024",
"time": "19:31",
"timeZone": "UTC",
"dayOfWeek": "Saturday",
"dstActive": false
}

=LET( 
  a,NOW(),
  t,INDEX(
    TEXTSPLIT(
      WEBSERVICE("https://www.timeapi.io/api/time/current/coordinate?latitude=0&longitude=0"),
      ","),
    1,8),
  DATEVALUE(MID(t,13,10))+TIMEVALUE(MID(t,24,8))
)

Upvotes: 0

Black cat
Black cat

Reputation: 6271

With the use of Kernel32.dll you can solve this issue.

Place this code in a Module

Private Declare Function GetSystemTime Lib "kernel32.dll" () As systemtime
Private Type systemtime
  year As Integer
  month As Integer
  weekday As Integer
  day As Integer
  hour As Integer
  min As Integer
  sec As Integer
  millisec As Integer
End Type
Public nexttime As Date



Function timeGMT() As String

Dim output As systemtime

output = GetSystemTime()
timeGMT = Format(output.hour, "00") & ":" & Format(output.min, "00") & ":" & Format(output.sec, "00")
End Function

The function is invoked in a cell with =timeGMT()

If you want continuously refresh the value use Application.CalculateFull in the ThisWorkbook module with this code. To set the refresh period adjust the TimeValue function to it. (In the example it is 6 sec.)

Public nexttime As Date

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nexttime, "thisworkbook.Workbook_Open", , False

End Sub

Private Sub Workbook_Open()
Application.CalculateFull
nexttime = Now() + TimeValue("0:0:6")
Application.OnTime nexttime, "thisworkbook.Workbook_Open"

End Sub

Upvotes: 1

Luuk
Luuk

Reputation: 14958

From: https://www.mrexcel.com/board/threads/how-to-insert-utc-time-into-a-cell.1216942/

Function GimmeUTC()
    ' https://www.mrexcel.com/board/threads/how-to-insert-utc-time-into-a-cell.1216942/
    ' Returns current date/time at UTC-GMT as an Excel serial date value
    Application.Volatile  
    Dim dt As Object
    Set dt = CreateObject("WbemScripting.SWbemDateTime")
    dt.SetVarDate Now
    GimmeUTC = dt.GetVarDate(False)
End Function

After defining this function, you can use =GimmeUTC() in a cell to show UTC time.

When using Google Sheets, you can change the settings of your worksheet, and then use NOW().

enter image description here

Upvotes: 1

Related Questions