Handling Excel Dates in Microsoft Flow

Problem

Dealing with dates can be a real pain in the proverbial sometimes … and Microsoft Flow is no exception.

I recently had to bulk load some records from an Excel file into SharePoint Online using Microsoft Flow … you’d be forgiven to think that this would be a trivial task … WRONG!

Microsoft Flow and Excel Logos

All was going well until … I encountered date fields in my Excel file … then all hell broke lose!

I was puzzled why Dates in my Excel file were processed by Microsoft Flow as numbers … what the? What’s going on here?

Solution

The root problem is that Microsoft Flow reads Date columns as Text. It turns out that Excel stores dates in Serial Date format which means that a date like ‘2019-01-10’ will be read as ‘43475’ within Flow. You can read more about how excel handles dates here – http://www.cpearson.com/excel/datetime.htm.

Therefore we need to convert the Number to a Date using the below expression within our flow


// Format
if(empty(<DATE TO CONVERT>,null,addDays('1899-12-30',int(<DATE TO CONVERT>),'yyyy-MM-dd'))

// Example
if(empty(item()?['Start Date'],null,addDays('1899-12-30',int(item()?['Start Date']),'yyyy-MM-dd'))

Shane Bartholomeusz

G’day, I’m a technical solutions consultant based in Perth, Australia that specialises in the Microsoft technology stack, including .NET, Dynamics 365, Azure and general web development etc.

This blog is my place to share my thoughts, helpful solutions and just random nerdy stuff.
Shane Bartholomeusz

Latest posts by Shane Bartholomeusz (see all)

4 thoughts on “Handling Excel Dates in Microsoft Flow

  1. Hi Shane – I’m just getting into Flow. Where exactly did you make the change? Can it be made at the spreadsheet/table level ?

    • Hi Robert, you’ll need to create the Expression within Microsoft Flow via the popup menu that appears when you click a field/step in your Flow.

      Otherwise, (although I haven’t tested this) you could convert the ‘date’ formatted cell to a ‘text’ type field and it should import without issue.

Leave a Reply