Nice tutorial. For highlighting the bank holidays you also could use a countif function, instead of isnumber(match). Countif immediately results in a logical test.
Really useful tutorial, your explanation was excellently delivered, it must have been as it worked first time for me. I also wondered if the calendar you had on the right used similar formatting but I did further wonder about the directional up and down function to the right of the month field.
On the calendar, is it possible to add additional rows between the weeks so that you can enter text in them to note events specific to them dates, birthdays for example. And if so, does the text change accordingly as you would click between the different months & years?
Very interesting and informative. Thank you. My question is: How can I make the cells which are not part of the month return a blank? Not use cell formatting as that just hides the value.
cracking video, really made sense. one question, how do I write in the date box? do I need to put it on the sheet list and then add it like the bank holidays??
Hi Chester, is there any way to do the conditional formatting in calendar that has the dates in every other cell in the row? So the 1st is in one cell, and then the 2nd is two cells down in the row?
Great tutorial! Slightly rhetorical question; Is there a way to integrate this calendar on top of an existing spreadsheet with its own widths of the columns and heights of the rows, without changing these original column widths and row heights? I would thinks something like an insert on top of the existing spreadsheet and the insert stays in place on that position.
I want to automatically get rid of the last row(s) when necessary, for instance, in April of 2022 or the last 2 rows of February 1943. I currently have a gold border around the calendar, which I'd like to keep, but it's not necessary. Have you any ideas on how to accomplish this?
i tried this in spreadsheet, but i can't apply the holiday formatting. any suggestion? spreadsheet conditional formatting keep saying "the formula is not valid"
What would you think of this formula : "=VSTACK( PROPER(TEXT(DATE(year,SEQUENCE(1,12),1),"[$-fr-fr]MMM AA")), MAKEARRAY(31,12,LAMBDA(line,col,IF(MONTH(DATE(year,col,line))=col,DATE(year,col,line),""))) )" (it's intended for French formatting. Replace "[$-fr-fr]MMM AA" by the equivalent for your country, for instance "[$-en-en]MMM YY" for UK English!).
thank you! ive watched so many calendar videos and yours is the only one that made actual sense!!! THANK YOU
Glad it was helpful!
Great video! Needed a mini calculator and got stuck. This video was perfect to get me sorted. Always learning something new with Excel/Sheets.
mr tugwell you really saved my life with this one 😭 thank you so much!!
Nice tutorial. For highlighting the bank holidays you also could use a countif function, instead of isnumber(match). Countif immediately results in a logical test.
Thanks a ton Chester for your efforts and easy and clear english and logic
Chester… can’t give you more than 1 thumbs up and I can’t subscribe more than once. If I could, I WOULD!! LOVE LOVE LOVE your videos.
Very generous!
Really useful tutorial, your explanation was excellently delivered, it must have been as it worked first time for me.
I also wondered if the calendar you had on the right used similar formatting but I did further wonder about the directional up and down function to the right of the month field.
Another exceptional explanation. Complicated concepts explained in a way that is understood. Great Chester!!!
Fantastic! Thanks for the information!
This one is perfect and simple to understand
On the calendar, is it possible to add additional rows between the weeks so that you can enter text in them to note events specific to them dates, birthdays for example. And if so, does the text change accordingly as you would click between the different months & years?
Very interesting and informative. Thank you. My question is: How can I make the cells which are not part of the month return a blank? Not use cell formatting as that just hides the value.
cracking video, really made sense. one question, how do I write in the date box? do I need to put it on the sheet list and then add it like the bank holidays??
great tutorial! thanks! is there a possibility to add weeks as well?
Hi Chester, is there any way to do the conditional formatting in calendar that has the dates in every other cell in the row? So the 1st is in one cell, and then the 2nd is two cells down in the row?
Great tutorial! Slightly rhetorical question; Is there a way to integrate this calendar on top of an existing spreadsheet with its own widths of the columns and heights of the rows, without changing these original column widths and row heights? I would thinks something like an insert on top of the existing spreadsheet and the insert stays in place on that position.
Thank you!!!
I want to automatically get rid of the last row(s) when necessary, for instance, in April of 2022 or the last 2 rows of February 1943. I currently have a gold border around the calendar, which I'd like to keep, but it's not necessary. Have you any ideas on how to accomplish this?
Good one!👏👍
My conditional formatting for highlighting weekends and holidays and noe work
Great! Thanks Chester!!
Hello.How to calculate working hours per month in dynamic calendar
I've been trying to add the holidays to my calendar, and it never highlights it. I've used the formula listed with ISNUMBER and COUNTIF.
Superb.
i tried this in spreadsheet, but i can't apply the holiday formatting. any suggestion? spreadsheet conditional formatting keep saying "the formula is not valid"
How can I use this dynamic calendar and make it populate when I click on a cell to then select a date to then print that into the selected cell?
Super
How can i do the same without the SEQUENCE formula? My office is the professional plus 2019.
I have followed your process but haven't got the holiday
Geez...
What would you think of this formula :
"=VSTACK(
PROPER(TEXT(DATE(year,SEQUENCE(1,12),1),"[$-fr-fr]MMM AA")),
MAKEARRAY(31,12,LAMBDA(line,col,IF(MONTH(DATE(year,col,line))=col,DATE(year,col,line),"")))
)" (it's intended for French formatting. Replace "[$-fr-fr]MMM AA" by the equivalent for your country, for instance "[$-en-en]MMM YY" for UK English!).