• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Sco Openserver
    • Sco Unix Support
    • SCO Openserver Installation ISOs
    • SCO Openserver Binaries
    • Add a Hard Drive to an Openserver 5 Virtual Machine
    • Install SCO Vision SQL-Retriever ODBC Driver on Windows 10
    • License Expired on Virtual SCO Openserver Installation
    • How to reset the root password on SCO Openserver 5
  • Scripting
    • PowerShell
      • Add leading zeros
      • Check if a File Exists
      • Grep with Powershell
      • Create Environment Variables
      • Test for open Ports
      • Append to a Text File
    • VBScript
      • Check if a File Exists
      • lpad and rpad functions
      • Windows Update E-Mail Notification
  • Office 365
    • Connect to Office 365 with PowerShell
    • Add or remove an email alias using Powershell
    • Change Primary email address of Active Directory user
    • How to hide an AD user from the Global Address List
    • How to hide mail contacts from the Global Address List
    • Change the primary email address for an account with PowerShell
    • Change Primary email address of an AD User
    • Grant a single user access to access to all calendars
    • Forward email to an external address using Powershell
    • Convert shared mailbox to user mailbox with Powershell
  • Get in Touch
  • About Me
    • Privacy Policy

Easily convert Excel Dates to usable Power Automate Dates

November 15, 2020 by Paulie Leave a Comment

When you use the “List rows present in a Table” action in Power Automate to get data from Excel that contains dates, you might be surprised to see that the data in the date column is actually an integer and not a date.

Take a look at this Excel table:

Image showing an Excel table with a Date column that will be converted back into a date in Power Automate

In the example above the both the Date column and the Int Date column contain the same data, but with different display formats. This is because Excel stores dates as an integer. The integer represents the number of days that have elapsed since the 1st January, 1900. In Power Automate, the first record of this table is represented like this in JSON:

{
	"Order Ref": "6075",
	"Value": "11223.16",
	"Date": "44126",
	"Int Date": "44126"
}

In JSON representation, you can clearly see that Date and Int Date hold the same value.

Typically you would want to use that date field to filter, compare or insert data to some other format. So let’s look at an example.

Filter by Excel Date Column

In this example I want to get data from Excel and filter it so that only the rows that match the date 22-10-2020 remain (of which there is only one).

Image of Power Automate filtering an Excel table based on the Date column.

The expression on the left side, which converts the Excel date is:

addDays('1899-12-30', int(item()['Date']), 'dd-MM-yyyy')

Explanation:

The addDays function adds a specified number of days to a given date. It’s second parameter requires an integer, so the int() function is used to convert the JSON string to an int.

The final parameter specifies the date format of the newly formatted date.

You can use exactly the same expression inside of an if condition to only perform operations on records matching a particular date.

If possible, it is better to use a filter before a loop as the performance is much better within a filter compared to a loop.

Validate the Excel data first

One of the problems that can come up when using filtering Excel data based on a date column is that the int function can error easily. Take a look at the following example:

Image of an Excel spreadsheet with dates is going to be filtered with Power Automate.

Cells E4 and E6 will generate the following error if they are used in the example expression above:

The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.

This sort of situation can happen easily when you have a data source like Excel. One thing I like to do is add an additional hidden column in Excel that has a formula like the following:

=ISNUMBER([@[Delivery Date]])

Because Excel is actually storing the numbers as dates, this evaluates to either true or false:

Image showing if Excel dates are valid for use in a Power Automate flow.

The invalid dates can then easily be filtered in Power Automate:

Image showing Excel Dates being filtered in a Power Automate Flow

This test isn’t fool proof (for example there could easily be a valid number in there which isn’t a valid date) but it is a good start. Of course you can easily check for blanks etc in Power Automate without using this hidden column technique.

Create an Excel Integer Date from a Power Automate Date

Sometimes you might want to do the opposite function and create an Excel style integer date from a Power Automate date. For example, Excel date 22nd November 2020 is represents as 44157. To create that integer in Power Automate from the same date you can use the expression:

div
(
  sub
  (
    ticks(formatDateTime('2020-11-22', 'yyyy-MM-ddT00:00:00')), 
	599264352000000000), 
  864000000000
)

or for todays date you could use:

div
(
  sub
  (
    ticks(formatDateTime(utcNow(), 'yyyy-MM-ddT00:00:00')), 
	599264352000000000), 
  864000000000
)

A little explanation:

The ticks function returns an integer which represents the number of 100-nanosecond intervals, since January 1, 0001 12:00:00 midnight.

599264352000000000 is the tick value which represents 30th December 1899, which is the start date of Excel Dates.

So the number of ticks in the specified date is subtracted from the Number of ticks for the 30th December 1899 and then divided by 864000000000 which gives you an integer which replicates what Excel would produce!

I hope this helps you to convert your Excel date for use in Power Automate. Good luck!

Related

Filed Under: Power Platform Tagged With: Power Automate

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Power Automate Support Image
Link to my LinkedIn Profile
Go to mobile version