{"id":7728,"date":"2021-04-26T15:27:13","date_gmt":"2021-04-26T20:27:13","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=7728"},"modified":"2021-04-26T15:27:13","modified_gmt":"2021-04-26T20:27:13","slug":"excel-converting-unix-timestamp-to-human-readable-datetime","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=7728","title":{"rendered":"Excel &#8211; Converting Unix Timestamp to Human Readable Date(time)"},"content":{"rendered":"<p>You can use the formula <em>=(B2\/86400)+DATE(1970,1,1)<\/em> to convert a unix epoch time to a human readable date (or date time). In my case, I have the unix timestamp in microseconds so I&#8217;ve got to divide by 86400000. The value you get is a not-so-meaningful float &#8230; but that&#8217;s actually a date.<\/p>\n<p><a href=\"https:\/\/www.rushworth.us\/lisa\/?attachment_id=7729\" rel=\"attachment wp-att-7729\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-7729\" src=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt1-1024x140.png\" alt=\"\" width=\"960\" height=\"131\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt1-1024x140.png 1024w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt1-300x41.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt1-768x105.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt1-750x102.png 750w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt1.png 1105w\" sizes=\"auto, (max-width: 960px) 100vw, 960px\" \/><\/a><\/p>\n<p>Select a date format to display the value as a date<\/p>\n<p><a href=\"https:\/\/www.rushworth.us\/lisa\/?attachment_id=7730\" rel=\"attachment wp-att-7730\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-7730\" src=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt2-1024x227.png\" alt=\"\" width=\"960\" height=\"213\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt2-1024x227.png 1024w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt2-300x67.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt2-768x170.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt2-750x166.png 750w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt2.png 1118w\" sizes=\"auto, (max-width: 960px) 100vw, 960px\" \/><\/a><\/p>\n<p>Or chose a custom format and use something like &#8220;m\/d\/yyyy hh:mm&#8221; to display a date and time.<\/p>\n<p><a href=\"https:\/\/www.rushworth.us\/lisa\/?attachment_id=7731\" rel=\"attachment wp-att-7731\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-7731\" src=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt3.png\" alt=\"\" width=\"835\" height=\"605\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt3.png 835w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt3-300x217.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt3-768x556.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/04\/ExcelUnixTimestampToHumanReadableDatePt3-750x543.png 750w\" sizes=\"auto, (max-width: 835px) 100vw, 835px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>You can use the formula =(B2\/86400)+DATE(1970,1,1) to convert a unix epoch time to a human readable date (or date time). In my case, I have the unix timestamp in microseconds so I&#8217;ve got to divide by 86400000. The value you get is a not-so-meaningful float &#8230; but that&#8217;s actually a date. Select a date format &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[677],"tags":[1342,708,1341],"class_list":["post-7728","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-datetime","tag-excel","tag-unix-epoch"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/7728","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7728"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/7728\/revisions"}],"predecessor-version":[{"id":7732,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/7728\/revisions\/7732"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7728"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7728"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7728"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}