First of all, HAPPY NEW YEAR 2021 !!!
My wishes for the year that begins is that only good things happen to us and that we soon forget the fateful 2020.
My first New Year's resolution is to dedicate this blog post to the good know-how of Google, from which Microsoft should learn, so it does not contain an Excel template, as is usual in this blog, but a Google Sheets template that I have shared on the following link, and that you can copy and edit with a Google account:
Excel UserVoice is Microsoft's forum so that Excel users, like you and me, can have a voice and comment on suggestions and vote for them so that Microsoft improves its flagship tool: Excel !!! If you didn't know, you already know just now!
One of the best suggestions that I have voted and commented on, and that I suggest you do the same so that Microsoft will listen to us as soon as possible -ASAP, is to be able to use dates with a negative serial number in the cells , so that 1900 is not the first year that can be used in Excel cells and functions. Access this excellent suggestion from this link:
The suggestion has been published since September 27, 2015. In 5 years Microsoft Excel has not said a peep, when the competition of Google SheetsLibreOffice Calc work perfectly with all the years of the Gregorian Calendar,
Update 2022-06-06: That page causes Error 404: Page Not Found.
Microsoft has intentionally deleted UserVoice, and the entire history of suggestions submitted by Office users has disappeared, thereby losing all votes from the last few years.
For 7 months Microsoft has created a new Feedback portal (in English), where you can share comments and help make improvements and create new products, so I have returned to write a comment and vote for this suggestion:
Dealing with dates before 1900 · Community (microsoft.com)
To show that the Microsoft Excel competence is more advanced, I have designed a calendar based on a pivot table in Google Sheets, with the same idea that I published in the following article:
Calendar in Excel with a pivot table (in Spanish)
That calendar in Excel purposely begins on January 1, 1901, the beginning of the 20th century , since it has been shown that the months of January and February 1900 are incorrectly calculated, a bug in Excel incorrectly assumes that the year 1900 is a year leap, and we have already said that it is not capable of calculating dates before the year 1900 either.
Google spreadsheets work with negative serial numbers since the year 100, no less!
Excel forces us to program dates before 1900 in VBA language, as I have commented in the following article on this blog:
Perpetual Calendar from before 1900 (in Spanish)
Calendar in Google Sheets with a pivot table
In this image I show the appearance of the calendar in Google Sheets, made with a dynamic table that acts as a calendar, and with a table of event dates, for example holidays:
As you will see, I have hidden the essential column C, with the number
of the week (essential as a row in the pivot table), to give more visibility
to the calendar.
A Gregorian calendar can be generated by editing cell B2 from the year 100, which is not very rigorous since the Gregorian Calendar was imposed from the year 1582, and until the year 9999.
If the month in G2 cell is left blank, the calendar will be for all months. If a month is chosen, only that month will be shown.
The 'Dates' sheet contains 3 tables, two are auxiliary for the 7 days of the week and the 12 months of the year, and the other is the data source of the pivot table with which this calendar is generated.
The date table contains 366 rows to look at all the days of an entire year, including leap years, with 5 fields:
- Date: There are 366 days in column A: First day of the year in cell A2 with the formula:
- =DATEVALUE(Calendar_Year & "-01-01")
- The remaining days are added one day to the previous one: = A2 + 1
- Day of the week: Column B with the number and 3 letters of the day of the week with the formula:
- = INDEX(Days_Week, MATCH(SUBSTITUTE("*" & TEXT (A2, "ddd"), "."; ""), Days_Week, 0))
- # Week: It is the week number with System 1 numbering:
- Cell C2: = 1
- Other cells in column C: = C2 + IF(MATCH(SUBSTITUTE("*" & TEXT (A3, "ddd"), ".", ""), Days_Week, 0) = 1, 1, 0)
- Num. Week: It will be a zero if the selected year or month does not match:
- = IF(AND(TEXT(A2, "yyyy") = TEXT($A$2, "yyyy"), OR (Calendar_Month = "", TEXT(A2, "mm") = LEFT(Calendar_Month, 2))), C2, 0)
- Month: Month number with 2 digits and 3 letters of the name of the month:
- = SUBSTITUTE(TEXT(A2, "mm-mmm"), ".", "")
With the SUBSTITUTE(text, ".", "") function I remove the period "." that appears in the days of the week or months, for example: "Mon." or "Jan."
For negative dates the WEEKDAY function returns Error # NUM !, for example for the day 1-1-1800: = WEEKDAY(-36522, 2)
For negative dates, the WEEKNUMBER function returns Error #NUM!, for example for the day 12-29-1899: = WEEKNUMBER(-1, 2)
The DATE function cannot be used either, since with negative dates it produces a curious effect, for example: = DATE(1899, 1, 1) returns the positive serial number corresponding to the day 01-01-3799.
For years 0 to 1899, the DATE function adds that value to 1900 to calculate
the year.
For example, DATE(119, 2, 1) returns the date 1/2/2019.
In this calendar, formulas are used that replace the two that return Error #NUM!, WEEKDAY and WEEKNUMBER, and the DATE function, as seen above in the fields of the 'Dates' sheet
This calendar is embedded as "read only" in the following box:
How to create the pivot table
The pivot table is defined with:
- Data source in range: Dates!A1:E367
- Rows: with the fields: Month and No. Week (Week)
- Columns: with the field: Day Week (Day)
- Values: with the field: Date(Calendar). Add by: Max
- Filters: with the field: Week No. - The value is greater than 0
Conditional formats
In the sheet ' Dynamic Calendar ' a table of dates and their events, for example holidays, has been added in columns L: M, with dynamic formulas to calculate holidays, of the type:
= DATEVALUE(Calendar_Year & "-01-01")
As I already mentioned, it is not possible to use the DATE function for years before 1900, so I have used the above function.
I explained the formulas to obtain the days of Holy Week in the following link, and I have proven that the formula is correct between the years 1894 and 2203:
To color today and Saturdays, Sundays and holidays, 4 conditional formats have been defined in the range D6:J74
- Orange color for days of events -festivities- with the formula:
- = IF.ERROR(MATCH(D6, $L$6:$L$74, 0), 0)> 0
- Yellow color for today: = D6 = TODAY()
- Dark red color for Sundays with the formula:
- = AND (SUBSTITUTE(TEXT(D6, "ddd"), ".", "") = "Sun", ISNUMBER(D6))
- Light red color for Saturdays with the formula:
- >= AND (SUBSTITUTE(TEXT(D6, "ddd"), ".", "") = "Sat", ISNUMBER(D6))
CONCLUSIONS
It is shown that Google spreadsheets allow calculating with negative dates before the year 1900, which is not possible with Microsoft Excel that only supports positive dates from 1900.
In addition, Google Sheets automatically refreshes the pivot tables, which is very practical, since Excel forces you to manually refresh the pivot tables from the menu: Data - Update all, which is sometimes forgotten, and generates errors in the generated and reported data.
Who has not happened? Raise your hand!
