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).
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”
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"))