Formatting the Excel Date from DD /M to MM/DD

[GaneshaGV123]

Hi All,

I have a Date column in Excel where the date format is DD /M. I want to filter the data in this column to get the content for a specific day. To do this, I need to format the date from DD /M to MM/DD and then compare it with today's date.

However, the expression formatDateTime(parseDateTime(item()?['Date of Birth']), 'MM-dd') is throwing an error.

Error Message - "Filter_array_Date' failed: The evaluation of 'query' action 'where' expression '@equals(formatDateTime(parseDateTime(item()?['Date of Birth']), 'MM-dd'), formatDateTime(utcNow(), 'MM-dd'))' failed: 'In function 'parseDateTime', the value provided for date time string '19 /7' was not valid. "

Please Help

Date format in Excel

 Thanks in advance,

[ManishSolanki · Accepted solution]

Hi @GaneshaGV123 

To filter the records based on today's date, you could take help from below example:

Filter expression used in the above expression is:

@equals(item()?['Date of Birth'], formatDateTime(utcNow(), 'dd/ M'))

In the above example, I have converted the today's date format same as "Date of Birth" column in source excel sheet in the filter expression.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[trice602]

Hi @GaneshaGV123 ,

If I can recommend converting this in Excel first, you will be much happier with the results and is the "easy route".  Use this formula in Excel as a helper column.

=CONCAT(TEXTAFTER(A2,"/",1,0),"/",TEXTBEFORE(A2," /",1,0))

[GaneshaGV123]

Awesome that really worked !!

[GaneshaGV123]

Yes, I understand. The complete flow is as follows:

A file is shared via email by an application.
The file is saved in OneDrive.
Once the email is received, a filter is applied to the file.
This process is automated and does not require any manual intervention.

[GaneshaGV123]

I am grateful for your quick help and support with the date format conversion and comparison in Power Automate.

I have a new scenario now. I want to send a single email to users in the following format:

Name Department Location
Name Department Location
Name Department Location

I am getting this input from a filtered array converted as a string. However, when I use this input in the body of the Send Email action, it looks like a paragraph. Could you please help me achieve the desired format?

I hope this is helpful! Let me know if you have any other questions.

Input to the body of the Send Email action

Email output 

Thanks & Regards

Ganesh G V

[GaneshaGV123]

@ManishSolanki 

I am grateful for your quick help and support with the date format conversion and comparison in Power Automate.

I have a new scenario now. I want to send a single email to users in the following format:

Name Department Location
Name Department Location
Name Department Location

I am getting this input from a filtered array converted as a string. However, when I use this input in the body of the Send Email action, it looks like a paragraph. Could you please help me achieve the desired format?

I hope this is helpful! Let me know if you have any other questions.

Input to the body of the Send Email action

Email output 

Thanks & Regards

Ganesh G V

[ManishSolanki · Accepted solution]

Hi @GaneshaGV123 

Pls pass the body of filter array directly in the "Create HTML table" action. The output of create HTML table can then be send to an email body:

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[GaneshaGV123]

You are really Awesome !! It works as expected.

However I would like to tweak the name field in the Create HTML table. The current name format is "Last name, First name". I would like to change the name format to "First name Last name".

eg:- GV, Ganesh - Current Format

       Ganesh GV Expected Format

The above screenshot is the actual output and the expected output Should be as per below

Rukmesh Kumar

Snehal Rangnath Bhavar

[ManishSolanki]

Hi @GaneshaGV123 

Could you pls share the sample data text in the same format as pass to "Create HTML table"?

Thanks

[GaneshaGV123]

Hello,

Please find the below

[ManishSolanki · Accepted solution]

Hi @GaneshaGV123 

Pls use the below expression for "Name" column header value in "Create HTML action" to get the name in required format:

join(reverse(Split(item()?['Name'],', ')),' ')

Rest all header values remains the same.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[GaneshaGV123]

Manish Really Awesome !!

It worked as expected Really appreciated and Thank you for all your support !!

I Would like to get in touch,

Really your just Woow and Awesome !!

[GaneshaGV123]

Hello,

I'm here with new Scenario, threshold limit, where list the rows present a table, the maximum limit is 5000 but in my data I have almost 8000 data kindly help how to fix it.

Regards

Ganesh G V

[GaneshaGV123]

@ManishSolanki 

Hello,

I'm here with new Scenario, threshold limit, where list the rows present a table, the maximum limit is 5000 but in my data I have almost 8000 data kindly help how to fix it.

Regards

Ganesh G V

[ManishSolanki]

Hi @GaneshaGV123 

You need to turn on the pagination setting and set the item threshold for Get items action.

Click on ellipses menu (...) and select "Settings" from the sub menu:

Next, turn on pagination and set the threshold value as per your need:

Click Done and retest the flow.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[GaneshaGV123]

Hello,

I have tried the same but Iam getting an error as per the attached.

[ManishSolanki]

In the previous reply, I have mistakenly shared the setting for SharePoint "Get list" action. For "List rows present in a table" action, the max limit is set to 5000.

Thanks

[GaneshaGV123]

Hi Manish,

Do we have any option to overcome from that threshold limit? in List the rows in a table? 

Really Appreciate your help here.

Regards

Ganesh G V

[GaneshaGV123]

Hello,

I Want to update the Status Column in a Excel Table as Present if the First in is Not blank Using powerautomate,  I have the data as per the below format If First in is not blank then update the status as "Present" if Blank leave it blank. 

Can we add Pivot table using powerautomate ?

Regards

Ganesh G V