How to recognize Gmail DateTime format?

Help StudioX
, , , , ,
1

I want to convert a datetime from gmail, but the format I’m using to convert doesn’t work with all emails.
I am using the Use Gmail activity and I created a variable that receives the datatime and another variable to convert this value to the format “MM/dd/yyyy”.
In this variable I structured like this: DateTime.ParseExact(DateReceived, “ddd, dd MMM yyyy HH:mm:ss ‘+0000 (UTC)’”, CultureInfo.InvariantCulture).ToShortDateString

This structure does not work when the email date is in this format:
“Thu, 2 Dec 2021 10:30:00 -0300”

It works when the format is like this “Thu, 02 Dec 2021 10:30:00 +0000 (UTC)”

Does anyone know how to solve this problem?

2

Hello Vinicius,

You have it almost 100% right. About the day, think you just need to remove a ‘d’ from the expression, since the day in the date doesn’t contain a leading zero.
For the timezone, it’s hard to say, but you can try to exclude that part.
So, the new expression would be DateTime.ParseExact(DateReceived, “ddd, d MMM yyyy HH:mm:ss”, CultureInfo.InvariantCulture).ToShortDateString

3

Hello Gil,

I made some changes and used the expression in this format: DateTime.ParseExact(DateReceived, “ddd, d MMM yyyy H:mm:ss zzzz”, system.Globalization.CultureInfo.InvariantCulture).ToShortDateString
But I still haven’t been able to solve the TimeZone. I can only solve this problem when I include the TimeZone type as UTC, but I have idenfied other formats such as PST and BRST and I don’t know how to structure it to identify and convert any of these formats.

4

Hi,

I have idenfied other formats such as PST and BRST and I don’t know how to structure it to identify and convert any of these formats.

Do you mean want to parse datetime string which has only timezone abbreviations like “Thu, 02 Dec 2021 10:30:00 PST” ? If so, it’s difficult because timezone abbreviations are not unique. See the following document in detail. (For example, CST means -0600, +0800 or -0500.)

If timezone in the format is limited and they are unique each other, we can have conversion table in advance, then replace it to “+0000” format, for example.

Regards,

5

Hello Yoichi

I would like to abbreviate any type of date format, regardless of timezone, I want to be able to recognize and convert to this format: MM/dd/yyyy.
Since I’m pulling these dates from Gmail, there isn’t a date pattern in each email unfortunately. Each email can contain a different timezone.

6

Hi,

Can your share specific examples (input and expected result)?

If we don’t need timezone-abbreviation to parse to DateTime type, we will be able to remove it using regex etc in advance.

Regards,

7

These are some of the formats that appear when I perform the date extraction from emails:

Thu, 2 Dec 2021 10:30:00 -0300
Wed, 01 Dec 2021 12:00:39 +0000 (UTC)
Fri, 3 Dec 2021 07:21:42 -0600 (CST)
Mon, 29 Nov 2021 03:41:46 -0800 (PST)
Sun, 28 Nov 2021 07:20:16 -0200 (BRST)

And this is the final format with just the month, day and year and without the timezone abbreviation:

MM/dd/yyyy

8

Hi,

Can you try the following expression?

DateTime.ParseExact(System.Text.RegularExpressions.Regex.Replace(yourString,"\s*\(\w+\)$",""), "ddd, d MMM yyyy HH:mm:ss K", System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy")

Regards,

9

Hi,
It works!
I ran the tests and it’s converting without a problem with most emails.
But there was a case that it generated an error when trying to convert, probably because of the original date format, which is the one that doesn’t contain the abbreviation of the day of the week:
25 Nov 2021 12:40:57 -0300

It’s saying it’s not a valid datetime.

10

HI,

How about the following expression?

DateTime.ParseExact(System.Text.RegularExpressions.Regex.Match(yourString,"\d+\s+[A-Za-z]{3}\s+\d{4}\s+\d{1,2}:\d{1,2}:\d{1,2}\s+[-+]?[\d:]+").Value, "d MMM yyyy HH:mm:ss K", System.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy")

Regards,

11

Hi Yoichi,

With this new format, it’s working without any errors.
Thank you for your help.

Regards,

1 Like
12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.