{"id":11271,"date":"2024-11-29T12:22:26","date_gmt":"2024-11-29T17:22:26","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=11271"},"modified":"2024-11-29T12:22:27","modified_gmt":"2024-11-29T17:22:27","slug":"ad-passwordlastset-times","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=11271","title":{"rendered":"AD passwordLastSet Times"},"content":{"rendered":"\n<p>I&#8217;m doing &#8220;stuff&#8221; in AD again, and have <em>again<\/em> come across Microsoft&#8217;s wild &#8220;nanoseconds elapsed since 1601&#8221; reference time. AKA &#8220;Windows file time&#8221;.  In previous experience, I was just looking to calculate deltas (how long since that password was set) so figuring out <em>now<\/em>, subtracting <em>then<\/em>, and converting nanoseconds elapsed into something a little less specific (days, for example) was fine. Today, though, I need to display a human readable date and time in Excel. Excel, which has its own peculiar way of storing date time values. Fortunately, I happened across a formula that works<\/p>\n\n\n\n<p>=((C2-116444736000000000)\/864000000000)+DATE(1970,1,1)<\/p>\n\n\n\n<p>Voila!<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2024\/11\/ad_pwd_last_set.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"341\" height=\"464\" src=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2024\/11\/ad_pwd_last_set.jpg\" alt=\"\" class=\"wp-image-11272\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2024\/11\/ad_pwd_last_set.jpg 341w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2024\/11\/ad_pwd_last_set-220x300.jpg 220w\" sizes=\"auto, (max-width: 341px) 100vw, 341px\" \/><\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;m doing &#8220;stuff&#8221; in AD again, and have again come across Microsoft&#8217;s wild &#8220;nanoseconds elapsed since 1601&#8221; reference time. AKA &#8220;Windows file time&#8221;. In previous experience, I was just looking to calculate deltas (how long since that password was set) so figuring out now, subtracting then, and converting nanoseconds elapsed into something a little less &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30],"tags":[708,709,2068],"class_list":["post-11271","post","type-post","status-publish","format-standard","hentry","category-system-administration","tag-excel","tag-microsoft-excel","tag-windows-filetime"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/11271","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=11271"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/11271\/revisions"}],"predecessor-version":[{"id":11273,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/11271\/revisions\/11273"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11271"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11271"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11271"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}