We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

LibreOffice Calc Macro, how do I get UTC hour?

asked 2021-03-26 20:50:02 +0200

tonyburnaby gravatar image

updated 2021-03-26 23:15:52 +0200

I was hoping there is a function like: hour(utcNow())

This document mentioned "uno" dates, and a structure member "IsUTC"

But I am not able to find anything else or related sample codes

Thanks

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
4

answered 2021-03-28 19:14:09 +0200

sokol92 gravatar image

Since LO 6.3

Function getUTCTime() As Double
  Dim aLocale As New com.sun.star.lang.Locale
  Dim oLocaleCalendar2 As Object
  oLocaleCalendar2=CreateUnoService("com.sun.star.i18n.LocaleCalendar2")
  With oLocaleCalendar2
    .loadDefaultCalendarTZ(aLocale, "GMT") 
     getUTCTime=.getLocalDateTime-Int(.getLocalDateTime)
  End With  
End Function

Sub TestgetUTCTime
  Msgbox Format(getUTCTime, "hh:mm:ss")
End Sub
edit flag offensive delete link more

Comments

Nice!

This:

getUTCTime=.getLocalDateTime-Int(.getLocalDateTime)

has a potential of returning wrong result, when the two calls happen across midnight (one before, the other after). It would show OK as string, but will introduce an error of 24 hours when used for calculations.

It's better to save the result of a single call to a variable, or use

getUTCTime=.getLocalDateTime Mod 1

And why use "GMT", when you may use getDateTime to get UTC, as the question stands 😉

Mike Kaganski gravatar imageMike Kaganski ( 2021-03-28 21:55:16 +0200 )edit

Thanks a lot! Didn't know. The following code based on your insight also works in LibreOffice V3.3 and in AOO.

Function nowUTC(Optional pNullDate As String) As Double
REM The function shall regard the document's NullDate if it is called from Calc.
REM without passing a pNullDate (factually "0"). 
REM Otherwise the pNullDate must be passed in ISO 8601 extended.
  Dim aLocale As New com.sun.star.lang.Locale
  Dim oLocaleCalendar As Object
  Dim sOffset As String, nOffset As Double
  If IsMissing(pNullDate) Then pNullDate = "0"
  If pNullDate = "0" Then
    With ThisComponent.NullDate
      sOffset = "" & .Year & "-" & .Month & "-" & .Day
    End With
  End If
   nOffset = DateValue(sOffset)
  oLocaleCalendar=CreateUnoService("com.sun.star.i18n.LocaleCalendar")
  With oLocaleCalendar
    .loadDefaultCalendar(aLocale)
     nowUTC=.DateTime +  DateValue("1970-01-01") - nOffset
  End With
End Function
Lupp gravatar imageLupp ( 2021-03-28 22:28:47 +0200 )edit

Thank you colleagues for your valuable comments!

sokol92 gravatar imagesokol92 ( 2021-03-29 12:45:31 +0200 )edit

Thanks again to @sokol92. Sorry. The code posted in my previous comment had some flaws (sOffset !)
A version reworked with that respect and slightly enhanced you find here: https://forum.openoffice.org/en/forum... . There may also be a discussion at some time.

Lupp gravatar imageLupp ( 2021-03-29 16:23:31 +0200 )edit

Another variant of the function.

Function getUTCDateTime() As Date
  Dim aLocale As New com.sun.star.lang.Locale
  Dim oLocaleCalendar2 As Object
  oLocaleCalendar2=CreateUnoService("com.sun.star.i18n.LocaleCalendar2")
  With oLocaleCalendar2
    .loadDefaultCalendarTZ(aLocale, "UTC") 
     getUTCDateTime=DateSerial(1970,1,1) + .getLocalDateTime
  End With  
End Function

Sub TestgetUTCDateTime
  Msgbox getUTCDateTime
End Sub
sokol92 gravatar imagesokol92 ( 2021-03-29 16:41:52 +0200 )edit
0

answered 2021-03-26 22:48:46 +0200

Ratslinger gravatar image

Hello,

Just tried this in Win 10 and seems to work:

https://forum.openoffice.org/en/forum...

edit flag offensive delete link more

Comments

yeah, I am using Linux, tried that code, didn't work. Thanks all the same.

tonyburnaby gravatar imagetonyburnaby ( 2021-03-26 23:06:48 +0200 )edit

But the python-script at the same link should be portable.

J.

Wanderer gravatar imageWanderer ( 2021-03-26 23:56:20 +0200 )edit

@tonyburnaby,

Just tested the python script using Ubuntu 20.04 with LO v7.1.1.2 and had no issues.

In future please include basic information with questions such as OS & LO version.

Ratslinger gravatar imageRatslinger ( 2021-03-27 00:09:13 +0200 )edit

J. yeah, I saw that too, unfortunately that is beyond my coding skills :) thanks for pointing that out though.

Ratslinger, Thanks for the effort, yeah, will remember to include OS and LO versions for future questions

tonyburnaby gravatar imagetonyburnaby ( 2021-03-27 00:10:02 +0200 )edit
0

answered 2021-03-26 22:23:40 +0200

erAck gravatar image

No such thing in BASIC.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2021-03-26 20:50:02 +0200

Seen: 85 times

Last updated: Mar 28