Get current UTC time into Excel with Python

Python in Excel adds lots of new possibilities to Excel including a new way to the latest UTC time. Here’s the full code, how it works and a fix for an annoying problem.

Knowing the UTC / GMT time is important for comparison with the ‘Last Trade Time’ data from Excel’s Stock / Currency data feed.  Here’s some Python code to get UTC time and insert it, as an Excel serial date/time, into a cell. See Excel gets Python – who, when and why

With the latest UTC time, it’s a simple subtraction from the ‘Last Trade Time’ to see how ‘old’ your price data is. See Easy way to get UTC / GMT current time in Excel for details.

Alas, Excel’s knows about the latest time (either Today() or Now()) but not which time zone that’s in. You need VBA code to check which timezone the Now() is showing. We already have some Excel VBA code that will grab the UTC time from a Windows computer only. Now there’s a platform independent way to get the same detail from Python. That will be handy once Python in Excel is added to Excel for Mac and Web.

Beginners Bonus! No fussing about with Python objects, which are useful but overkill here.

Using Python to get the UTC time

Python and it’s datetime module ‘knows’ about time zones. A single line will return the current date/time in any time zone you choose including UTC e.g.

Start by adding the modules …

import datetime as dt

from datetime import timezone

 …

then ask for the ‘now’ time specifying the UTC time zone.

dt.datetime.now(timezone.utc)

Convert Python date/time to Excel serial date

The tricky bit is converting the Python datetime object (dt in our example) into an Excel serial date.

On the Internet there are many, many solutions for this. We put together this function to do the job.

# user defined function datetoexcel_date()

def datetoexcel_date(datein):

    # Initializing a reference date

    # Base date is not 31 Dec but 30!

    BaseDateTime = dt.datetime(1899, 12, 30)

    # needed to make both date variables 'naive' - without TZ.

    datein = datein.replace(tzinfo=None)

    # whats the difference between the two?

    delta = datein - BaseDateTime

    return float(delta.days) + (float(delta.seconds) / 86400)

As usual with Office Watch code samples we’ve gone for readability over efficiency.  For example, the lines starting  ‘BaseDateTime’ and ‘datein’ could be folded into the ‘delta’ line like this.

delta = datein.replace(tzinfo=None) - dt.datetime(1899, 12, 30)

We’ve kept them separate so you can follow what’s happening.

Naïve vs Aware TimeZone error

In Python a datetime object can have a timezone attached to it (“offset-aware”) or not (“offset-naive”).

If you try to work with both an aware and naive object, it’ll trigger an error.

“ TypeError: can't subtract offset-naive and offset-aware datetimes”

That’s why there’s a line to convert a timezone aware date/time into a naive one for the calculation.

datein.replace(tzinfo=None)

Trouble with Python recalculating in Excel

For this bit of code, you’ll want it to refresh regularly to show the latest date and time.  The current Python in Excel is very limited in the refresh options.

The usual suspects like F9, Calculate Now, Data | Refresh All etc don’t work on cells with Python code. There’s no equivalent of Application.Volatile from VBA.

The only refresh options we could find are:

• Click in the Python code editor and press Ctrl + Enter or the green tick ‘Enter’ button at top-left.
• Use the VBA command Application.CalculateFull

Workaround to auto update Python code

The workaround is to make the Python code think it’s dependent on an Excel cell with a volatile function like NOW(). That means a simple F9 or other recalculation option will work.

We added a cell with =Now() then added a single line to the Python code which referenced that cell (F3 in our example). The code line doesn’t do anything (i.e. there’s no other use of the ‘temp’ variable) but it’s enough to fool Excel into updating the Python cell anytime the linked cell changes.

UTC time in Excel from Python – complete code

Here’s the complete code to copy/paste. Make sure the result is inserted as an Excel value not a Python Object.

# Calculating the excel serial date number for current date/time at UTC

import datetime as dt

from datetime import timezone

# user defined function excel_date()

def datetoexcel_date(datein):

    # Initializing a reference date

    # Note that here date is not 31st Dec but 30th!

    BaseDateTime = dt.datetime(1899, 12, 30)

    # needed to make both date variable 'naive' - without TZ.

    datein = datein.replace(tzinfo=None)

    # whats the difference between the two?

    delta = datein - BaseDateTime

    return float(delta.days) + (float(delta.seconds) / 86400)

datetoexcel_date(dt.datetime.now(timezone.utc))

Easy way to get UTC / GMT current time in Excel
Get the local time zone offset into Excel
Dates, time and duration, the truth in Excel
Excel gets Python – who, when and why