{"id":9939,"date":"2023-04-04T13:52:26","date_gmt":"2023-04-04T18:52:26","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=9939"},"modified":"2023-04-07T11:32:18","modified_gmt":"2023-04-07T16:32:18","slug":"using-excel-to-turn-week-of-month-and-day-of-week-into-actual-dates","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=9939","title":{"rendered":"Using Excel to turn week of month and day of week into actual dates"},"content":{"rendered":"

Our patching schedules are algorithmic \u2013 the 1st<\/sup> Tuesday of the month, the 3rd<\/sup> Wednesday of the month, etc. But that\u2019s not particularly useful for notifying end users or for us to verify functionality after patching.<\/p>\n

\"Graphical<\/p>\n

Long term, I think we can pull the source data from a database and create appointment items each month for whatever list of servers will be patched that month based on a relative date<\/a> (so no one has to add new servers or remove decommissioned servers). But, short term? I really wanted a way to see what date<\/em> a server would be patched. So I created a but of a convoluted spreadsheet to produce this information based on a list of servers and patching schedule patterns.<\/p>\n

There are two \u201cextra\u201d tabs used \u2013 \u201cDates\u201d used to say what month and year I want the patching dates for<\/p>\n

\"Graphical<\/p>\n

And \u201cServerData\u201d which provides a cross-reference between the server names and a useful description.<\/p>\n

\"Graphical<\/p>\n

There are then a series of formulae used to add columns to our source data. First, the \u201cFunction\u201d is populated in column G with a VLOOKUP =VLOOKUP(B2,ServerData!A:B,2,FALSE)<\/p>\n

Columns I and J break the \u201c1st<\/sup> Saturday\u201d into the two components \u2013 week of month and day of week \u2013<\/p>\n

I =LEFT(C2,3)
\nJ =RIGHT(C2,LEN(C2)-4)<\/p>\n

Columns K and L then map these components into numeric values I can use in a formula:<\/p>\n

K =IF(I2=”1st”,1,IF(I2=”2nd”,2,IF(I2=”3rd”,3,IF(I2=”4th”,4,”Unscheduled”))))
\nL =IF(J2=”Sunday”,1,IF(J2=”Monday”,2,IF(J2=”Tuesday”,3,IF(J2=”Wednesday”,4,IF(J2=”Thursday”,5,IF(J2=”Friday”,6,IF(J2=”Saturday”,7,”Unscheduled”)))))))<\/p>\n

And finally a formula in column H that turns the week of month and day of week values into an actual date within the month and year on the \u201cDates\u201d tab:<\/p>\n

H =DATE(Dates!$B$2,Dates!$A$2,1+7*K2)-WEEKDAY(DATE(Dates!$B$2,Dates!$A$2,8-L2))<\/p>\n

Voila \u2013 I have a spreadsheet that says we should expect to see this specific list of servers being patched tonight.<\/p>\n

\"Graphical<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Our patching schedules are algorithmic \u2013 the 1st Tuesday of the month, the 3rd Wednesday of the month, etc. But that\u2019s not particularly useful for notifying end users or for us to verify functionality after patching. Long term, I think we can pull the source data from a database and create appointment items each month …<\/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":[708,710,709,691,729,731,265,705],"class_list":["post-9939","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-excel","tag-excel-functions","tag-microsoft-excel","tag-microsoft-office","tag-microsoft-office-365","tag-ms-excel","tag-office-365","tag-office365"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/9939","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=9939"}],"version-history":[{"count":2,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/9939\/revisions"}],"predecessor-version":[{"id":9945,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/9939\/revisions\/9945"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=9939"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=9939"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=9939"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}