Answered

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

(0) Share

Report

Posted on by KyotoUK

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.

Categories:

General topics

I have the same question (0)

All responses (8)

Answers (2)

Verified answer

Jcook 7,781 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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

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

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
KyotoUK 10 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

Jcook 7,781 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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

to this:

Was this reply helpful? Yes No
KyotoUK 10 on at

Like (0)

Report
Copy link

Link copied!

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

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

Rich

Was this reply helpful? Yes No
Jcook 7,781 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
KyotoUK 10 on at

Like (0)

Report
Copy link

Link copied!

@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

Was this reply helpful? Yes No
Jcook 7,781 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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-p/703482#M208

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

Was this reply helpful? Yes No