Single excel date extraction and formating

[Anonymous]

Hello,

   I am wanting to import the date from the excel file below. I have created an email with the table below, minus the date referred and note column. I want to know how to format the date that shows up in my email as a date. The date referred column will always have the same date for each row. I would like to take this date and include it in the body of my email, without getting a ton of emails based off the number of rows in the excel document. 

Any advice would help. 

Thank you

[Anonymous]

@Anonymous ,

Since Excel stores dates as a number, the quickest way to get it in a DateTime format is to use the addDays() expression:

The expression should be like:

addDays('1899-12-31',[insert date from Excel here],'yyyy-MM-dd')

The reason behind this is that the number that Excel stores for the date is the number of days since January 1, 1900. For some reason, to get the right date, I have to add the Excel date to December 31, 1899. You can figure out which one works for you.

My theory is that it has to do with Timezones. Since PA deals with most DateTimes in UTC timezone, at Midnight on January 1, 1900 in UTC, it was technically 7PM on December 31, 1899 in EST (my timezone).

I hope this helps.

thanks,

Kyle

[Anonymous]

Thank you. I am constantly going to be updating and changing dates to this table. So what would I put in the "insert date from excel here" part of the expression.

[Anonymous]

@Anonymous ,

you would use Dynamic Content from "List Rows Present in a Table" for the column with the desired date in it.

thanks,

Kyle

[Anonymous]

Thank you for your reply. I do not know what I am doing wrong. I am getting an error with the second compose statement. 

[Anonymous]

Can you please show a screenshot of the output of the first compose from that failed run? (The one in the Apply to Each loop).

PA is saying that that isn't a number, when it is expecting one. It's probably the null (empty) cell in the BK Dismissed Date column that is causing this error.

[Anonymous]

Ah, yes I I do have a empty cell in the BK Dismissed Date, which is something that may happen in this data. 

[Anonymous]

I just noticed that you only have the first Compose in your Apply to Each loop and nothing else. This means that your flow is going to loop through all of the rows in your table, but the output from the Compose action in the loop isn't going to be used anywhere before it is replaced by the value in the next iteration of the loop. Additionally (and this is the real problem) the output of the Compose action inside the loop is not accessible by actions outside of the loop. If you tried using Dynamic Content to point to that Compose, it wouldn't show up. The reason for this is that, outside of the loop, that Compose has no value.

Any subsequent actions that require the specific output of the first Compose need to be inside of that loop; basically the rest of your flow through building your array.

As for the issue of having occasional null values in that date column, you'll probably need to add a condition where you check to see if that field is null, if it is, then do nothing with that date value (or lack of value as the case may be), and if it is not (in the "No" branch), have your Compose with the addDays() expression. Then below the condition (but still inside the loop) have the rest of your array-building actions. Then after the loop runs through all of the rows in your Excel Table and builds your table-array, you can create the HTML table and send the email.

Those last couple of steps are not my area of expertise, as I have not yet had much (read:any) experience with arrays and HTML tables. But I am confident in the logic for the Loop and the Condition.

Let me know how it goes.

thanks,

Kyle

[Paulie78]

As you have said:

The date referred column will always have the same date for each row

You might as well just do a single compose that gets the first entry from the Excel sheet instead of picking it up in every loop with this:

addDays('1899-12-30', int(outputs('List_rows_present_in_a_table')?['body/value'][0]['Date Referred']), 'dd-MM-yyyy')

Then you don't have to worry about empty values(unless it is in the first row), and you don't have to execute it many times over.

[Anonymous]

@Paulie78 ,

Thank you, I was trying to figure out the best way for @Anonymous  to get that date in a single action without looping through each row.

However, as I understand it, the BK dismissed date will not always be the same. So @Anonymous  needs a way to evaluate each value and get it into a usable date format to go into their HTML table.

I am glad you're here though, as you came to mind as I was considering how to address any issues @Anonymous may have with the actual building of the table for their email.

[Paulie78]

@Anonymous glad to be here! Thank you 😀

You could do something like this in the loop:

if
(
  equals(items('Apply_to_each')?['BK Dismissed Date'], '')
  ,'No Date', 
  addDays('1899-12-30', int(items('Apply_to_each')?['BK Dismissed Date']), 'dd-MM-yyyy')
)

This returns the date if there is one, if there is not it says "No Date". That should fix the error in the loop.

I also wrote up how the dates work on my blog:

https://www.tachytelic.net/2020/11/convert-excel-dates-power-automate/

[Anonymous]

Thank you all for your help. I have tried multiple ways of building this flow. My problem now is getting the table out of the loop and into the email. 

@Paulie78 Thank you for your instructions on the single compose to get the single date. That worked 

[Paulie78]

I dont understand why you have the loop at all, am I missing something simple?

[Anonymous]

@Paulie78 ,

I was thinking that @Anonymous needed to loop through each row as each "BK Dismissed Date" was converted to a date from a number. However, I am now wondering if the conversion expression can be put directly into the "Daily Template Variables" in the "BK Dismissed Date" line.

Would that bypass the need for looping?

[Anonymous]

@Anonymous I did try that and got an error that the action type does need to be 'foreach' for repeat items. I also got the same error message when I tried it as a separate compose as well. 

[Paulie78]

I would simply do this:

By pumping the output from Excel into a select we can sort out the Date AND specify all of the HTML columns because that will thin out the output from Excel. In my example above the input of the select looked like this for one record:

{
    "@odata.etag": "",
    "ItemInternalId": "2c9242e1-a667-4523-a92c-66b845e5350e",
    "Order Ref": "6075",
    "Value": "11223.16",
    "Date": "44126",
    "Date Referred": "44126",
    "BK Dismissed Date": "44126"
  }

and the output looks like this:

{
    "Order Ref": "6075",
    "Value": "11223.16",
    "Date": "22-10-2020"
  }

So the data is ready to plop straight into a html without even having to build the columns etc.

Then that will be a really clean flow with that setup and fast too.

[Anonymous]

@Paulie78 Thank you, I think this would work well and keep the flow clean. I am still learning about Power Automate, I am still getting an error and not understanding why. 

[Paulie78]

Before we work the error out, can you just get rid of the Date conversion and put the excel date back in, then test it please? Then if it works, we can figure out what went wrong with the date expression.

[Anonymous]

Yes, the flow worked when I for rid of the Date conversion.

[Paulie78]

Ah, I know what it is, you still have the blanks in BK Dismissed right?