Format excel data for use in email body

(0) Share

Report

Posted on by kamotekid

Hi everyone, I hope someone would help.

A teammate has a flow that generates an excel spreadsheet, a sample of which is in below screenshot.

I am making a flow to get that excel data for Title that begins with "Approved", then format the Proposed Start Date and Proposed End Date columns and then put into an email body if their Start date is earlier than today but end date is later than today. In short, those rows whose start and end dates are current will be put together into the body of an email.

I am mainly having difficulty with converting the values in those dates columns - they are not date formatted, but are actually string. How should I convert them to MM-dd-yyyy format on my flow? It's driving me nuts. Once that's done, I next need to set up conditions to compare date relative to utcnow() and put them onto an HTML format table in email.

Please help me in my desperation. Thanks.

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

ccc333ab 1,144 on at

Like (0)

Report
Copy link

Link copied!
To convert date and do a comparison, here is a short example here to hopefully help you out. I'm just using variables so you'll need to replace with your Excel values and such....

1. Just setting up variables to emulate your Excel string dates
Next I am formatting the date into MM-dd-yyyy format.
formatDateTime(variables('strStartDate'),'dd-MM-yyyy')
formatDateTime(variables('strEndDate'), 'MM-dd-yyyy')
(Really don't need this, can do this in the condition expression but just wanting to show you the results when I run it).

And now check the condition. I like to ensure all comparison operators are all of the same type, so I have converted all the "times" to ticks. Expressions are below.
ticks(variables('strStartDate')) <= ticks(utcNow())
ticks(variables('strEndDate')) >= ticks(utcNow())

And the true result. I changed the dates to fall out of this range and it produced false.

Was this reply helpful? Yes No
v-bofeng-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @kamotekid :

I've made a test for your reference:

1\My table:

2\My Flow:

My Expression:

@and( less(formatDateTime(Concat(split(item()?['Proprsed Start Date'], ' ')[2], '-', split(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(item()?['Proprsed Start Date'], 'Jan', '1'), 'Mar', '2'), 'Feb', '3'), 'Apr', '4'), 'May', '5'), 'Jun', '6'), 'Jul', '7'), 'Aug', '8'), 'Sep', '9'), 'Oct', '10'), 'Nov', '11'), 'Dec', '12'), ' ')[1], '-', split(item()?['Proprsed Start Date'], ' ')[0]), 'yyyy/MM/dd'), formatDateTime(utcNow(), 'yyyy/MM/dd')), startsWith(item()?['Title'], 'Approved'), greater(formatDateTime(Concat(split(item()?['Proprsed End Date'], ' ')[2], '-', split(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(item()?['Proprsed End Date'], 'Jan', '1'), 'Mar', '2'), 'Feb', '3'), 'Apr', '4'), 'May', '5'), 'Jun', '6'), 'Jul', '7'), 'Aug', '8'), 'Sep', '9'), 'Oct', '10'), 'Nov', '11'), 'Dec', '12'), ' ')[1], '-', split(item()?['Proprsed End Date'], ' ')[0]), 'yyyy/MM/dd'), formatDateTime(utcNow(), 'yyyy/MM/dd')) )

The Result:

Best Regards,

Bof

Was this reply helpful? Yes No
v-bofeng-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @kamotekid :

I found that @ccc333ab 's solution is much more better,please consider tring his solution.

Best Regards,

Bof

Was this reply helpful? Yes No
kamotekid 25 on at

Like (0)

Report
Copy link

Link copied!

Thank you both for the quick answers. I wanted to try both, but gave @ccc333ab 's first try and it worked! I did not know about function "ticks" and that was a more efficient way to compare dates I was working with.

Thanks again!

AJ

Was this reply helpful? Yes No