Need help extracting date and time from an email using time format dd.MM.yyyy HH:mm:ss

(1) Share

Report

Posted on by JU-11101126-0

GOAL: Power Automate Flow/Automation: Email to Calendar event.
The idea for this automation is to receive maintenance announcements by email, extract the date and starting time from the body of the email in question, and then add that email as a calendar event. Calendar events date and starting time should be the same as mentioned in the extracted date and time.

Locale:
Country: Finland
Time Zone: UTC+02:00
Common date format: d.M.yyyy or dd.MM.yyyy (example: 7.10.2024)
Common time format: HH:MM:SS   (example: 14:05:22)
ISO 3166 Country Code: FIN
ISO639-2 Country Code: FI
Country: Finland
ISO 3166 Country Code: fin
ISO639-2 Lang: fi

Attempts have been made using email in HTML form and converting HTML to plain text.

I have tried to extract date and time separately. In my flow extracting date is the first "parsing" step and extracting time second. Flow fails every time on Extract Date

Flow outline: (just that you understand this issue)

1. Trigger - When a new email arrives (V3)

Include Attachments: Yes
    Importance: Any
    Only with Attachments: No
     Folder: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

2. Initialize Variable:
    Name: EmailBody
    Type: String
    Value: Body= triggerOutputs()?['body/body']

3. Extract Date (compose)

Expression: substring(triggerOutputs()?['body'], indexOf(triggerOutputs()?['body'], '">') + 2, 10) (FAILED)

****

Tests, HTML to Text conversion (after When a new email arrives (V3))

Action: Add the “HTML to Text” action.
Content: triggerOutputs()?['body/body']

Test 1 (Extract Date)
Substring(variables('PlainTextBody'), indexOf(variables('PlainTextBody'), 'd.M.yyyy'), 10)

Extract Date
Result: Failure/error
Error: InvalidTemplate
Unable to process template language expressions in action 'Extract_Date' inputs at line '0' and column '0': 'The template language function 'substring' parameter is out of range: 'start index' must be non-negative integer and should be less than the length of the string.

Test 2 (Extract Date)
substring(variables('PlainTextBody'), indexOf(variables('PlainTextBody'), 'dd.MM.yyyy'), 10)

Extract Date
Error: InvalidTemplate
Unable to process template language expressions in action 'Extract_Date' inputs at line '0' and column '0': 'The template language function 'substring' parameter is out of range: 'start index' must be non-negative integer and should be less than the length of the string.

Test 3 (Extract Date)
substring(variables('PlainTextBody'), indexOf(variables('PlainTextBody'), '\d{1,2}.\d{1,2}.\d{4}'))

Extract Date
Error: InvalidTemplate

Test 4 (Extract Date)

first(split(first(split(variables('PlainTextBody'), '\n')), ' '))

Extract_Date raw outputs is "Keskeytys"

"Keskeytys" is the first written word in email body

Because many newlines.

All failed

For example the current time here in Finland while writing this is 11.10.2024 15:08

Can you help PA n00b? Copilot makes me run in circles.

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

Michael E. Gernaey 53,442 Super User 2025 Season 2 on at

Like (0)

Report

What does the emails look like, as in actual examples?

I dont see anything listed as, "here is what it looks like", so no way I can guess at how to help sorry

Was this reply helpful? Yes No
JU-11101126-0 4 on at

Like (0)

Report

Hi,

This is getting very interesting and challenging. I built a flow, using Utility - Extract Text between Values.

Idea is to grab first of many or only date and time from the email body, in order to use those as date and starting time for a calendar event.

Only start time and date matters, i can add +2 hours to calendar events end time.

At this stage i have focused only to solve this date and time issue.

Challenge is to use local formatting, that is commonly used here in Finland.

Examples, Maintenance notification (date an time):

19.10.2024 20:00-22:00

Huoltokatko tapahtuu 19.10.2024 20:00 ja 22:00 välisenä aikana. (Maintenance occurs 19.10.2024 between 20:00 and 22:00 hours)

Huoltokatko alkaa 19.10.2024 20:00.

Same thing can be written in many ways. And the format differs per client/customer.

Tested this with an email with only 1 row of text.

HTML Body (partial):

style=\"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)\">19.10.2024 20:00</div><div

This might be something that has been previously overlooked developing this platform. This might be unsolvable.

Maybe somehow i can extract date and time from "\">19.10.2024 20:00</div>" , but that only works, if i am seraching for DD:MM:YYYY HH:MM.

I have tried multiple approaches and all have failed.

Flow, using Utility - Extract Text between Values.

Flow test results:

When a new email arrives (V3) OK
Initialize variable OK
Utility - Extract Text between Values OK
Extract Date, Error
Extract Time, skipped
Output Date, skipped
Output Time, skipped

Config

Was this reply helpful? Yes No