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.

1 thought on “Facebook data export date conversion”

  1. Andrew Fielding

    Another odd thing is daylight savings time.

    In the USA it tends to change a week before the UK.

    To account for that week in March, use the following code:

    =IF(AND(G3>DATE(2021,3,14),G3<DATE(2021,3,27)),TEXT(G3+TIME(7,0,0),"DD/MM/YYYY HH:MM"),TEXT(G3+TIME(8,0,0),"DD/MM/YYYY HH:MM"))

Leave a Comment

Your email address will not be published. Required fields are marked *

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