Unanswered

Eliminate Dates with letters i.e. 21st

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I have some data coming in from a real estate contract where the close date is in the following format: November 11th, 2021.

I need it as 3 different variables: Month in 2 digit numeric format, day in 2 digit numeric format, year in 4 digit numeric format. ex: 11/11/2021

So far I've used an IF statement for a couple of months such as: IF X= December, then month = 12.

Before I go thru the whole year of months and then the whole month of days doing IF statements, is there a better way?

Categories:

Power Automate Desktop

I have the same question (0)

All responses (11)

Answers (0)

Sort by

MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Replace text on for each representation (nd, th, rd, st), then remove spaces, convert from text to date, and then you can convert the textdate format to anything you want. Copied flow text below. If you copy the text and paste it in your flow, it will show up with the commands and then you can change them to fit your particular model, so you don't have to rebuild them. If there is only 1 digit for the day (ex. November 2nd, 2021), the 3 if's steal the right 6, and make sure it starts with a 1, 2, or 3...if it doesn't (i.e. the 0 is missing), then it replaces the last 5 with "0" and the same last 5. Good luck!

SET NewVar5 TO $'''November 2nd, 2021'''
Text.Replace Text: NewVar5 TextToFind: $'''th, ''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> Replaced1
Text.Replace Text: Replaced1 TextToFind: $'''nd, ''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> Replaced2
Text.Replace Text: Replaced2 TextToFind: $'''rd, ''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> Replaced3
Text.Replace Text: Replaced3 TextToFind: $'''st, ''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> Replaced4
Text.Replace Text: Replaced4 TextToFind: $'''%' '%''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> Replaced5
Text.GetSubtext Text: Replaced5 CharacterPosition: Replaced5.Length - 6 NumberOfChars: 1 Subtext=> Subtext
IF Subtext <> 1 THEN
IF Subtext <> 2 THEN
IF Subtext <> 3 THEN
Text.GetSubtext Text: Replaced5 CharacterPosition: Replaced5.Length - 5 NumberOfChars: 5 Subtext=> SubtextLastFive
# add the leading 0 to the numeric day
Text.Replace Text: Replaced5 TextToFind: SubtextLastFive IsRegEx: False IgnoreCase: False ReplaceWith: $'''0%SubtextLastFive%''' ActivateEscapeSequences: False Result=> Replaced5
END
END
END
Text.ToDateTimeCustomFormat Text: Replaced5 CustomFormat: $'''MMMMdyyyy''' DateTime=> TextAsDateTime
Text.FromCustomDateTime DateTime: TextAsDateTime CustomFormat: $'''MM/dd/yyyy''' Result=> FormattedDateTime
Display.ShowMessage Title: $'''Result:''' Message: FormattedDateTime Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed5

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Etignor1

There are multiples options, one of them is below.

The Input given and the output in message box after running the flow.

Copy/paste the below code in a new PAD window, run the flow and check the output.

/# Only nd, rd, st, th will be present from 1st to 31st. Remove them as they are not converted. #/ Text.Replace Text: InputDate TextToFind: $'''st''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> AfterReplace Text.Replace Text: AfterReplace TextToFind: $'''nd''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> AfterReplace Text.Replace Text: AfterReplace TextToFind: $'''rd''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> AfterReplace Text.Replace Text: AfterReplace TextToFind: $'''th''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> AfterReplace Text.FromDateTime DateTime: AfterReplace StandardFormat: Text.WellKnownDateTimeFormat.ShortDate Result=> NewDate Text.FromCustomDateTime DateTime: NewDate CustomFormat: $'''dd''' Result=> varDay Text.FromCustomDateTime DateTime: NewDate CustomFormat: $'''MM''' Result=> varMonth Text.FromCustomDateTime DateTime: NewDate CustomFormat: $'''yyyy''' Result=> varYear Display.ShowMessage Message: $'''The day is: %varDay% The month is: %varMonth% The year is: %varYear%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed Display.ShowMessage Message: $'''The new format is: %varDay%/%varMonth%/%varYear%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

Edit: for the above code to run add an Input variable called InputDate and give it the desired input date value.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I cut and pasted the info and got this result

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Etignor1

What does the error on the red mark show for each of them?

Also have you created the InputDate variable mentioned in the last line of my post above? See the variable on the right hand side of the screenshot.

You can make changes to your flow as below.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Here's the screenshots including where I added the input variable

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

UPDATE: I added this date as in your example to my input variable put the errors are still there and they are still the same.

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Which version of PAD are you using?
One difference spotted is as below.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

It's showing that it's the most up to date version.

Also, it won't allow me to change that setting even if I delete it and try to add a new one. It is defaulted to String.

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

We are probably on different versions. You should be able to read my code. First one is Get Subtext.

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

@Etignor1 That's strange, it is also showing me as the latest version.

Was this reply helpful? Yes No