Shane Bartholomeusz

Geek and lover of all things tech related

Handling Excel Dates in Microsoft Power Automate


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

I recently had to bulk load some records from an Excel file into SharePoint Online using Microsoft Power Automate … 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 Power Automate as numbers … what the? What’s going on here?


The root problem is that Microsoft Power Automate reads Date columns as Text. Excel stores dates in Serial Date format which means that a date like ‘2019-01-10’ will be read as ‘43475’ within Power Automate.

You can read more about how excel handles dates here:

Therefore we need to convert the Number to a Date using the below expression within our Power Automate workflow

// 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'))


In this simple example I’ll create a workflow to insert the contents of an Excel spreadsheet into a HTML table.

The flow contains the following steps:
1. Read rows from an Excel Spreadsheet
2. Insert each row into a HTML formatted table

Excel Worksheet Screenshot

I’ve added the below expression into the ‘FormattedDateCompleted’ field of the HTML table.

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

Here is the output after running the workflow. You can see the original date value and the formatted date value.

Final Thoughts

Well I hope this solution has helped you. If you have any thoughts or alternative solutions feel free to post them in the comments below.

Shane Bartholomeusz


  1. Great Article! Thank a lot for your help!

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

    • Shane Bartholomeusz

      4th November 2019 at 9:48 pm

      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.

  3. Hi Shane, Thanks for the tip but how to convert/format date and time?

  4. Shane,

    I thank you profusely for this! You have saved me a lot of head scratching!


  5. anybody here that can explain to me how to get this configured in Power Automate ?

  6. Great article! btw, how to create an expression if i have date and time in excel, (Example: 43924.04097)

  7. Sorry but this doesn’t work for me. I just get an error stating ‘The Expression is Invalid’. Any ideas what I need to check.

    • Shane Bartholomeusz

      19th July 2020 at 8:26 am

      Hi John,
      Thanks for pointing that out. I just realised there was a mistake in the expression, which I’ve now corrected.

      I’ve also included an example for reference. Hope that helps.

  8. Great. Thanks. I’ll take a look.

  9. I am not able to get an html table column from a MM/DD/YY HH:MM formattedExcel column. I have no empty cells in the spreadsheet that are in the date column.

    I’ve tried your if / empty/ null but no luck.Column2 is my date column (see failure reason below).

    The execution of template action ‘Select’ failed: The evaluation of ‘query’ action ‘where’ expression ‘{
    “App Owner”: “@item()[‘Application Owner’]”,
    “Change Owner”: “@item()[‘Change Owner’]”,
    “Snap Size GB”: “@item()[‘SnapSizeGB’]”,
    “Snap Name”: “@item()[‘SnapName’]”,
    “Snap Description”: “@item()[‘SnapDesc’]”,
    “VM Name”: “@item()[‘VMName’]”,
    “V Center”: “@item()[‘VCenter’]”,
    “Column2 Date”: “@if(empty(item()?[‘Column2’]),null,addDays(‘1899-12-30’,int(item()?[‘Column2′]),’yyyy-MM-dd’))”
    }’ failed: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.

    2/6/19 6:43 AM
    8/30/19 1:16 PM
    5/27/20 3:54 AM
    5/27/20 2:50 PM
    5/27/20 2:50 PM
    9/26/19 11:31 PM
    10/10/19 11:34 PM
    2/7/20 8:24 PM
    5/19/20 3:12 PM
    5/19/20 3:13 PM
    6/11/20 11:08 PM
    6/11/20 11:09 PM

    • Shane Bartholomeusz

      8th August 2020 at 8:12 am

      The reason you encounter an error is because the above expression expects whole numbers only (i.e. an integer) however the time portion of the date/time stamp is returned as a fraction.

      For example,
      ’10/7/2020′ will show as ‘44111’.
      ’10/7/2020 2:00′ will show as ‘44111.0833333’.

      You can use the below expression to strip out the time component and return the date portion only.
      if(empty(item()?[‘Column2’]),null,addDays(‘1899-12-30’,int(split(item()?[‘Column2’], ‘.’)[0]),’yyyy-MM-dd’))

  10. Great work Shane! cheers!

  11. Hi Shane! This helps in a great way, but one question: when I run the Flow, the date it delivers is always a day BEHIND what the actual date should be. For example, if the designate date is August 30, it posts August 29 in the results.
    Further, since I’m creating a flow from Excel to Planner, it will post a new task with the Aug 29 date, but in the Planner schedule, it posts the Aug 30 date.
    Since this issue doesn’t occur when I create a task directly in Planner, I have to assume that the problem lies with how the date is being interpreted during the Flow.
    Any suggestions? Thanks in advance!

    • Shane Bartholomeusz

      20th August 2020 at 8:39 pm

      Hi Matt,
      While I haven’t come across this issue myself I suspect it could be related to a timezone conversion issue.

      Try convert the date to the correct timezone.

      convertFromUtc(, )
      convertFromUtc(utcNow(),’W. Australia Standard Time’)

  12. Saved my day. Thank you.

  13. This is exactly what I was looking for. Thank you!

  14. Mandi Johnson-Fisher

    7th January 2021 at 6:08 am

    I am trying to figure out why I keep receiving errors for null values. After converting the string I am updating a sharepoint list but the flow keeps failing out on the field I have converted. “cannot read push of undefined” because of the null value. Any ideas?

  15. Mandi Johnson-Fisher

    8th January 2021 at 9:34 pm

    It finally worked! It was user error (me) lol thanks for the help this saved me!

  16. There is another simple solution that works for me. Add a column in your original excel table that is the date column formatted as text. A formula like Text([Your Date Column], “dd/MM/yyyy”) should work. This will keep the formatting of your date column but turn it into a text value. When you create your flow use this Text date column instead of converting the date column.

  17. Excellent information! Solved our ETL load problem on a date. Thank you!

  18. Thank you so much mann!!!

  19. Thank you it was really helpful

Leave a Reply

© 2023 Shane Bartholomeusz

Theme by Anders NorenUp ↑