How to make a dynamic calendar in excel

Подписаться 45 тыс.

Просмотров 214 тыс.

50% 1

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...

Наука

Опубликовано:

4 май 2020

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 82

@USMColdies 2 года назад

This was pure art; when you can distill complexity into functionality and aesthetic beauty- amazing

@sarahbee1115 2 года назад

Awesome Video. No unnecessary blah, blah. Right to the point. Not too fast. Nice voice :- ) and it works!!

@unclebroady2303 2 года назад

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!

@Omwereru Год назад

WOW!!!!...No words!, but I will repeat it, by copying your method and formulas, Thanks a million!...You just made my year!

@nombre464 3 года назад

Fantastic! Thank you very much, very useful and clear :)

@garychen2012 2 года назад

Thank you for sharing this, Karina. I appreciate that.

@iris_walkfish7581 3 года назад

Cool! I was waiting this tutorial for ages!!!!

@silverfunnel6819 4 года назад

Very useful, thank you lady!

@ricksanchez9073 18 дней назад

Wow, probably the best tutorial i have ever seen. Subscriber aquired!

@Abynate Год назад

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.

@nazik58 3 года назад

Many thanks...really helpful tutorial 🌹

@avitalalef9947 2 года назад

Thank you! i was looking for this exact hack :)

@didierliango6402 2 года назад

Awesome, thanks a lot!

@juanaadams4097 2 года назад

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!!!

@LukePlunder 6 месяцев назад

Thanks a lot. This helped me so much!!!

@cguynycseven Год назад

Great tutorial!

@SalinaLeighh 2 месяца назад

So awesome and helpful thanks!

@adriennkanto264 3 года назад

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

@liamandkai030810 3 года назад

Thankyou. This is great

@GeorgeAJululian 2 года назад

Thank you Very Helpful

@jeffmcguire2072 2 года назад

Jaw-dropping video!

@danieltan9908 3 года назад

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.

@mamadolay5986 Месяц назад

Thank you for all this much❤❤

@abdulwarismalik4200 2 года назад

Nice video it was very informative . Thanks

@hariomgoswami2960 3 года назад

Many many thanks Mam

@pyniedoll 7 месяцев назад

thks for sharing ❤

@masiel2010 Год назад

¡Gracias!

@fathonifirmansyah3789 3 года назад

Nice video, greeting from Indonesia

@donbvonb 2 года назад

happy chewsday! dope vid!

@brentgarret2388 Год назад

Thanks for the video! I created one with your tutorial and someka's tutorial video.

@meganhanson9402 Год назад

That's also a good one!

@markusjaggli548 3 года назад

Awesome

@DDX01 3 года назад

Very very nice

@davidgermany1527 3 года назад

If I wanted to say Highlight my bi-weekly paydays with this calendar, how would I go about doing that with conditional formatting?

@jpkamalanathan3859 4 года назад

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

@rynthorn1551 4 года назад

Very good tutorial, thank you!

@abhinavsinghchauhan2127 3 года назад

its wow ,,mam

@joniginajunior Год назад

Thanks for your bright explanation. I found one discrepancy: how could you correct the 29th of February in leap years?

@stevethetrixter 2 года назад

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 ?

@codclick8931 3 года назад

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 года назад

???

@patrycjakotlicka8030 Год назад

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)

@andreavivas5905 3 года назад

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.

@lifeh2o911 3 года назад

Very Impressive... Cant i copy and past it from somewhere ?

@anraki013 2 года назад

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))

@serenityseamoss1825 2 года назад

what if I want to start the datavalue on the first saturday of each month rather than the first day of each month?

@Flamestryke-TV 2 года назад

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.

@umutdeniztopcuoglu 2 месяца назад

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.

@patrickschardt7724 3 года назад

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.

@murugavel1341 3 года назад

hiding the dates other than the particular month using conditional formatting with ";;;" not working properly when we change the month.

@alancubos327 Год назад

Is there a way to highlight a specific date?

@ianl1052 2 месяца назад

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.

@msspei Год назад

Can i do that in Google Sheet?

@murugavel1341 3 года назад

I have followed but it is not dynamic and borders not disappearing ...

@user-gd2vm1cl9x Год назад

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

@romeodariochiu8137 2 года назад

Good day Mam. Sorry to disturb you as i got an error when adding the month and the year for header, Thanks. Keep safe.

@AKapasi 2 года назад

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

@alfonsoparra7653 6 месяцев назад

I wish i knew how to do this with the week starting on sunday.

@juanaadams4097 2 года назад

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

@credencehomehealthservices 5 месяцев назад

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