works great - thank you for your time. I had a client that is not great at explanation - so I needed to see what day each date was as he was speaking - this filled the new. I also got to do what I truly love - learn something new Excel
man you are absolutely amazing I dint wondered all these function were in the excel but when I saw your video I was astonished that we can also create the calendar and access every month and many more things......hat off to you.........amazing!!!!!!!!
This is great!! I was able to create a monthly calendar with Monday - Friday only. Now I am trying to figure out how to create a weekly view with Monday - Friday as well. This is amazing. Thanks for sharing.
Found this searching for creating an interactive calendar. This is great. Even though it's 6 years old, it has helped me accomplish part of what I needed to. Now I just have to figure out how to color dates depending on the value in another lookup sheet. :) Thanks for this.
now i know why it was not working i was using this formula for week day =WEEKDAY(R10,16) but i changed it to =WEEKDAY(R10) and it works so thank you for your Concern
Great video. Stumbled upon this trying to fix a problem and it totally fixed it. Just one question: How do you get the calendar to highlight the current day? Seen several videos for other methods but none that incorporate your formula.
You are so awesome! THANK YOU!😉 I can't believe I kept trying for four days! The kids and I are making a calendar for father's day! 😃now after the last steps that you do when you delete the upper right hand box, how would/could I change the month?
Great video! I was curiouw, is it possible to get the dates to work for every other row, so I can have a blank row underneath a day to input something, like names, holidays, birthdays, etc?
Hey Excellsfun, I was wondering after you create the calendar is it possible to create a page for every single day? I would like to add mutiple clients on that one day. therefore by clicking on the page it allows me to enter into the day.
Question, with your formula is there a way to insert addtional rows and columns for each day of the month without it counting those cells? I am trying to make a printable monthly shift calender in which I can have 4 cells within a day in the month. So each day of the month will have 4 cells within it, first cell represent shift 1 days, immediately to the right of that cell the number representing his partner's #. Below shift 1 night shift's number and immediately to the right his partner's #?
Hi there! First of all, great video! Nice job there. Clear explanation and intuitive instructions. I´m doing a simplified version of this calendar and I wonder, is it possible (and in that case what´s the formula) to add a function that highlights todays date in the calander as soon as I open the Excel project/sheet and automatically updates it every time I open the sheet? Seen in, for example Gmail or Mac´s Calendar? Thanks in advance. Best Regards, Felix Persson
Hi Mike, how can I list the data in a calender view ? Date Call Volume 1-Sep 3992 2-Sep 4,087 3-Sep 2,174 4-Sep 2,524 5-Sep 4,571 6-Sep 3,894 7-Sep 3703 8-Sep 3491 9-Sep 3432 10-Sep 2,340 11-Sep 1997 12-Sep 0 13-Sep 4267 14-Sep 3898 15-Sep 3790 16-Sep 3545 17-Sep 2547
Hi Alan. I'm trying to keep daily record at work, how can I type into the calendar so can be look up later?? how it will work?? I need to type the activities everyday in order to look back if I need it.. thanks..
Hello, 😃I have a question, I am hoping you can answer. When I go to de-link the formula in box c7 from the top right hand corner I get something that looks like #name? And I can not figure out what is going on. I have been trying for the past four days to figure out where I keep going wrong.
This was very helpful! I do have a question, how do you copy the calendar down so that you have the next month calendar at the bottom of it. I'm trying to make what you just demonstrated but a whole column with a whole years worth calendar. If you can answer this it would be the greatest thing :)
Dunno if you still need help but I think in your case you'd need to hard code some of the parameters. So maybe you'd needa create a table that has 1,2,...,11,12 to represent months, and each calendar table reference that table. So it's like the first half of his video where he referenced the calendar to the left table The downside is that it's not fully dynamic, but you can at least get "semi-dynamic" on a yearly basis. And on the eomonth function, you adjust the -1 to 0, +1, +2....
Hi Mike, Thanks for your tutorials! How can I mark leave days in an attendance sheet. Criteria:- 1. Attendance cycle is 16th Nov 2015 to 15th Dec 2015. 2. Employees are A, B, C and D 3. A taken leave from 18th Nov for 4 days 4. B taken leave from 14th Dec for 2 days 5. C taken leave from 17th Nov for 29 days I would request for your help. Regards,
Hi I need help. The 1st, 2nd,3rd,4th,5th and 6th row has 5 row intervals in between. Example first cell is F7, the next row is F13. How to write the right formula? Thanks!
Perfect video. Looked at others and they werent as "appealing" and were just...."blah". Used the info and worked with no issues. But I am having a problem with using the calendar for my recurring bills. I have another worksheet with all my info and have "Account", "Amount", "Due Day". Due Day being just the day of the month its due. So I believe that I can use that info to create a formula to find the "Account" that corresponds with the "Due Day" no matter what month it is. Just curious on how to make that happen....
+Krystal Mitchell It was B2 the location at the top. But it will show a Date I think... I think He had it set up before hand, because I had the same problem. It's what he shows you right after when you re-Hilight the cell and go to format cell. Make it say what it says in the custom area. i.e.. adjust the mmmyyy stuff in that field. :)
When I press CTRL + ENTER after doing the de-linking part at 15:25 the start date of the next month doesn't appear (I made my Calendar using July 2014 and so the start date of August 2014 should be on a Friday)
Great Calendar! Although, I would like to make 2 modification, but I need help. 1> I'd like to add a color to the current day? like light grey. Then I'd like to Plug in a by-weekly action. Somewhere along the lines of IF (This day passes) then add (This number) to (Number in cell F9) Also a variation If (This day Passes) Then Deduct (This number) from (the number in Cell F9) Or instead of Passes, ON this day... Also, I'd like to set up the days in a (from now to infinity) type of situation. PLEASE NOTE: I understood like 70 percent of your calendar video, the rest was just copied successfully.
Hi,how you knew to put sunday first?I mean that calendar works only for that order.How you knew that you should not put Monday as first day of the week?How I can make the calendar to begin with Monday?
Thankyou.. Id been trying to figure this out myself returned to this video which gave me the basic concept for what I am trying to do o find this wonderful gem.
sure you can! If you want to hard code the content, you can write text(the whole expression he taught, "d") & "the content you wanna add". if you want to reference it from a table, say you needa put in your work schedule into the calendar, then you build a separate table with dates and your schedule. then in the calendar expression you write the same expression again and use the vlookup function. E.g. text(expression, "d") & vlookup (....). The beauty of his expression is that the result comes out as a date format, so it's compatible with vlookup when referencing your table. Note that I've added the text function because once you use "&" in your formula, the expression turns from date format to general format. So eg 8th Nov 2016 becomes 42682. PS: thank you ExcellsFun, super clear step by step walk through and unlike other websites/videos you actually explains why you use such functions in the formula. Subscribed!
I do know how to make Monday my first day of the week. UNFORTUNATELY when trying to use the long formula to authomatically create a new month I can't if I want my first day being Monday. Can anyone help me out? Thank you.
i made calendar in java and coped result automatically to text file. it show all 12 month's dates with days. then opened by excel and inserted int tables.
Hi again. May I ask for your email add? I have wanted to show you what is wrong with my formula after following your EMT709 "Defined Name Dynamic Range Formula w/ Relative References & Data Extract. Thank you very much.
This is sooo Cool..! But I want Highlight those date, If I have Birth Day/Important dates list in next sheet if some of them is there in current month I wanna Highlight all those dates.
This is awesome!! Thank you Mike the magician!
You are welcome!!!
You are welcome! I am glad that you like it!
Cool! I am glad that you like it! Simple is good!
works great - thank you for your time. I had a client that is not great at explanation - so I needed to see what day each date was as he was speaking - this filled the new. I also got to do what I truly love - learn something new Excel
I am glad that you liked it! Did you mean:
EXCELlent!!!
man you are absolutely amazing I dint wondered all these function were in the excel but when I saw your video I was astonished that we can also create the calendar and access every month and many more things......hat off to you.........amazing!!!!!!!!
Cool! I am glad that you like it!
This is great!! I was able to create a monthly calendar with Monday - Friday only. Now I am trying to figure out how to create a weekly view with Monday - Friday as well. This is amazing. Thanks for sharing.
This trick will work in 2007 or 2010. When you say not working, what do you mean "not working"?
This was too magical for me to hanble... but my eyes have been opened to a new world of possibilities
Cool! Magic is good!
Cool! I am glad that you got it worked out!
Thank you so much for all your lessons!
Cool! Sleek is good!
Found this searching for creating an interactive calendar. This is great. Even though it's 6 years old, it has helped me accomplish part of what I needed to.
Now I just have to figure out how to color dates depending on the value in another lookup sheet. :)
Thanks for this.
Did you ever figure this out? It would be conditional formatting.
now i know why it was not working i was using this formula for week day
=WEEKDAY(R10,16)
but i changed it to
=WEEKDAY(R10)
and it works so thank you for your Concern
Is there a way to create this calendar while skipping a row? I love this calendar function, but I need I blank row under each date for information.
You are welcome!!!
Thank you so much for being so generous with your time and expertise!
Excellent video! I think I learned 12 new things in 16 minutes!
Great video. Stumbled upon this trying to fix a problem and it totally fixed it. Just one question: How do you get the calendar to highlight the current day? Seen several videos for other methods but none that incorporate your formula.
You are so awesome! THANK YOU!😉 I can't believe I kept trying for four days! The kids and I are making a calendar for father's day! 😃now after the last steps that you do when you delete the upper right hand box, how would/could I change the month?
Great job Mike. I tried Bob Umlas calendar from his book and although I got the same result, I kinda like yours better, seems simpler.
Thanks for sharing!
Great video! I was curiouw, is it possible to get the dates to work for every other row, so I can have a blank row underneath a day to input something, like names, holidays, birthdays, etc?
Hey Excellsfun, I was wondering after you create the calendar is it possible to create a page for every single day?
I would like to add mutiple clients on that one day. therefore by clicking on the page it allows me to enter into the day.
You r totally awesome!!! Thank you very much for what you are doing!!!
Hi. I cant seem to use my conditional formatting. Can you please advise me on how to use it? Thanks!
wow. Thank you amazing Mike for this EXCELlent video.
You are welcome! Thanks for the support!
Question, with your formula is there a way to insert addtional rows and columns for each day of the month without it counting those cells? I am trying to make a printable monthly shift calender in which I can have 4 cells within a day in the month. So each day of the month will have 4 cells within it, first cell represent shift 1 days, immediately to the right of that cell the number representing his partner's #. Below shift 1 night shift's number and immediately to the right his partner's #?
Excellent!
Brilliant tutorial, thanks a lot
Hi there!
First of all, great video! Nice job there. Clear explanation and intuitive instructions.
I´m doing a simplified version of this calendar and I wonder, is it possible (and in that case what´s the formula) to add a function that highlights todays date in the calander as soon as I open the Excel project/sheet and automatically updates it every time I open the sheet? Seen in, for example Gmail or Mac´s Calendar?
Thanks in advance.
Best Regards,
Felix Persson
Thanks for the video.
How to get the WEEK count using Formula?....Your advise is very appreciated. Thanks in advance.
Hi Mike, how can I list the data in a calender view ?
Date Call Volume
1-Sep 3992
2-Sep 4,087
3-Sep 2,174
4-Sep 2,524
5-Sep 4,571
6-Sep 3,894
7-Sep 3703
8-Sep 3491
9-Sep 3432
10-Sep 2,340
11-Sep 1997
12-Sep 0
13-Sep 4267
14-Sep 3898
15-Sep 3790
16-Sep 3545
17-Sep 2547
Hi Alan. I'm trying to keep daily record at work, how can I type into the calendar so can be look up later?? how it will work?? I need to type the activities everyday in order to look back if I need it.. thanks..
Awesome Job - Excel God
Hello, 😃I have a question, I am hoping you can answer. When I go to de-link the formula in box c7 from the top right hand corner I get something that looks like #name? And I can not figure out what is going on. I have been trying for the past four days to figure out where I keep going wrong.
For forst date in month try this formula:
=DATE(YEAR(B1),MONTH(B1),1)
instead of:
=EOMONTH(B1,-1)+1
Is ther a way to make a second page read from the first calendar to match. If first page calendar is jan, the second converts to febuary. and so forth
this is exciting!
This was very helpful! I do have a question, how do you copy the calendar down so that you have the next month calendar at the bottom of it. I'm trying to make what you just demonstrated but a whole column with a whole years worth calendar. If you can answer this it would be the greatest thing :)
Dunno if you still need help but I think in your case you'd need to hard code some of the parameters. So maybe you'd needa create a table that has 1,2,...,11,12 to represent months, and each calendar table reference that table. So it's like the first half of his video where he referenced the calendar to the left table
The downside is that it's not fully dynamic, but you can at least get "semi-dynamic" on a yearly basis. And on the eomonth function, you adjust the -1 to 0, +1, +2....
Excellent video.What a magic.Idone it
.Thanks.
Hi Mike! Is there a way to create a Julian Date sheet in excel?
I do not know how to do that. But you can try:
mrexcel.com/forum
Hi Mike,
Thanks for your tutorials!
How can I mark leave days in an attendance sheet.
Criteria:-
1. Attendance cycle is 16th Nov 2015 to 15th Dec 2015.
2. Employees are A, B, C and D
3. A taken leave from 18th Nov for 4 days
4. B taken leave from 14th Dec for 2 days
5. C taken leave from 17th Nov for 29 days
I would request for your help.
Regards,
Hi I need help. The 1st, 2nd,3rd,4th,5th and 6th row has 5 row intervals in between. Example first cell is F7, the next row is F13. How to write the right formula? Thanks!
This was awesome, thanks!
The first of the month date is quite simple to get in older version by the way, just use the formula DATE(YEAR(range),MONTH(range),1).
Thanks! Very handy..
it`s working at 2010 excel
but not working at 2007
what can i do ???????
Perfect video. Looked at others and they werent as "appealing" and were just...."blah". Used the info and worked with no issues. But I am having a problem with using the calendar for my recurring bills. I have another worksheet with all my info and have "Account", "Amount", "Due Day". Due Day being just the day of the month its due. So I believe that I can use that info to create a formula to find the "Account" that corresponds with the "Due Day" no matter what month it is. Just curious on how to make that happen....
When you put in = then clicked the first 1/1/2015 the blue color made it hard to see what you entered in to get Feb to pop up. What was that?
+Krystal Mitchell It was B2 the location at the top. But it will show a Date I think... I think He had it set up before hand, because I had the same problem. It's what he shows you right after when you re-Hilight the cell and go to format cell. Make it say what it says in the custom area. i.e.. adjust the mmmyyy stuff in that field. :)
When I press CTRL + ENTER after doing the de-linking part at 15:25 the start date of the next month doesn't appear (I made my Calendar using July 2014 and so the start date of August 2014 should be on a Friday)
Great Calendar! Although, I would like to make 2 modification, but I need help. 1> I'd like to add a color to the current day? like light grey. Then I'd like to Plug in a by-weekly action. Somewhere along the lines of IF (This day passes) then add (This number) to (Number in cell F9) Also a variation If (This day Passes) Then Deduct (This number) from (the number in Cell F9) Or instead of Passes, ON this day... Also, I'd like to set up the days in a (from now to infinity) type of situation. PLEASE NOTE: I understood like 70 percent of your calendar video, the rest was just copied successfully.
i like your video
Hi,how you knew to put sunday first?I mean that calendar works only for that order.How you knew that you should not put Monday as first day of the week?How I can make the calendar to begin with Monday?
the weekday function can change it to monday as 1st day. just type: weekday(today(),2)
Thankyou.. Id been trying to figure this out myself returned to this video which gave me the basic concept for what I am trying to do o find this wonderful gem.
I followed along but some input does not work on 2013.
how can i add a reminder to this calender ????????????
Thank you so much!!!
Thanks so very much...
is there any way you can type into the calendar or is that not possible at all?
sure you can! If you want to hard code the content, you can write text(the whole expression he taught, "d") & "the content you wanna add".
if you want to reference it from a table, say you needa put in your work schedule into the calendar, then you build a separate table with dates and your schedule. then in the calendar expression you write the same expression again and use the vlookup function. E.g. text(expression, "d") & vlookup (....).
The beauty of his expression is that the result comes out as a date format, so it's compatible with vlookup when referencing your table.
Note that I've added the text function because once you use "&" in your formula, the expression turns from date format to general format. So eg 8th Nov 2016 becomes 42682.
PS: thank you ExcellsFun, super clear step by step walk through and unlike other websites/videos you actually explains why you use such functions in the formula. Subscribed!
Wow this is really amazing..
How do you create and allow text inside
I am sorry, but I do not do consulting.
How I do hard coding with calendar from this video
For back and forth dialog to get custom Excel solutions try:
mrexcel [dot] com/forum
How do I make monday first day of the week?
Stupid question, I remembered ;)
Of course!
I do not know.
I do know how to make Monday my first day of the week. UNFORTUNATELY when trying to use the long formula to authomatically create a new month I can't if I want my first day being Monday. Can anyone help me out? Thank you.
i made calendar in java and coped result automatically to text file.
it show all 12 month's dates with days.
then opened by excel and inserted int tables.
why when i put -1), then it come tgether with error typo? n if i didnt put ), it's fine, but i can't continue to the next *7. kindly assist me :/
ahh nevermind. i forgot to put ( before rows. haha. thanks. appreciated it :)
If you want a + or - or @ you must type a space before it
Hi again. May I ask for your email add? I have wanted to show you what is wrong with
my formula after following your EMT709 "Defined Name Dynamic Range Formula w/
Relative References & Data Extract. Thank you very much.
How do you subtract a larger month from a smaller month and get an absolute number for the month instead of "#NUM!".
thanks...
Super...wish you could come to work with me
Why am i doing this for fun? something is wrong...
This is sooo Cool..!
But I want Highlight those date, If I have Birth Day/Important dates list in next sheet if some of them is there in current month I wanna Highlight all those dates.
start @6:30 with 1.5x/2x speed and tell me you don't hear gordon Ramsey
excel is the most confusing thing i have ever seen in my life