cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Email to Excel Date and Time Format and delete all rows older than 28

Another newbie here, we have requested that staff send an email to a mailbox with the subject SignIn or SignOut so that we know who is in the office at any given time. In an emergency this allows us to see who might be stuck in the office in the event of a fire evacuation. This is a short term solution...

 

I have it all working with Automate and it adds a row in with the persons name and date/time they sent it. The format is a little hard to read and i can't seem to set it to dd/mm/yy hh/mm. It currently looks like 2020-09-24T21:09:46+00:00

 

The other part is that i only want to store this data for 22 days, how can i get automate to run each night and delete all entries that are 22days or more.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Email to Excel Date and Time Format and delete all rows older than 28

Hello @KyotoUK 

 

For converting the time. You can use the action Convert time zone

Change your Source and Destination times to meet your own.

For the Format string, either find a good format you like. Or select the Enter custom value and choose your own format (must meet ISO)

yy = year

MM = month

dd = day

hh = hour

mm = minutes

ss = seconds

Jcook_0-1600987993121.png

 

for your second question regarding deleting items older than 22 days. You will have to have a second Flow which checks the data (SharePoint or Excel) where ever you are storing the data. And use the same Convert time actions to get current date, and the use an If condition to check if the date column is greater than 21 days.

 

This post explains lots on this:

https://powerusers.microsoft.com/t5/Building-Flows/Delete-Rows-older-than-7-day-s/td-p/295135

 

If you need help with second Flow. Please provide more details:

- Where data is being stored,

- field name for date

 

 

 





Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




View solution in original post

Highlighted
Super User III
Super User III

Re: Email to Excel Date and Time Format and delete all rows older than 28

Hi @KyotoUK 

 

In your picture you're referencing the Received time from your trigger still.

 

You will want to use the Converted time from the Convert time zone action in your Received time fields

 

You want to change your dynamic content from

Jcook_1-1601311501172.png

 

to this:

 

Jcook_0-1601311462142.png

 





Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




View solution in original post

8 REPLIES 8
Highlighted
Super User III
Super User III

Re: Email to Excel Date and Time Format and delete all rows older than 28

Hello @KyotoUK 

 

For converting the time. You can use the action Convert time zone

Change your Source and Destination times to meet your own.

For the Format string, either find a good format you like. Or select the Enter custom value and choose your own format (must meet ISO)

yy = year

MM = month

dd = day

hh = hour

mm = minutes

ss = seconds

Jcook_0-1600987993121.png

 

for your second question regarding deleting items older than 22 days. You will have to have a second Flow which checks the data (SharePoint or Excel) where ever you are storing the data. And use the same Convert time actions to get current date, and the use an If condition to check if the date column is greater than 21 days.

 

This post explains lots on this:

https://powerusers.microsoft.com/t5/Building-Flows/Delete-Rows-older-than-7-day-s/td-p/295135

 

If you need help with second Flow. Please provide more details:

- Where data is being stored,

- field name for date

 

 

 





Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




View solution in original post

Highlighted
Dual Super User III
Dual Super User III

Re: Email to Excel Date and Time Format and delete all rows older than 28

Hi @KyotoUK 

 

While getting date time type data from excel, note that it works differently in Power Automate. If the column is of type date time, it will return a number and not the date time formatted string. You can check this blog post on how you can  convert that number to an appropriately formatted string and use it in Power Automate for filtering the data. 

 

https://www.bythedevs.com/post/working-with-date-time-data-of-excel-in-power-automate

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Highlighted
Regular Visitor

Re: Email to Excel Date and Time Format and delete all rows older than 28

Thanks for the prompt reply, i have added the flow item in but i can't get the format or time to change. It's currently adding it as UTC but we are currently in BST or UTC+1 for the next few weeks.

 

My flow as it stands is currently.

 

https://1drv.ms/u/s!Akl5k-7z63Ylk0H4wVahiJyVCjlT?e=2vCOJR

 

@Jcook 

Highlighted
Super User III
Super User III

Re: Email to Excel Date and Time Format and delete all rows older than 28

Hi @KyotoUK 

 

In your picture you're referencing the Received time from your trigger still.

 

You will want to use the Converted time from the Convert time zone action in your Received time fields

 

You want to change your dynamic content from

Jcook_1-1601311501172.png

 

to this:

 

Jcook_0-1601311462142.png

 





Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




View solution in original post

Highlighted
Regular Visitor

Re: Email to Excel Date and Time Format and delete all rows older than 28

 Of course, it's so obvious now. Thanks again.

 

I'll take a look at the 28day thing tomorrow.

 

Rich

Highlighted
Super User III
Super User III

Re: Email to Excel Date and Time Format and delete all rows older than 28

@KyotoUK 

 

I can help build a POC for you later in a couple hours. I can post a Flow that you can download and use to delete items from X amount of days.





Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




Highlighted
Regular Visitor

Re: Email to Excel Date and Time Format and delete all rows older than 28

@Jcook 

 

That would be great, saves me asking more questions about it 🙂

 

I went with a custom string of ddd dd/mm/yy - HH:mm in the end.

 

Rich

Highlighted
Super User III
Super User III

Re: Email to Excel Date and Time Format and delete all rows older than 28

Hello @KyotoUK 

 

For your custom format for date, dont forget that months is MM not mm.

 

Here is a link to the Flow to delete old records based on X amount of days:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Delete-Excel-Rows-X-Amount-of-Days-Old/m...

You will be able to download the Flow, follow the steps to configure in your Tenant.





Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Users online (6,445)