How to make a dynamic calendar in excel
Ғылым және технология
1) Use data validation to make drop down lists for the years and months. 2) Use the DATEVALUE and WEEKDAY formulas to make the dates. 3) Use conditional formatting to hide the dates from other months.
=DATEVALUE("1-"&A2&"-"&B2)-WEEKDAY(DATEVALUE("1-"&A2&"-"&B2),3)
---------------------------------------------------------------------------------
#exceltips #exceltutorials #excelformulas
SUBSCRIBE: goo.gl/c46YPs
Microsoft Office 365, 2016, Office Insider, Version 2003
---------------------------------------------------------------------------------
IF YOU LIKED THIS VIDEO YOU MAY ALSO LIKE:
- How to make a calendar heatmap in excel of average number of births • How to make a calendar...
- How to make a calendar heatmap in excel • How to make a calendar...
Пікірлер: 82
Wow, probably the best tutorial i have ever seen. Subscriber aquired!
Awesome Video. No unnecessary blah, blah. Right to the point. Not too fast. Nice voice :- ) and it works!!
Thank you so much for this. I have been looking for these formulas for a couple of weeks. This will enable me to create a spreadsheet for my daily worked hours running from and to the 19th of each month. SUBSCRIBED!
This was pure art; when you can distill complexity into functionality and aesthetic beauty- amazing
Fantastic! Thank you very much, very useful and clear :)
WOW!!!!...No words!, but I will repeat it, by copying your method and formulas, Thanks a million!...You just made my year!
Thank you for sharing this, Karina. I appreciate that.
Very useful, thank you lady!
Cool! I was waiting this tutorial for ages!!!!
Many thanks...really helpful tutorial 🌹
Thank you! i was looking for this exact hack :)
THANK YOU!!! i had been looking for this type of calendar, a friend of mine did the first one for me, but now I've been getting a Error Unknown range name: 'JULSUN1' and your video has helped me out a lot!!!
This is supercool! Will put it to great use. Another example why Google Sheets is inferior to Excel. I honestly can't give up on Excel's practicality and efficiency.
Thanks a lot. This helped me so much!!!
Awesome, thanks a lot!
Great tutorial!
So awesome and helpful thanks!
Thankyou. This is great
Nice video it was very informative . Thanks
Dear Karina, many thanks for the video it is super useful. May I ask do you have an idea how we can implement working days numbers (WD) view as well. Many thanks for your advise in advance, Best regards, Adrienn
Great video, but I'd just want to know if there is a way from making a planner out of this calendar? I'm trying to find out how I can attach activities t corresponding dates and at the same time along with your demo having them also disappear and display in regards to the current date value is being searched.
Thank you Very Helpful
Very good tutorial, thank you!
Thank you for all this much❤❤
Thanks for the video! I created one with your tutorial and someka's tutorial video.
@meganhanson9402
Жыл бұрын
That's also a good one!
Many many thanks Mam
Jaw-dropping video!
Nice video, greeting from Indonesia
happy chewsday! dope vid!
thks for sharing ❤
¡Gracias!
Very very nice
If I wanted to say Highlight my bi-weekly paydays with this calendar, how would I go about doing that with conditional formatting?
Awesome
Mam Monthmonth This conditional formatting formula I was understanding not successfully please one more video with more examples to understand very well for me thank you
Thanks for your bright explanation. I found one discrepancy: how could you correct the 29th of February in leap years?
this is a great tutorial, thank you, but I cannot figure out how to add all 12 months now. Can you offer any help for that ?
Thank you for helping us do it ma'am :)
@AnonymousUnknown-ke2ni
3 жыл бұрын
Ahahahaahaha
@AnonymousUnknown-ke2ni
3 жыл бұрын
Na ka pag pass kna?
@codclick8931
3 жыл бұрын
@@AnonymousUnknown-ke2ni sino ka po
@AnonymousUnknown-ke2ni
3 жыл бұрын
@@codclick8931 🤣🤣🤣🤣
@codclick8931
3 жыл бұрын
???
Very Impressive... Cant i copy and past it from somewhere ?
its wow ,,mam
Hi Karina, this video is very helpful, Happened to come across the following function in another calendar. Was hoping if you could do a video on how this function works.( =$A$1-(WEEKDAY($A$1,1)-(start_day-1))-IF((WEEKDAY($A$1,1)-(start_day-1))
Hi how are you! First very good video it helped me a lot with a work issue. Second i have a question. Excel kept showing me the error #Value in the DATEVALUE() formula when i used the Months as Strings (January, Feb...) The only way to make it work was to make the drop list as numbers 1-12 for the month... Do you know how to solve that? Thank you!
@tamaninesauc
5 ай бұрын
I'm probably a tad late but check your language settings and if all the date formats match. i use two languages daily so if i spelled the months in the language i had set excel in it worked.
Thank you Karina! Very helpful! At the beginning I noticed that the formula for some reason doesn't work and I got an error. I was trying to figure out what can be wrong and the case was that in my excel instead of "," I need to use ";" so my formula should be =DATEVALUE("1-"&A2&"-"&B2)-WEEKDAY(DATEVALUE("1-"&A2&"-"&B2);3)
what if I want to start the datavalue on the first saturday of each month rather than the first day of each month?
Is there a way to highlight a specific date?
Can i do that in Google Sheet?
Is there any possibility to create a calender just with months and by clicking the months to bring a data? For example monthly payment. It will looks like an interactif calender with data inside.
Like a normal calendar, I would have made the dates not in the month a light grey color so that they would just be visible but not be distracting For the conditional formatting month comparison, is there a way to compare dates not in the selected month to that text month
@DattoDanSSS
Жыл бұрын
I'm trying to work this exact problem out now, did you find a method that works?
@keningilbert
11 ай бұрын
I was expecting she would have shown how to do both approaches, hiding and greyed out, since each is as common of the other. Without not having tried this I’m wondering if simply changing the color while on the Font tab of the Conditional Formatting window (6:43 min mark) would work or at least provide an acceptable alternative.
I have a question. I am trying to make a 1 sheet calendar (12 months) based on your example. How can I get all of the 12 months to dynamically update similarly to your example. I am trying to make this function but cannot seem to make the rest of the months dynamically update
@karinaadcock
2 жыл бұрын
I think something with EOMONTH will probably work. It stands for End Of Month and will get you the last day of the month. So =EOMONTH(your date, 0) will get the last day of the month your date is in. =EOMONTH(your date, 0)+1 will get the first day of the next month. =EOMONTH(your date, 1)+1 will get the first day of the month after that.
Come on guys! 205K views and *only* 1.7K likes and 77 comments?!? After the effort Karina put into this amazing video, I expect more. How much effort does it take to type a few words of thanks. Every like and comment will help grow the channel. Great video. Thanks for your time and effort. I've learned a lot.
I have followed but it is not dynamic and borders not disappearing ...
hiding the dates other than the particular month using conditional formatting with ";;;" not working properly when we change the month.
I wish i knew how to do this with the week starting on sunday.
Good day Mam. Sorry to disturb you as i got an error when adding the month and the year for header, Thanks. Keep safe.
I've taken this great video and made a calendar with all 12 months on one sheet. I've also added another layer of conditional formatting which highlights all public holidays in another colour. However, I've come up against one issue which I wondered you could help with? If I find a public holiday which lands on e.g. 31/01/21 it will appear twice, in the end of Jan month and at the beginning of Feb. Anyway to fix this? Thanks,
@stevethetrixter
2 жыл бұрын
Could you show us how. you made all 12 months work, I am trying to do the same and I cannot figure out how to do it
@AKapasi
2 жыл бұрын
@@stevethetrixter I'll dig it out and let you know. From memory I still had to apply all my conditional formats to each month rather than across the whole calendar.
@stevethetrixter
2 жыл бұрын
That would be great thanks. I just can’t figure out the formula to start the next month after the end of that single monthly sheet. It’s really bugging me that I can’t solve it.
@AKapasi
2 жыл бұрын
@@stevethetrixter I did 12 mini versions of the same thing shown in the video, and using DATEVALUE got each one to show Jan, feb, Mar etc. Not sure if that helps
I followed all the steps you did, now until the last step I have a problem with the last one. When I write the command Month(e7)month($g$9) goes to custom, records the command ;;; When I confirm everything, all the numbers on the calendar disappear. I also tried to do other commands, I didn't succeed. I would be happy if I receive a response from you, what can be done in such a situation, thank you
can i do it for sunday as the first day?
@karinaadcock
2 жыл бұрын
The WEEKDAY formula has different options for the first day of the week.
@brittanyc7998
Жыл бұрын
Which return type would I use for a week that starts on sunday 17? When I try 17 it doesn't work out properly.
how do i get rid of the months on the lists (so that they are not seen)?
@riafarran81
Жыл бұрын
I would say put them in another tab or hide columns
Please explain how you are able to carry one formula to another cell without having to copy or cut it. You need to explain some basic stuffs because not everyone is as savvy
AX
very complicated
DATEVALUE it is not necessary .
W⅔
The content is great, but the stacatto speaking made me leave. Sorry.
A very helpful video! Thanks