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:
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).
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:
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:
The invalid dates can then easily be filtered in Power Automate:
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!
