How I create a truly dynamic calendar in Excel or Sheets
HTML-код
- Опубликовано: 8 сен 2024
- How to create a truly dynamic calendar in Excel or Google Sheets by adding a simple formula in one cell.
Get a copy of the Google Sheets doc that I used in this tutorial by clicking the link below.
docs.google.co...
Very elegant!
Thank you for this tutorial! I truly appreciate it! I'm just really jumping into google sheets and creating dynamic things with it and this was extremely helpful!
Best one I saw
OMG you have no idea how long I've been trying to create a damn calendar.. 3 bloody days of trawling through crappy videos! You have just saved my life mate, thank you!!! Honestly the other videos on how to get the first day of the week are so damn complicated - i just knew there was an easier way of finding it!!!! thank you once again!
Thanks for the comment. I knew this video would get lost amongst all the other calendar videos so I'm glad you found it.
@@sheets4work158 well you've bagged yourself a sub dude please upload more stuff your a fountain of knowledge 👍
@@sheets4work158
Richard Emeni
Wanted: dynamic calendar programer with sheets and or Excel. Please contact linkedventures2015@gmail.com. This is URGENT
Great one
this was so helpful!!! thank you so much for making this video. This is the easiest way I've seen it, so simple and quick to follow.
THANKS a LOT🥺❤️🔥!!!!!
YOU HAVE JUST SAVED MY LIFE!!!
THANKS
Very nice. Two thumbs up!
Simple and works perfect!
First of all great tutorial! Thank you. This is exactly what I was looking for.
One follow up question though, if you possible.
How do I change the color of "today's date" ?
My calenders is setup with the month in a drop down list, if that makes a difference.
Your formulas works perfectly, but I can't seem to get conditional formatting to work right to change the color of "today's date" only. The closest I got changed the right day, but in all months.
cool thanks. I'm going to see if i can use this to build a workout sheet that for each date i can see what my workout is and then also be able to record my sets and reps for that date.
Thanks! This really worked.
Is there a way to format the day we're on? I want to fill the specific cell with the day we are on, is it possible? I think about Today formula but I'm not sure.
=text(xxx,"dddd")
xxx is cell
My excel doesn't support the comma in the formula like =J3-weekday(J3,2)+1 I need to change the " , " with " ; "
thank you! now I finally understand why none of the tutorials worked for me lol
Thanks! I was having the same problem and you helped me!
It doesn’t work with my Google sheet 😢
great this works thanks for sharing this with us !!!
this is great, . I wonder if you can then link to another sheet that populates or formats the days based on daily entries in another tab. e.g. on Monday 1st there was an entry (in another tab or dynamic range) so let's colour this green, on the second it didn't happen so let's colour this red...
Thanks for the comment. If you click on the link in the description to make a copy of the google sheets spreadsheet I used for the video, there are a couple of extra sheets that show how to display a bigger calender and dynamicaly show events. You should be able to amend it for your own requirements.
I made the January calendar but I can't figure out the conditional formatting because it doesn't select the right dates
Awesome, thank you
this was great! thank you!
2:50 the formula shows only date as for example 2/28/2022, formating it to number shows 44 620, it never shows just plain single digit number like yours. What the hell am I doing wrong?
Awesome, this was great! Thanks for sharing! 👍😀 Though the last part (changing the color) I did not get it, because my english! Can you explain it to me, please?
Por supuesto. We need to use conditional formatting to change the colour of the dates from the months before and after the current month. To do this we need to use a formula (the option at the bottom of the list in Excel and G Sheets). After highlighting all the dates, the formula is =month(J5)month($J$3) that's saying if the month of the first date in the range (top left) is not equal to the month of the date we entered for the month heading in J3 then colour grey. The formula will be automatically applied to every cell in the calendar from J5 down but we need to lock the cell we're comparing them with J3 using the $ symbol $J$3. Espero que sea más claro que el video. Fui demasiado rápido. Gracias por tu comentario.
@@sheets4work158 Wow, thanks for the detailed explanation! 🤗
THANKS!!
Richard Emeni
Wanted: dynamic calendar programer with sheets and or Excel. Please contact linkedventures2015@gmail.com. This is URGENT
This calendar was way more informative than any I have seen thus far! I am trying to make Saturday the first day of the month but the calendar gives me an error if I use the week formula with "=j3-weekday(j3,7)+1". Can you assist with that?
I thought the same but the documentation is a little confusing. The 1 and 2 just change how the result of the weekday function numbers the days so with 1 Saturday outputs 7 and 2 outputs 6. I got Saturday by using 1 and removing the -1 J3-weekday(J3,1) but had an issue when the first day is of the month is Saturday so I added an if statement for that =if(weekday(J3,1)7,J3-weekday(J3,1),J3)
@@sheets4work158 Awesome! I'll have to try that out.
@@sheets4work158 It worked perfectly. Thank you so much!
If I wanted to have add an event to only start on the 1st of the month, but dynamically, that date moves from cell to cell, how can I lock the referenced event to the 1st date block? Thanks in advance
You would need to put a formula in every cell under the dates to check if the date above is the 1st and then show your event, something like =if(day(D12)=1,"My Event","") also merge the cells in the box. I've added a sheet to my shared sheet that can display events from a list dynamically on the big calendar. it uses a query function under the date in each box to lookup the events list on the right. The query was not a simple as I thought it would be so my thanks go to Ben Collins bencolllins.com for providing the solution.
Thank you so much! A kicker that I didn't explain is that it's like a topic 1, topic 2.. Topic 1 goes to Day 1 (whichever day that may land on, and then Topic 2 goes to the day following day 1 (whichever day that may land on.
how would you add two calendars month before and month after smaller with current month bigger?
To create 2 calendars you can simply copy and paste the existing one. To make one smaller than the other requires a bit of formatting in the spreadsheet, merging cells etc. I assume that you want the small calendar above the larger one. I'll try this on another sheet in the document that's linked in the description and let you know how I get on.
Didn't realise that I hadn't shared the Google Sheets document that I used in the video. Here's the link docs.google.com/spreadsheets/d/1SnvfcQuZjYG9Yq69R7Nw34BQRwknmchFIo4wIcIymJQ/copy?usp=sharing I've updated it to include a sheet with 3 calendars 2 for previous and next month above a large calendar that will display the current month.
@@sheets4work158
Richard Emeni
Wanted: dynamic calendar programer with sheets and or Excel. Please contact linkedventures2015@gmail.com. This is URGENT