Power QueryExercises

Convert Unix timestamps and Excel serial dates in Power Query

How-to · By Tahir Nas · Examples run in our simulator · Updated

10 min read

To convert a Unix timestamp in Power Query, add it as a number of seconds to 1 January 1970: #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Timestamp]). To convert an Excel serial date such as 46023, use Date.From([Serial]) or change the column type to Date. Power Query reads a number as an OLE Automation date, which matches Excel's serial numbers from March 1900 onwards.

Two kinds of "number that is really a date"

Both formats store a point in time as a count from a fixed starting point, but the units and the starting points differ:

FormatCountsStarts atExampleMeans
Unix timeSeconds (sometimes milliseconds)1 January 1970, 00:00 UTC17672256001 January 2026, 00:00 UTC
Excel serial dateDays, with a fraction for the time1 January 1900 is day 1460231 January 2026

A quick way to tell them apart: a present-day Unix timestamp in seconds has 10 digits, in milliseconds 13, while an Excel serial for a recent date has 5 digits before any decimal point.

Unix timestamps to datetime

The POSIX standard defines the Epoch as "the time zero hours, zero minutes, zero seconds, on January 1, 1970 Coordinated Universal Time (UTC)" (Base Definitions, The Open Group). Its definition of seconds since the Epoch treats every day as exactly 86,400 seconds (Seconds Since the Epoch, The Open Group), so adding the number as a plain duration gives the right date and time, with no leap-second correction needed.

let
    Source = #table(type table [EventId = text, CreatedAt = number], {{"e1", 1767225600}, {"e2", 1735689599}, {"e3", 0}, {"e4", -86400}, {"e5", null}}),
    Epoch = #datetime(1970, 1, 1, 0, 0, 0),
    Added = Table.AddColumn(Source, "CreatedUtc", each if [CreatedAt] = null then null else Epoch + #duration(0, 0, 0, [CreatedAt]), type nullable datetime)
in
    Added

Result

EventIdCreatedAtCreatedUtc
e117672256002026-01-01T00:00:00
e217356895992024-12-31T23:59:59
e301970-01-01T00:00:00
e4-864001969-12-31T00:00:00
e5nullnull

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

1767225600 becomes 1 January 2026 at midnight and 1735689599 becomes 31 December 2024 at 23:59:59. Zero is the epoch itself, and a negative value counts backwards, so -86400 is 31 December 1969. The null row stays null instead of raising an error.

Adding a datetime and a duration is defined in the M language specification, and #duration(days, hours, minutes, seconds) accepts fractional seconds (M language values, Microsoft Learn). Name the result column …Utc so nobody later mistakes it for local time.

Milliseconds and microseconds

JavaScript, many web APIs and some databases send milliseconds instead of seconds. Divide by 1,000 (or 1,000,000 for microseconds) before you build the duration. If a column mixes units, the size of the number tells you which one you have:

let
    Source = #table(type table [EventId = text, CreatedAt = number], {{"e1", 1767225600}, {"e2", 1767225600123}, {"e3", 1767225600123456}}),
    Epoch = #datetime(1970, 1, 1, 0, 0, 0),
    ToSeconds = (v as number) as number =>
        if Number.Abs(v) >= 1e14 then v / 1000000
        else if Number.Abs(v) >= 1e11 then v / 1000
        else v,
    Added = Table.AddColumn(Source, "CreatedUtc", each Epoch + #duration(0, 0, 0, ToSeconds([CreatedAt])), type datetime)
in
    Added

Result

EventIdCreatedAtCreatedUtc
e117672256002026-01-01T00:00:00
e217672256001232026-01-01T00:00:00.123
e317672256001234562026-01-01T00:00:00.123

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

All three rows land on 1 January 2026, the second and third with a fraction of a second (the simulator displays it to the millisecond, 00:00:00.123). The thresholds work for dates between roughly 1973 and 5138, which covers real event data. If a source documents its unit, use that instead of guessing: a single conversion with the documented divisor is easier to review.

Timestamps stored as text

CSV and JSON sources often deliver the timestamp as text, sometimes with spaces around it and sometimes empty. Number.From handles the conversion, but an empty string is not a number, so test for it first.

let
    Source = #table(type table [CreatedAt = text], {{"1767225600"}, {" 1735689599 "}, {""}}),
    Epoch = #datetime(1970, 1, 1, 0, 0, 0),
    Added = Table.AddColumn(Source, "CreatedUtc", each
        let t = Text.Trim([CreatedAt]) in if t = "" then null else Epoch + #duration(0, 0, 0, Number.From(t)), type nullable datetime)
in
    Added

Result

CreatedAtCreatedUtc
17672256002026-01-01T00:00:00
1735689599 2024-12-31T23:59:59
null

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The padded value converts after Text.Trim, and the empty row becomes null. Without the empty-text test, that row shows "We couldn't convert to Number", which the error page for that message explains in detail.

Keep the zone, or convert it

A Unix timestamp is always UTC. If you add it to a #datetimezone epoch instead, the result carries the +00:00 offset with it, and later steps (or a reader) can't mistake it for local time.

let
    Source = #table(type table [CreatedAt = number], {{1767225600}}),
    Added = Table.AddColumn(Source, "CreatedUtc", each #datetimezone(1970, 1, 1, 0, 0, 0, 0, 0) + #duration(0, 0, 0, [CreatedAt]), type datetimezone)
in
    Added

Result

CreatedAtCreatedUtc
17672256002026-01-01T00:00:00+00:00

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The value is 2026-01-01T00:00:00+00:00. Converting that to UK or any other local time is a separate step with its own traps (summer time, and the refresh machine's zone), covered in Time zones in Power Query: DateTimeZone without the confusion.

Back from datetime to Unix time

Subtract the epoch and ask for the total seconds. Duration.TotalSeconds returns the whole duration in seconds, not just the seconds part.

let
    Source = #table(type table [Stamp = datetime], {{#datetime(2026, 1, 1, 0, 0, 0)}, {#datetime(2024, 12, 31, 23, 59, 59)}}),
    Added = Table.AddColumn(Source, "UnixSeconds", each Duration.TotalSeconds([Stamp] - #datetime(1970, 1, 1, 0, 0, 0)), Int64.Type)
in
    Added

Result

StampUnixSeconds
2026-01-01T00:00:001767225600
2024-12-31T23:59:591735689599

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The two rows return 1767225600 and 1735689599, the same values the first query started from. Only do this with UTC datetimes: subtracting the epoch from a local time gives a number that is off by the UTC offset.

Excel serial numbers to dates

Excel stores a date as the number of days since its starting point, with the time of day as a fraction. Microsoft's support article on date systems says that in the 1900 date system "dates are calculated by using January 1, 1900, as a starting point", and gives 5 July 2011 as serial 40729 (Date systems in Excel, Microsoft Support).

Power Query understands this format natively. The documentation for DateTime.From says a number becomes "a datetime equivalent of the OLE Automation Date expressed by value" (DateTime.From, Microsoft Learn), and the OLE Automation date numbering lines up with Excel's for every date from 1 March 1900.

let
    Source = #table(type table [Serial = number], {{46023}, {46023.75}, {45658.5}, {61}, {60}, {1}}),
    AsDateTime = Table.AddColumn(Source, "AsDateTime", each DateTime.From([Serial]), type datetime),
    AsDate = Table.AddColumn(AsDateTime, "AsDate", each Date.From([Serial]), type date)
in
    AsDate

Result

SerialAsDateTimeAsDate
460232026-01-01T00:00:002026-01-01
46023.752026-01-01T18:00:002026-01-01
45658.52025-01-01T12:00:002025-01-01
611900-03-01T00:00:001900-03-01
601900-02-28T00:00:001900-02-28
11899-12-31T00:00:001899-12-31

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

What the rows show:

  • 46023 is 1 January 2026, and 46023.75 is the same day at 18:00. Date.From drops the time part.
  • 45658.5 is 1 January 2025 at midday.
  • 61 is 1 March 1900, the same date Excel shows.
  • 60 and 1 come out as 28 February 1900 and 31 December 1899, one day earlier than Excel.

The last point is Excel's doing, not Power Query's. Microsoft explains that Excel "incorrectly assumes that the year 1900 is a leap year", a choice inherited from Lotus 1-2-3 for compatibility (Excel incorrectly assumes that the year 1900 is a leap year, Microsoft Learn). Excel's serial 60 is 29 February 1900, a day that never existed. Real data almost never goes back that far, but if yours does, check the first two months of 1900 by hand.

A whole column of serials

When the column already holds numbers, a type change is all you need. This is what usually happens when an Excel column formatted as General, or a CSV saved from Excel, arrives with 46023 where a date should be. If the dates look right in Power Query and only turn into numbers once they reach a sheet, the cause is different, and why dates turn into numbers after loading to Excel covers it.

let
    Source = #table({"InvoiceDate"}, {{46023}, {46024}, {null}}),
    Typed = Table.TransformColumnTypes(Source, {{"InvoiceDate", type date}})
in
    Typed

Result

InvoiceDate
2026-01-01
2026-01-02
null

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The column becomes 1 and 2 January 2026, and the null stays null.

When the serial is text

If the serial arrives as text ("46023"), changing the type straight to Date fails, because the text is read as a date string rather than a number:

let x = Date.From("46023") in x

Result: an error

DataFormat.Error: We couldn't convert to Date.

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The simulator reports "We couldn't convert to Date"; Excel and Power BI may word the message differently. Change the type to a number first, then to a date, as two steps:

let
    Source = #table({"InvoiceDate"}, {{"46023"}, {"46024"}}),
    AsNumber = Table.TransformColumnTypes(Source, {{"InvoiceDate", type number}}),
    AsDate = Table.TransformColumnTypes(AsNumber, {{"InvoiceDate", type date}})
in
    AsDate

Result

InvoiceDate
2026-01-01
2026-01-02

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Both rows now convert. The same two-step fix applies whenever a type change fails on numbers stored as text; the lesson on fixing DataFormat.Error at the source shows how to find the step where it should happen.

Workbooks that use the 1904 date system

Some workbooks, mostly ones created in older versions of Excel for Mac, count from 1 January 1904 instead. Microsoft's article states that "the difference between the two date systems is 1,462 days" (Date systems in Excel, Microsoft Support), with the 1900 serial always the larger. Add 1,462 before converting:

let
    Source = #table(type table [Serial1904 = number], {{44561}, {39267}}),
    AsDate = Table.AddColumn(Source, "Date", each Date.From([Serial1904] + 1462), type date)
in
    AsDate

Result

Serial1904Date
445612026-01-01
392672011-07-05

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

39267 is Microsoft's own example of 5 July 2011 in the 1904 system, and the query returns that date. If every date in a column looks four years and a day too early, this is the cause.

Going the other way

Number.From on a date returns its serial, which is handy when a downstream tool expects Excel numbers:

let
    Source = #table(type table [InvoiceDate = date], {{#date(2026, 1, 1)}, {#date(2011, 7, 5)}}),
    Serial = Table.AddColumn(Source, "ExcelSerial", each Number.From([InvoiceDate]), Int64.Type)
in
    Serial

Result

InvoiceDateExcelSerial
2026-01-0146023
2011-07-0540729

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

1 January 2026 returns 46023 and 5 July 2011 returns 40729, matching Microsoft's example.

Columns that mix serials and text dates

A column typed by hand in Excel can hold real dates next to text that only looks like a date. The real dates arrive as datetime values, or as serial numbers when the cells are formatted as General. Test the type of each value and handle each kind on purpose:

let
    Source = #table({"Mixed"}, {{46023}, {#datetime(2026, 1, 3, 0, 0, 0)}, {"2026-01-04"}, {"05/01/2026"}}),
    Fixed = Table.AddColumn(Source, "Date", each
        if [Mixed] is number or [Mixed] is datetime or [Mixed] is date then Date.From([Mixed])
        else try Date.FromText([Mixed], [Format = "yyyy-MM-dd", Culture = "en-GB"])
            otherwise Date.FromText([Mixed], [Format = "dd/MM/yyyy", Culture = "en-GB"]), type date)
in
    Fixed

Result

MixedDate
460232026-01-01
2026-01-03T00:00:002026-01-03
2026-01-042026-01-04
05/01/20262026-01-05

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The four rows become 1, 3, 4 and 5 January 2026. Spell out the text formats you expect: "05/01/2026" is 5 January in the UK and 1 May in the US, and a format string with a culture removes the guess (in a format string "/" stands for the culture's date separator, so the culture matters too). The lesson on converting dates and numbers with a locale and the change type using locale guide cover the text side in depth, and the lesson on try … otherwise explains the fallback pattern.

Practise and check

Once a column holds real dates, reporting columns are easy to add; the post on fiscal years and fiscal quarters does it for a year that doesn't start in January. To practise working with timestamps after conversion, try the exercise that asks you to split a timestamp into its date and hour. The guide on data type pitfalls that quietly change your numbers covers the other ways a type change can mislead you. Every query on this page was run in the Power Query Exercises simulator, and the dates in the text are its output.

Frequently asked questions

Why is my converted Unix time an hour out?

The conversion gives UTC. In UK summer time, local clocks are an hour ahead of UTC, so the value looks one hour early to anyone reading it as local time. Convert to local time on purpose, as a separate step, rather than adjusting the epoch.

Can I convert Unix time with a type change?

No. A type change from number to datetime treats the number as an Excel-style day count, so 1767225600 would be read as about 1.77 billion days, far beyond 31 December 9999, the last date M supports. Use the epoch addition shown above.

Why does a type change turn 2026-01-01 into 46023?

You changed a Date column to a number. The date was fine; Power Query showed its serial. Change the type back to Date, or keep both columns if a downstream tool needs the number.

Sources

About the author

Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.

Keep reading

Practise Power Query in the browser with graded exercises checked against fresh data.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.