Notifications
Q&A

How to convert an RFC822 timestamp to a date index number?

+1
−1

A self-answered question - How to get DATEVALUE to recognize RSS 2 pubDate (RFC822)? - written by spcsLrg on Web Applications Stack Exchange offers this formula:

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

as a way to convert Thu, 01 Oct 2020 10:00:00 +0000 to a date value.

Is there a simpler way?

Why should this post be closed?

0 comments

1 answer

+2
−0

Yes, only the sixth to eleventh characters are required and these may be extracted with the MID function. MID is a string function that returns Text but a double unary operator should trigger Sheets' automatic date detection and display as 44105 when in default format:

=--mid(A1,6,11)

The same formula works in Excel and LibreOffice Calc, given suitable delimiters.

0 comments

Sign up to answer this question »