Skip to main content

How to Get Excel Dates as Dates, Not Numbers, in Power Automate

· 6 min read
Jagdish Kumawat
Founder @ Dewiride

Excel stores a date as a whole number and a time as a fraction of a day, and that's what a flow gets from List rows present in a table unless you ask for something else: 46297 instead of 2 October 2026, 0.791666666666667 instead of 19:00. One setting on the action returns them as dates.

The example is the old overtime workbook of the Overtime Timesheet app. Its table, OvertimeLog, has a Date column and Start and Finish columns that hold only times. A flow reads it with List rows present in a table (How to Read Rows from an Excel Table in Power Automate).

Step 1: See What the Default Returns​

With DateTime Format left empty, the first row of a test run came back like this:

The body output of a run with value.0.Department Emergency Department, value.0.Date 46297, value.0.Start 0.791666666666667 and value.0.Finish 0.958333333333333

A flow can't format, compare or convert these as dates without doing the arithmetic itself.

Step 2: Choose ISO 8601​

Open the action, expand Advanced parameters, and open DateTime Format. It offers two values:

The DateTime Format drop-down open with ISO 8601, Serial Number and Enter custom value

Select ISO 8601. Serial Number is what the action returned when the setting was empty.

Step 3: Check the New Output​

Test the flow again. The same row now has dates and times:

The body output of the next run with value.0.Department Emergency Department, value.0.Date 2026-10-02T00:00:00.000Z, value.0.Start 1899-12-30T19:00:00.000Z and value.0.Finish 1899-12-30T23:00:00.000Z

What came back, column by column:

Column in ExcelSerial NumberISO 8601
Date 02/10/2026462972026-10-02T00:00:00.000Z
Start 19:000.7916666666666671899-12-30T19:00:00.000Z
Finish 23:000.9583333333333331899-12-30T23:00:00.000Z
Time worked 03:300.1458333333333331899-12-30T03:30:00.000Z
Hours 3.5, a plain number3.53.5

Two things to know before using them:

  • A time-only cell gets the date 30 December 1899. The cell holds a fraction of a day with no date, so the date part is day zero of the count, 30 December 1899: the same start Microsoft's .NET documentation gives for this way of numbering days, midnight, 30 December 1899. Use only its time.
  • The Z doesn't make it UTC. The workbook's times are local times, typed as they appeared on the clock, and they come back unchanged with a Z on the end. If the times are local, convert them to UTC yourself (How to Convert Local Time to UTC in Power Automate with convertToUtc).

Step 4: Read the Parts You Need​

formatDateTime takes the date or the time out of these values (How to Format a Date in Power Automate with formatDateTime). Inside the loop over the rows, For each old row:

The date of the row
formatDateTime(items('For_each_old_row')?['Date'], 'yyyy-MM-dd')

returns 2026-10-02, and

The start time of the row
formatDateTime(items('For_each_old_row')?['Start'], 'HH:mm')

returns 19:00. HH is the hour from 00 to 23, and mm is minutes; MM would be the month.

Common Mistakes​

MistakeWhat happensDo this instead
Leaving DateTime Format emptyDates come back as serial numbersISO 8601
Using a time-only value as a dateThe date is 30 December 1899Take only its time with 'HH:mm'
Treating the Z as UTCLocal times are off by the clock changeConvert local times with convertToUtc
'yyyy-mm-dd' for a datemm is minutes'yyyy-MM-dd'

Troubleshooting​

SymptomLikely causeFix
46297 instead of a dateDateTime Format isn't setSet it to ISO 8601
A time shows the year 1899The cell holds only a timeUse the time part
Hours an hour off in summerThe local times were treated as UTCconvertToUtc with your time zone
A number column changedIt didn't: plain numbers stay numbersCheck the column's format in Excel

FAQ​

Can I convert the serial numbers instead?​

A serial number counts days, so you could add it to 30 December 1899 with addDays(), and turn the time fraction into minutes. ISO 8601 does it for you, for every date and time column at once.

Does the setting change the workbook?​

No. It only changes how the action returns the values.

Are other columns affected?​

Not the ones I compared: Department, a text column, and Hours, a plain number, came back the same with both settings.

Conclusion​

Set DateTime Format to ISO 8601 on List rows present in a table, and Excel's dates and times come back as dates and times. Time-only cells carry the date 30 December 1899, and the Z doesn't mean the times are UTC.

Additional Resources​