cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kamotekid
Frequent Visitor

Format excel data for use in email body

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.

 

kamotekid_0-1623968390611.png

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ccc333ab
Solution Sage
Solution Sage

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

ccc333ab_0-1623983475368.png

Next I am formatting the date into MM-dd-yyyy format. 

ccc333ab_1-1623983547902.png

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. 

ccc333ab_2-1623983690891.png

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. 
ccc333ab_3-1623983922675.png
ccc333ab_4-1623983944207.png
ccc333ab_5-1623983973217.png

 

ccc333ab_6-1623984001038.png

 

ccc333ab_7-1623984046967.png

 

View solution in original post

4 REPLIES 4
ccc333ab
Solution Sage
Solution Sage

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

ccc333ab_0-1623983475368.png

Next I am formatting the date into MM-dd-yyyy format. 

ccc333ab_1-1623983547902.png

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. 

ccc333ab_2-1623983690891.png

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. 
ccc333ab_3-1623983922675.png
ccc333ab_4-1623983944207.png
ccc333ab_5-1623983973217.png

 

ccc333ab_6-1623984001038.png

 

ccc333ab_7-1623984046967.png

 

View solution in original post

v-bofeng-msft
Community Support
Community Support

Hi @kamotekid :

I've made a test for your reference:

1\My table:

vbofengmsft_0-1623985293421.png

2\My Flow:

vbofengmsft_2-1623985437400.png

 

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:

vbofengmsft_3-1623985468212.png

Best Regards,

Bof

 

 

 

Hi @kamotekid :

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

Best Regards,

Bof

kamotekid
Frequent Visitor

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

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,392)