Today I looked at how to insert dates into Excel after a while back I tried to read dates from Excel
Changes since my previous post
In my previous Excel date post, I looked at the way Excel stores dates as a number of days since 1/1/1900.
No, Excel still writes dates as a number. Nothing has changed there.
Also, I still don’t think that Excel is a great datasource when you work with Power apps or Power Automate.
So what has changed.
Well, a little while back the Excel connector was updated, so that you can choose between the painful Serial number option and the ISO 8601 format when you deal with dates.
this option has been hidden under the blue Show advanced options link.
The Serial Number is that annoying number ( although …. we will see later in this post)
And the ISO8601 is the friendlier format like: 2021-06-21T13:14:09.0342501Z (although … we will see later in this post)
Insert dates into Excel using ISO 8601
I’m going to use the add a row into a table action to create a new row as shown below.
I use utcNow to generate me the current time in the ISO 8601 format. But I can use any date as long as it follows the right format. You could use formatDateTime to make sure that your date is formatted correctly.
So this is all good and easy. But why would you use that Serial number if you can use the the ISO 8601 format that is used a default by quite a few of the connectors.
Calculating dates
When you want to insert dates into Excel then you are likely to get a date from somewhere. This could be the UtcNow function as we saw earlier or we could get a date from fro example SharePoint list items or we could even get the date from Excel.
So just assume that we read the rows in the excel table and then we want to add one day to the date.
We could now use the List rows present in a table action and use the data returned and add one day to each line found and then insert the newly calculated day to the excel file.
So something like this:
Or the following expression can be used instead of the additional action
addDays(items('Apply_to_each')?['Date'],1)
Or to get the same done it is also possible to use the following expression instead if we switch over to the Serial number format.
add(int(items('Apply_to_each')?['Date']),1)
Not immediately easier.
But when you calculate the difference between two dates stored in Excel then this could quite well be easier, as all we need to do is subtract two numbers.