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
-
Enviar Mensajes Masivos de WhatsApp desde Excel, incluyendo imágenes (a cualquier número) - En el mundo actual, la comunicación eficiente es clave, especialmente cuando se trata de interactuar con una gran cantidad de […] The post Enviar Mensaje...Hace 9 horas
-
Avoid These Power Query Pitfalls with Simple Steps - Power Query is an extremely powerful tool for efficient data analysis and decision-making processes. However, even the most experienced among us can someti...Hace 1 día
-
Goldbach con semiprimos - (Ver entradas anteriores sobre números semiprimos) Al igual que en la conjetura de Goldbach, que descomponemos todo número par en suma de dos primos, aqu...Hace 2 días
-
Conversor PGN de ajedrez mejorado - 🔝*Select the language of this blog post in the Google box at the top left. * Esta es la *4ª versión del Conversor PGN de ajedrez* que hago, cada uno ...Hace 3 días
-
SLS Concrete design with Python - I am in the process of converting the SLS design related functions in the RC Design Functions spreadsheet to Python code. The current version can be downlo...Hace 5 días
-
10 Ways to Count the Number of Occurrences in Microsoft Excel - Wondering how to count the number of occurrences in Excel? You’ve reached the right resource! Keep reading. Suppose, you’ve got a tabular dataset in your w...Hace 1 semana
-
Announcing Power BI Dashboard Contest (win $500 prizes!) - Hey there, I have a SUPER exciting announcement! April is about to get a whole lot sweeter with our Power BI Dashboard Contest! Your mission, should you ...Hace 1 semana
-
Análisis financiero tipos introducción - Conoce el Análisis financiero tipos y cómo es fundamental para evaluar la salud financiera de una empresa. Hay que examinar meticulosamente los datos fin...Hace 1 semana
-
What's New in Excel (March 2024) - Welcome to our March 2024 update. This month, export to CSV and show details in PivotTables are now available in Excel for the web. Also, creating Power...Hace 2 semanas
-
Minutos de juego y puntos. El Espanyol, sus finales de partido y mis enfados - Pienso que el Espanyol este 2024 se está dejando muchos puntos al final de los partidos. Cuando el ... Leer más » The post Minutos de juego y puntos. El ...Hace 2 semanas
-
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 4 semanas
-
REALIZAR UNA CONSULTA LEFT JOIN CON TYPESCRIPT - Hola a todos, Hace tiempo que no publico sobre typescript, pero es que como ya sabéis estoy muy centrado en Power Apps y esto me … La entrada REALIZAR U...Hace 5 semanas
-
Data Validation Selection Popup Tool – Contextures - With the free Data Validation Select Popup (DVSP) tool from Contextures, you can add popup lists to Excel workbooks. Set the rules you’d like for the pop-u...Hace 1 mes
-
Aprende cómo usar la función #Filtrar con rangos de fechas SIN macros 📋 [VIDEO] - ¿Alguna vez te has preguntado cómo puedes filtrar tus datos por fechas específicas en Excel de manera rápida y eficiente? En este video, voy a enseñarte pa...Hace 1 mes
-
MASTERCLASS Gratis – Gráfica de Gestión Proyectos en #EXCEL. - Aprende a crear un Gráfico de CURVA S, ideal para GESTIÓN DE PROYECTOS, porque te permite identificar como esta tu proyecto tanto en COSTOS como en TIEMP...Hace 2 meses
-
Interés compuesto con Excel - En la imagen, se presenta un esquema de las distintas funciones Excel para calcular el valor del dinero en el tiempo mediante la capitalización compuesta...Hace 2 meses
-
Demos cursos de Excel 2007, 2010, 2013, 2016, 365 - Puedes consultar las demostraciones de los siguientes capítulos de los cursos Excel. Demo cursos ExcelHace 3 meses
-
El sistema operativo no está configurado para ejecutar esta aplicacion. - Si esta semana habéis recibido el mensaje "El sistema operativo no está configurado para ejecutar esta aplicacion" cuanto intentásteis abrir alguna aplic...Hace 6 meses
-
Atajos esenciales de Excel que todo profesional debe conocer - Manejar Excel con destreza puede ser el superpoder que no sabías que necesitabas. Agiliza tus tareas diarias y sorprende a tus colegas con estos atajos c...Hace 8 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
-
-
Navigating Outlook Favorites - I have these four favorites defined in Outlook: From the inbox, I could hit Shift+F6 to get into the Favorites area but sometimes I would end up in no man’...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 1 año
-
Progress on the Block Protocol - Since the 1990s, the web has been a publishing place for human-readable documents. Documents published on the web are in HTML. HTML has a little bit of… Re...Hace 1 año
-
Hello world! - [image: Hello world!] Welcome to WordPress. This is your first post. Edit or delete it, then start writing!Hace 1 año
-
Decálogo para realizar Trabajos de Fin de Grado (TFGs) y de Fin de Máster (TFMs) - 1.- Tanto TFGs como TFMs son un requisito para graduarse a la vez que una oportunidad para aprender. Así, el tiempo que se le dedica es muy variable. De...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 3 años
-
Cálculo de jornada que termina al día siguiente (Power Query) y despedida - [image: Story 319956911] Este blog se ha ocupado de cálculos de tiempo con bastante intensidad, como podrán comprobar con un clic a la etiqueta "Fechas y h...Hace 3 años
-
Dimensiones-Propiedades de perfiles de acero (IMCA) - Saludos compañeros, en esta oportunidad compartimos esta Planilla Excel que contiene las Propiedades y dimensiones de diferentes tipos de perfiles de acero...Hace 4 años
-
-
-
-
-
-
No Response to "Google Sheets Calendar with a Pivot Table"
Leave A Reply
Dime si te gusta lo que lees y, si no te gusta, dime por qué. Tengo habilitada la moderación de comentarios. Tu comentario se publicará pronto.