Traducir el blog
Google Sheets Calendar with a Pivot Table
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!
Mi lista de blogs
-
Regresos 14 – (2) Bases de cuatro cubos con suma cero - En la anterior entrada estudiamos las sumas de cubos cuyas bases suman cero. En esta otra ampliaremos el estudio a cuatro cubos. *Sumas con cua...Hace 8 horas
-
Problema 2: Errores en la nube de OneDrive - 🔝*To translate this blog post to your language, select it in the top left Google box. * Este año ha comenzado con dos problemas detectados en Microso...Hace 1 día
-
Crea con IA tablas Excel de ejemplo - Crea con IA tablas Excel de ejemplo para practicar y simplificar tareas de buscar datos o crearlos. A veces, encontrar el conjunto de datos perfecto para...Hace 2 días
-
What Does #### Mean in Microsoft Excel? - If you’re wondering what does #### mean in Excel, read this Microsoft Excel guide until the end, and learn all the ways Excel uses the pound or number sign...Hace 2 días
-
Cómo graficar totales de una tabla dinámica en Excel - Las tablas dinámicas son una de las herramientas más poderosas en Excel para organizar y analizar grandes cantidades de datos. Sin embargo, cuando necesi...Hace 3 días
-
OBTENER LA TENDENCIA EN POWER APPS CALCULANDO LA PENDIENTE - Hola a todos, He estado estos días implementando un nuevo input en el borrador de un proyecto de Apps que estoy creando. Se trata de … La entrada OBTENE...Hace 3 días
-
3DFrame-NLGeom - The VBA version of the 3DFrame spreadsheet has now been updated to allow geometric non-linear effects to be included. The new version can be downloaded fro...Hace 5 días
-
Fin - Llevo tiempo pensando esta entrada y no sé que decir. Analisis y Decisión nació en 2008 y no ... Leer más »Hace 1 semana
-
Validación de Datos en Excel: de lo Básico a lo Avanzado - En Excel, uno de los errores más comunes es dar por sentado que sabemos lo básico. Muchas personas que se […] The post Validación de Datos en Excel: de l...Hace 1 semana
-
📊🔎 Cómo usar SI y UNIRCADENAS para manejar múltiples resultados en un búsqueda - ¿Cómo buscar múltiples resultados en Excel y unirlos en una sola celda?Si has trabajado con Excel, seguramente conoces la función BUSCARV, que permite enco...Hace 2 semanas
-
Happy Excel Day 45678 - Where does the time go? Two years ago, we were celebrating Excel Day 45000, and suddenly it’s Excel Day 45678! Do you have any special events planned? If n...Hace 4 semanas
-
FREE 4 Hours Complete Excel Course - FREE 4 Hours Complete Excel Course Here is a FREE, complete end-to-end Excel course to help you learn all the key and essential aspects of Microsoft Exce...Hace 1 mes
-
Análisis DAFO (FODA, DOFA) las decisiones con Excel - Para conocer la situación de una empresa, proyecto o persona, recurrimos al análisis DAFO (FODA, DOFA) en la toma de decisiones con Excel. El los años sese...Hace 3 meses
-
Color, Conditions, and Copilot: How to save time using conditional formatting with Copilot in Excel - Hi everyone, this is part 11 in a series of posts to show you some of the things that are possible to do with Copilot in Excel. *What is conditional f...Hace 3 meses
-
4 ejemplos de utilización de validación de datos con fórmulas - Ya sabes que la herramienta de Validación de datos es de gran utilidad para controlar y restringir la introducción de datos y así, asegurarte de…Hace 5 meses
-
How To Predict Bearing Life With Excel - When you work in mechanical engineering, understanding the reliability and performance of bearings under various conditions is crucial. Bearings are the co...Hace 5 meses
-
-
TikTok’s search evolution - 2 in 5 Americans use TikTok as a search engine. Nearly 1 in 10 Gen Zers are more likely to rely on TikTok than Google as a search engine. More than half of...Hace 10 meses
-
Unblocking and Enabling Macros - When Windows detects that a file has come from a computer other than the one you're using, it marks the file as coming from the web, and blocks the file....Hace 1 año
-
Office Scripts: Trabajando con Tablas - [image: Office Scripts: Trabajando con Tablas] Me he dado cuenta que últimamente solo escribo de lenguaje M (es mi pequeño vicio)... pero hay que liberar l...Hace 2 años
-
Hello world! - [image: Hello world!] Welcome to WordPress. This is your first post. Edit or delete it, then start writing!Hace 2 años
-
La importancia de saber mecanografía en 2022 - [image: Resultado de imagen de mecanografía viñeta escribiendo a máquina] Según la RAE, la mecanografía es el arte de escribir a máquina. Hace unos cuantos...Hace 2 años
-
London Excel Meetup Workbooks - The workbooks used in my presentation on “Analytical and Interactive Dashboards in Excel” at the London Excel Meetup, September 3, 2020Hace 4 años
-
Cálculo de jornada que termina al día siguiente (Power Query) y despedida - [image: Cálculo de jornada que termina al día siguiente (Power Query) y despedida] Este blog se ha ocupado de cálculos de tiempo con bastante intensidad, c...Hace 4 años
-
Agenda Perpetua Excel Calendario Perpetuo - Saludos a los ingenieros y a todos los que forman parte de nuestra comunidad de planillas Excel para ingeniería civil, ya se vienen las fiestas navideñas y...Hace 5 años
-
International Keyboard Shortcut Day 2019 - The first Wednesday of every November is International Keyboard Shortcut Day. This Wednesday, people from all over the world will become far less efficient...Hace 5 años
-
Welcome, Prashanth! - Last March, I shared that we were starting to look for a new CEO for Stack Overflow. We were looking for that rare combination of someone who… Read more "W...Hace 5 años
-
Visualize parts and whole - combine clustered column and stacked column charts - *Inga: Disa what?* *Igor: -ppeared.* by The FrankensTeam ------------------------------ Really it was 3 years ago we posted our last article? *Freddy: Th...Hace 5 años
-
Salvador Sostres, analfabeto profesional - Los nuevos tiempos traen nuevas profesiones. Internet, además, ha revolucionado el mundo del periodismo y la palabra escrita. Adaptarse o morir, ese es el ...Hace 6 años
-
Planificación de compras - Realizar una lista con los productos que necesitamos y que formarán parte de nuestra cesta de la compra nos ayuda a *encontrar la combinación de bienes p...Hace 12 años
-
-
-
-
-
-
-
-
-
No Response to "Google Sheets Calendar with a Pivot Table"
Leave A Reply
Indícame las erratas que encuentres y qué es lo que te gustaría ver en los próximos artículos.