{"id":890,"date":"2019-09-03T13:03:19","date_gmt":"2019-09-03T12:03:19","guid":{"rendered":"http:\/\/wobable.com\/?p=890"},"modified":"2019-09-03T13:03:25","modified_gmt":"2019-09-03T12:03:25","slug":"facebook-data-export-date-conversion","status":"publish","type":"post","link":"https:\/\/wobable.com\/?p=890","title":{"rendered":"Facebook data export date conversion"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">One of the things that I have to do regularly is export data from Facebook pages of Facebook posts.<br><br>I import the data to Excel to clean it and analyse it.<br><br>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).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The following piece of excel code will convert all the dates into UK dates (which are actually dates).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">=IF(ISNUMBER(G3),DATE(YEAR(G3),DAY(G3),MONTH(G3))+TIME(HOUR(G3),MINUTE(G3),SECOND(G3)),IF(RIGHT(G3,2)=&#8221;PM&#8221;,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))))<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">I add this to a new column next to Column G (which contains the &#8220;posted&#8221; details) and it converts all the dates for me.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/wobable.com\/?p=890\"> <span class=\"screen-reader-text\">Facebook data export date conversion<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[1],"tags":[],"class_list":["post-890","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/p2c0fT-em","jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/wobable.com\/index.php?rest_route=\/wp\/v2\/posts\/890","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wobable.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wobable.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wobable.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wobable.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=890"}],"version-history":[{"count":1,"href":"https:\/\/wobable.com\/index.php?rest_route=\/wp\/v2\/posts\/890\/revisions"}],"predecessor-version":[{"id":891,"href":"https:\/\/wobable.com\/index.php?rest_route=\/wp\/v2\/posts\/890\/revisions\/891"}],"wp:attachment":[{"href":"https:\/\/wobable.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=890"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wobable.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=890"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wobable.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=890"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}