Contents
Production calendar, i.e. a list of dates, where all official working days and holidays are marked accordingly – an absolutely necessary thing for any user of Microsoft Excel. In practice, you can not do without it:
- in accounting calculations (salary, length of service, vacations …)
- in logistics – for the correct determination of delivery times, taking into account weekends and holidays (remember the classic “come on after the holidays?”)
- in project management – for the correct estimation of terms, taking into account, again, working-non-working days
- any use of functions like WORKDAY (WORKDAY) or PURE WORKERS (NETWORKDAYS), because they require a list of holidays as an argument
- when using Time Intelligence functions (like TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, etc.) in Power Pivot and Power BI
- … etc. etc. – lots of examples.
It is easier for those who work in corporate ERP systems such as 1C or SAP, as the production calendar is built into them. But what about Excel users?
You can, of course, keep such a calendar manually. But then you will have to update it at least once a year (or even more often, as in the “jolly” 2020), carefully entering all weekends, transfers and non-working days invented by our government. And then repeat this procedure every next year. Boredom.
How about going a little crazy and making a “perpetual” factory calendar in Excel? One that updates itself, takes data from the Internet and always generates an up-to-date list of non-working days for subsequent use in any calculations? Tempting?
To do this, in fact, is not at all difficult.
Data source
The main question is where to get the data? In search of a suitable source, I went through several options:
- The original decrees are published on the government’s website in PDF format (here, one of them, for example) and disappear immediately – useful information cannot be pulled out of them.
- A tempting option, at first glance, seemed to be the “Open Data Portal of the Federation”, where there is a corresponding data set, but, upon closer examination, everything turned out to be sad. The site is terribly inconvenient for importing into Excel, technical support does not respond (self-isolated?), and the data itself is outdated there for a long time – the production calendar for 2020 was last updated in November 2019 (disgrace!) and, of course, does not contain our “coronavirus ‘ and the ‘voting’ weekend of 2020, for example.
Disillusioned with official sources, I began to dig unofficial ones. There are many of them on the Internet, but most of them, again, are completely unsuitable for importing into Excel and give out a production calendar in the form of beautiful pictures. But it’s not for us to hang it on the wall, right?
And in the process of searching, a wonderful thing was accidentally discovered – the site http://xmlcalendar.ru/
Without unnecessary “frills”, a simple, light and fast site, sharpened for one task – to give everyone a production calendar for the desired year in XML format. Excellent!
If, suddenly, you are not in the know, then XML is a text format with content marked up with special
Just in case, I contacted the authors of the site and they confirmed that the site has existed for 7 years, the data on it is constantly updated (they even have a branch on github for this) and they are not going to close it. And I don’t mind at all that you and I load data from it for any of our projects and calculations in Excel. Is free. It’s nice to know that there are still people like this! Respect!
It remains to load this data into Excel using the Power Query add-in (for versions of Excel 2010-2013 it can be downloaded for free from the Microsoft website, and in versions of Excel 2016 and newer it is already built-in by default).
The logic of actions will be as follows:
- We make a request to download data from the site for any one year
- Turning our request into a function
- We apply this function to the list of all available years, starting from 2013 and up to the current year – and we get a “perpetual” production calendar with automatic updating. Voila!
Step 1. Import a calendar for one year
First, load the production calendar for any one year, for example, for 2020. To do this, in Excel, go to the tab Data (or Power Queryif you installed it as a separate add-on) and select From the Internet (From Web). In the window that opens, paste the link to the corresponding year, copied from the site:
After clicking on OK a preview window appears, in which you need to click the button Convert Data (Transform data) or To change the data (Edit data) and we will get to the Power Query query editor window, where we will continue working with the data:
Immediately you can safely delete in the right panel Request Parameters (Query settings) step modified type (Changed Type) We don’t need him.
The table in the holidays column contains codes and descriptions of non-working days – you can see its contents by “falling through” it twice by clicking on the green word Table:
To go back, you will have to delete in the right panel all the steps that have appeared back to Source (Source).
The second table, which can be accessed in a similar way, contains exactly what we need – the dates of all non-working days:
It remains to process this plate, namely:
1. Filter only holiday dates (i.e. ones) by the second column Attribute:t
2. Delete all columns except the first – by right-clicking on the heading of the first column and selecting the command Delete other columns (Remove Other Columns):
3. Split first column by dot separately for month and day with command Split Column – By Delimiter tab Transformation (Transform — Split column — By delimiter):
4. And finally create a calculated column with normal dates. To do this, on the tab Adding a column click on the button Custom column (Add Column — Custom Column) and enter the following formula in the window that appears:
=#dated(2020, [#»Attribute:d.1″], [#»Attribute:d.2″])
Here, the #date operator has three arguments: year, month, and day, respectively. After clicking on OK we get the required column with normal weekend dates, and delete the remaining columns as in step 2
Step 2. Turning the request into a function
Our next task is to convert the query created for 2020 into a universal function for any year (the year number will be its argument). To do this, we do the following:
1. Expanding (if not already expanded) the panel Inquiries (Queries) on the left in the Power Query window:
2. After converting the request to a function, the ability to see the steps that make up the request and easily edit them, unfortunately, disappears. Therefore, it makes sense to make a copy of our request and frolic already with it, and leave the original in reserve. To do this, right-click in the left pane on our calendar request and select the Duplicate command.
Right-clicking again on the resulting copy of calendar(2) will select the command Rename (Rename) and enter a new name – let it be, for example, fxYear:
3. We open the query source code in the internal Power Query language (it is succinctly called “M”) using the command Advanced Editor tab Review(View — Advanced Editor) and make small changes there to turn our request into a function for any year.
It was:
After:
If you are interested in the details, then here:
- (year as number)=> – we declare that our function will have one numeric argument – a variable year
- Pasting the variable year to web link in step Source. Since Power Query does not allow you to glue numbers and text, we convert the year number to text on the fly using the function Number.ToText
- We substitute the year variable for 2020 in the penultimate step #”Added custom object«, where we formed the date from the fragments.
After clicking on Finish our request becomes a function:
Step 3. Import calendars for all years
The last thing left is to make the last main query, which will upload data for all available years and add all the received holiday dates into one table. For this:
1. We click in the left query panel in a gray empty space with the right mouse button and select sequentially New request – Other sources – Empty request (New Query — From other sources — Blank query):
2. We need to generate a list of all years for which we will request calendars, i.e. 2013, 2014 … 2020. To do this, in the formula bar of the empty query that appears, enter the command:
Structure:
={NumberA..NumberB}
… in Power Query generates a list of integers from A to B. For example, the expression
={1..5}
… would produce a list of 1,2,3,4,5.
Well, in order not to be tied rigidly to 2020, we use the function DateTime.LocalNow() – analogue of the Excel function TODAY (TODAY) in Power Query – and extract from it, in turn, the current year by the function Date.Year.
3. The resulting set of years, although it looks quite adequate, is not a table for Power Query, but a special object – list (List). But converting it to a table is not a problem: just click the button To table (To Table) in the upper left corner:
4. Finish line! Applying the function we created earlier fxYear to the resulting list of years. To do this, on the tab Adding a column press the button Call custom function (Add Column — Invoke Custom Function) and set its only argument – the column Column1 over the years:
After clicking on OK our function fxYear the import will work in turn for each year and we will get a column where each cell will contain a table with the dates of non-working days (the contents of the table are clearly visible if you click in the background of the cell next to the word Table):
It remains to expand the contents of nested tables by clicking on the icon with double arrows in the column header Dates (tick Use original column name as prefix it can be removed):
… and after clicking on OK we get what we wanted – a list of all holidays from 2013 to the current year:
The first, already unnecessary column, can be deleted, and for the second, set the data type date (Date) in the dropdown list in the column heading:
The query itself can be renamed something more meaningful than Request1 and then upload the results to the sheet in the form of a dynamic “smart” table using the command close and download tab Home (Home — Close & Load):
You can update the created calendar in the future by right-clicking on the table or query in the right pane through the command Update & Save. Or use the button Refresh all tab Data (Date — Refresh All) or keyboard shortcut Ctrl+Alt+F5.
That’s all.
Now you never again need to waste time and thought-fuel searching for and updating the list of holidays – now you have a “perpetual” production calendar. In any case, as long as the authors of the site http://xmlcalendar.ru/ support their offspring, which, I hope, will be for a very, very long time (thanks to them again!).
- Import bitcoin rate to excel from the internet via Power Query
- Finding the next business day using the WORKDAY function
- Finding the intersection of date intervals