Problem I. 380. (September 2015)
I. 380. By using a spreadsheet application it is easy to determine the day of the week for a given date. However, many of these applications cannot interpret dates before 1900 January 1. To handle this situation we can use perpetual calendars that were developed in the pre-computer era. Our perpetual calendar has 3 auxiliary tables. The first auxiliary table (``Évek indexszámai'' in the example) assigns an integer (called index) to each year. The second table (``Hónapok kulcsszámai'' in the example) yields another number (called key) based on the index and the number of the month. Finally, by using the third table (``A kapott számértékhez rendelt nap'' in the example) we find the day of the week from the sum of the key and the number of the day of the month (in the examples, ``Héfő'' is Monday, and ``Szombat'' is Saturday). Let us consider 1848 March 15, for example. The index corresponding to 1848 is 45. The key corresponding to 45 and March is 2. Finally, looking up the sum \(\displaystyle 2+15\) in the third table we get Wednesday.
In this task you should create a spreadsheet to find the day of the week for a given date. The auxiliary tables are available to you in the (UTF-encoded and tabulator-separated) file tablak.txt.
1. Read the data file tablak.txt to a sheet beginning with cell A1, then save this sheet as oroknaptar in the default application file format.
2. Now you determine the index corresponding to the given year in two steps. First display-in the cells of row 36 and by using a formula-the row number in which the year (=``Év'') given in cell C1 appears for each of the columns of the auxiliary table B5:T35. If the year does not appear in a particular column, the corresponding cell in row 36 should contain a 0.
3. As a next step, display-in cell C2 and by using a function-the index of the year in cell C1. The index is found in column A5:A35; its row number is specified by the non-zero element of row 36.
4. Next determine the key (=``Kulcs'') and display it in cell E2. The key depends on the index computed above and on the number of the month (=``Hónap'') given in cell E1. The key should be determined by using the table A39:M53 and applying a function: columns of this auxiliary table correspond to the months (B39:M39), while rows correspond to the indices (A40:A53).
5. To determine the day (=``Nap''), display-in cell G2 and by using a formula-the sum of the key (E2) and the number of the day (G1).
6. In the last step, determine the name of the day. Locate the sum computed in G2 by using a formula in the auxiliary table A57:G63: the name of the day-to be displayed in cell I2-is found in the last column of this table in the row in which the sum appears.
7. Each of the 3 auxiliary tables (A5:T35, A39:M53, A57:G63) should be bordered by thin lines inside and thick lines outside. The content of the 3 cells specifying the date, and that of the cell containing the name of the day should appear in bold with light gray background.
8. By using conditional formatting with bold fonts and dark red color, you should highlight
\(\displaystyle a)\) the year in the range B5:T35 that appears in cell C1,
\(\displaystyle b)\) the month in the column header B39:M39 that appears in cell E1,
\(\displaystyle c)\) the index in the row header A40:A53 that appears in cell C2, and
\(\displaystyle d)\) the integer in the range B40:M53 that appears in cell G2 (as ``Számérték'').
Downloaded file: tablak.txt.
Sample:
You should submit your sheet and a short documentation in a compressed file i380.zip.
(10 pont)
Deadline expired on October 12, 2015.
Statistics:
13 students sent a solution. 10 points: Hamrik Szabin, Kelkó Balázs, Kovács 246 Benedek, Nagy Ábel, Németh 729 Gábor, Olexó Gergely, Radnai Bálint, Rittgasszer Ákos, Uzonyi 000 Ákos. 9 points: Jakab 042 Richárd, Szakali Benedek, Tersztenyák Balázs. 8 points: 1 student.
Problems in Information Technology of KöMaL, September 2015