0

My formula contains an RFC822 pubDate, e.g. as pulled from Google's RSS2 Feed.

=DATEVALUE("Thu, 01 Oct 2020 10:00:00 +0000")

but it results in

Error
DATEVALUE parameter 'Thu, 01 Oct 2020 10:00:00 +0000' cannot be parsed to date/time.
  • According to this, my locale should be set, but changing that can have other consequences in my sheet, which I do not want (FYI my current sheet locale is United States)
  • According to this stackexchange question, there is apparently no support for that

Is there a workaround to get my manually entered RFC822 dates recognized by DATEVALUE formula Google Sheets (I need this particular formula to perform various operations, so other than re-entering dates in compliant format, is there anything else I can try?

1

I did it by using a REGEXREPLACE.

Example: A1 contains Thu, 01 Oct 2020 10:00:00 +0000

In B1:

=DATEVALUE(TRIM(REGEXREPLACE(A1,"^(Sun|Mon|Tue|Wed|Thu|Fri|Sat)\,(.*)\s+\+?[0-9]{4}.*$","$2")))

B1 now shows 44104.

| improve this answer | |
0

You can also use the following simpler formula

=DATEVALUE(REGEXREPLACE(A2,"\+.*",""))

RFC822 dates to DATEVALUE in Google Sheets

| improve this answer | |
  • Have you tried the given solution? Did it work for you? If not we could try finding out why. If yes, please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos Oct 9 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.