Facebook data export date conversion

One of the things that I have to do regularly is export data from Facebook pages of Facebook posts.

I import the data to Excel to clean it and analyse it.

One of the frustrations with this is that some of the dates in the data source get formatted as US dates, and some of them get formatted as UK dates. The UK Dates are then converted to text format (for reasons that are not clear to me).

The following piece of excel code will convert all the dates into UK dates (which are actually dates).

=IF(ISNUMBER(G3),DATE(YEAR(G3),DAY(G3),MONTH(G3))+TIME(HOUR(G3),MINUTE(G3),SECOND(G3)),IF(RIGHT(G3,2)=”PM”,DATE(MID(G3,7,4),LEFT(G3,2),MID(G3,4,2))+TIME(MID(G3,12,2)+12,MID(G3,15,2),MID(G3,18,2)),DATE(MID(G3,7,4),LEFT(G3,2),MID(G3,4,2))+TIME(MID(G3,12,2),MID(G3,15,2),MID(G3,18,2))))

I add this to a new column next to Column G (which contains the “posted” details) and it converts all the dates for me.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

  

  

  

This site uses Akismet to reduce spam. Learn how your comment data is processed.