Great Excel Tutorial, this is the best Dynamic Calendar I have seen. However, a bit too fast on the functions for a person trying to learn for me, and would like to suggest making those formula function larger. Looking on a laptop is way too small on our eyes unless we have a giant monitor. Would suggest taking it more step-by-step on the calculations page and also the calculation for the month of January as a bit too fast. Or even do a series that specializes in the calculations and functions itself like a step by step video approach to help make dynamic calendars and how the calculations can apply to other parts of the same file in Excel or sheets, like sale dates that can be changed just by changing the year. You did a great job explaining the formatting and it works for the dates and the weeks. February through April really made it clear on how to take care of re-entering. If possible slow down on what exactly you are doing when you take action on the keyboard as we don't see it. May till the end you could probably just fast forward as it repeats February through April. Awesome video.
Just came across your video and thought I would try it like so many others I have tried. At last one that seems to work. Even able to print on A5 landscape a (six month on each of two pages as I am a bit sight impaired) version that suits me fine. Thank you so much for your time and effort.
Thank you for watching our video. We are glad that your requirement has been fulfilled and you are welcome. You can also download our template from the below link which has 23 different calendar designs: indzara.com/2023/01/excel-calendar-2023-with-23-formats-free-download/ Best wishes.
Thank you! I was unable to get my I_YR function to work, I had to type the year in manually as the first month kept giving me a #VALUE error. I know I will have to change it yearly but this is still a big help. I was making a school year calendar (August-May) and it still worked great. I did have to slow down my playback to .75 but that is only because I don't know the functions and had to pause and flip back & forth several times. Thanks for the video!
You are welcome. The I_YR is a named range of the cell V1. This name range is set on time line 2:27 in the video left to the formula bar. The named ranges can be checked and modified from Formula ribbon -> Define Name or name manager. You can share your sheet with some screenshots highlighting your concern to support@indzara.com for more support. Best wishes.
So far this tutorial has been wonderful. I am stuck at 17:28 in the video. When I select the 42 square and then put in edit mode I press control, Enter but all it does is add a line to my formula. It does not populate the ## signs that you show. What am I doing wrong? Thank you!
Do not know how you got to edit mode before placing days in calendar. I'm on a Mac & when I push control & enter. Nothing happens. Otherwise this is an awesome tutorial. I'm just hung up. Thank you. You are a great teacher!
You will need to press F2 (Mac fn+F2) to enter edit mode and then press CTRL+ENTER to duplicate the formulas. You can also copy paste the cells to duplicate formulas. I just tried in Mac and it works. Best wishes.
Thank you SO much! January is perfect. Then I went to copy and paste & a few random numbers appeared in February. The copied formula appears to be there though. Any idea what I may have done wrong. Thank you in advance. I really appreciate your quick response so far.
@@JeriSchultheiss We are glad that your concern has been resolved. If you face any further issue, you can share your sheet to support@indzara.com to check further. Best wishes.
You will need to have the formulas for 366 rows for leap year and the formulas will automatically show 28 days for Feb if it is not a leap year and 29 days for Feb if it is a leap year. Best wishes.
I think you did very well. I do have a question. I have issues when trying to do the day by formulas in merged cells. Do you know why this is happening? It works perfectly in a single cell as i have done it both ways. Thanks Awesome video.
Thank you for sharing your valuable feedback. If cell A1 and B1 are merged and when a formula is referring to a merged cell, it might be referring to B1 or both A1:B1. Where as it should be A1, which will hold the actual formula. Requesting to check the same in your formula and for further queries, requesting to share your file along with some screenshots highlighting your concern to support@indzara.com. Best wishes.
You are welcome. In the Help sheet, column B where we calculate Weeknumber please use parameter 2 in WEEKNUM function. Similarly column C when we calculate the weekday use parameter 2 in WEEKDAY function. Then, change the labels in the calendar sheet to start week from MON instead of SUN. Best wishes.
For anyone struggling to do this: Column B: =WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1)+1, 2)+1 Column C: =IFERROR(CONCATENATE(MONTH(A2)," ",B2," ",WEEKDAY(A2,2)),"")
Very nice tutorial, I was able to build the exact calendar I was looking to create. You are very knowledgable with Excel operations, the video was very fast, but through pause and rewind I was able to make it work. One question though, maybe the difference is in your version and mine. I have version 16.43. In the beginning when your created Sheet 1, you used a formula =DATE(I_YR,1,1) which allows the year to be changed from year to year. When I entered this formula in my version I keep getting an error. I had to use =DATE(2024,1,1) just to keep it moving. Because of this my dynamic year does not work. How can I fix this in my version?
Thank you for watching our video and sharing your valuable feedback. Regarding I_YR, It is a named range. To create the same select the cell containing the year value in the calendar sheet, then goto Formula ribbon -> Named Range -> New and name it as I_YR. Best wishes.
Hi, thanks for guidance us in Excel. I want to highlight 3 days in a week on my own required basis. As I already highlight Saturday and Sunday but additional 3 working days highlight.
You are welcome. Please check our next part of the video at the below link to learn how to highlight events: th-cam.com/video/b0lWFlhAj3k/w-d-xo.html You can also download the free template at the below link: indzara.com/2023/01/excel-calendar-2023-with-23-formats-free-download/ The premium version of the template is available at the below link: indzara.com/product/event-calendar-maker/ Best wishes.
Thank you for sharing your valuable feedback. Yes, you can do it in Google Sheets as well. Following is the link to the same template in Google Sheets: indzara.com/2020/03/2020-calendar-free-google-sheet-template/ Best wishes.
Thank you for watching our video. Requesting to share your sheet and share some screenshot highlighting the changes done to support@indzara.com to check further. You can also purchase our Event calendar maker template at the below link where you can change the week starting day: indzara.com/product/event-calendar-maker/ Best wishes.
Hello. What a great video. I loved it. Every step in detail. Congratulations. I just would like, if possible, for you to help me somehow to set the calendar to start on a Monday instead of a Sunday. I've tried already, but there are blank cells in the middle. Once again, congratulations on your work.
Thank you sharing your valuable feedback. In the timeframe 11:53, the formula syntax will show the return type. On entering , you will be provided with the list of possible return type. In which you need to choose the return type as Monday (2). Note: the same return type need to be provided in both the weeknum formula. Best wishes.
@@indzara Hello, good evening. I tried to change but couldn't get the desired result. I used this formula =IFERROR(CONCATENATE(MONTH(A2)," ",B2," ",WEEKDAY(A2,2))," ") and also this one =WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1)+1)+1. It puts the 1st day on a Sunday. Perfect. But the month should end on the 31st of January, but it doesn't, it reaches the 30th and fills with 30 until the end of the blank cells (42 cells). I would really love to have the week start on Monday. I've never seen a week starting on Sunday. Thank you for your help and sorry.
There are two WEEKNUM formula in the below formula present in timeframe 11:53: =WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1)+1)+1 You need to ensure both the WEEKNUM formula to start from Monday as shown below: =WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1)+1,2)+1 Best wishes.
Thank you for the walk through :-) It would be useful to know how to add colour coding (eg for weekends and important dates) on this i.e. similar to your main multiple calendar template. Many thanks
You are welcome. I covered one part - weekends - in a previous video. th-cam.com/video/XcdvOlimG3k/w-d-xo.html I can post one in the future about event dates as well. Best wishes.
How do the formulas need to be modified to reflect a year-long ACADEMIC calendar that has months from different years? I.e/, typically in the USA, school starts in August of one year, but continues through May of the following year. I appreciate your insight. Thank you.
=IFERROR(INDEX(C_DATES,MATCH(CONCATENATE(MONTH($B$3),"",ROW(B5)-ROW(B$4),"",COLUMN(B5)-COLUMN($A5)),C_Key,0)),"") there were no number display in January format can you help me to do this Thanks!!
Thank you for watching our video. Following is the formula used for the 366th day of a year in row 367: =IF(Month(A366+1)=Month(A366),A366+1,"") Best wishes.
Thank you for showing interest in our template. The calendar will work for any year from 1900. The minimum valid date in excel is 01-01-1900 also equivalent to number 1. Best wishes.
Around 17:23 in the video, when I select the 42 cell and pressed Ctrl + Enter, my cells all appeared blank unlike yours which had ##. Please, how can I change this?
Hello sir i totally followed your videos and instructions and made a calendar for my project but there is a problem after getting dates it is showing 1 day ahead..means 1 January 2019 starts with tue but in this calendar start with Wednesday, similarly goes with every month... please help me sir
Thank you for watching our video. Please share your sheet to us at the below link to assist further: support.indzara.com/support/tickets/new Best wishes.
Great video but I’m stuck at the edit mode enter part. It never seem to work. I’m using a Mac and I’ve pressed the fn+f2 then Ctrl and enter but still I’m seeing blank cells unlike yours with the ## what is the problem?
Thank you for sharing your valuable feedback. I tested in mac and it works. Following are the steps: 1. Enter the formula. 2. Select the 42 cells as instructed starting from the cell where the formula is entered. 3. Then press fn+F2 to enter edit mode in mac. 4. Then press control+Enter or command+Enter key to duplicate the formulas to the selection. You can also select the cells and press CTRL+D and CTRL+R to duplicate the formulas column wise (CTRL+D) and row wise (CTRL+R). You can also copy paste the formula cell in the 42 cells and it will provide you the same output. Best wishes.
First, thank you for this amazing tutorial. I've learned a lot of new things while following along with this tutorial. Next, I want to mention that I encountered that the formula =IFERROR(INDEX(C_DATES,MATCH(CONCATENATE(MONTH($B$3)," ",ROW(B5)-ROW(B$4)," ",COLUMN($A5)),C_KEY,0)),"") would end up showing the week numbers instead of the actual day(s) of the month. So, to better explain, I did this tutorial for the year 2021. And the result I got after entering the above-mentioned formula is that Friday and Saturday of January 2021 would show the number 1. And the following week would show number 2, and so forth. Can you or anyone help me, please? Thanks in advance.
@@indzara I've noticed the same thing going to year 2022. January is supposed to start on a Friday (which it does in the calendar) but I also get Sunday 2021 showing as 1 and Monday as 2. if you have found the issue can you please post the solution? Thank you.
@@oscarbecerra8151 For weekdays starting with Monday (change the start day on actual calendar to monday, For anyone struggling to do this: Column B: =WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1)+1, 2)+1 Column C: =IFERROR(CONCATENATE(MONTH(A2)," ",B2," ",WEEKDAY(A2,2)),"") i did this and mine is working fine
Thank you. To enter into edit mode, select a cell and press F2. Or click inside the formula bar. Does this help? Can you please explain what you mean by stuck? Best wishes.
Thank you for showing interest in our template. Yes, you can insert rows between each weeks but method of taking the row number inside the formula will differ. Best wishes.
@@indzara Do you perhaps have an example for that? It got it's part of calculation to get the second number of the lookup key. But when i move the table down it either disappears or cant get it to work. Or course i can fill in it manually but that defeats the purpose :].
I love this calendar, but what if i wanted to enter some kind of event for a specific day because when you doubler click on the cell, it doesn't let me enter any text as there is a formula there. I tried to have the dates skip rows but doesn't work. How do i resolve this problem?
Thank you for showing interest in our templates. Requesting to check our following video to highlight events: th-cam.com/video/b0lWFlhAj3k/w-d-xo.html You can also download template from the following link to highlight upto 100 events. indzara.com/2013/06/all-purpose-calendar-maker-excel-template/ You can purchase our premium Event Calendar Maker from following link: indzara.com/product/event-calendar-maker/ Best wishes.
This is a great video! Thank you so much! Do you have a video that shows how to add the week # to the left of each month row? So I can show week 1-52 and see those? :)
You are welcome. Thanks for your feedback. For week number in year, please use weeknum function. Example =WEEKNUM(H5,1) where H5 is the last day of the week. We use 1 to reflect that Sunday is the start day of each week. Please let us know if this addresses your question. If further questions, please email support@indzara.com with your file. Thanks & Best wishes.
@@indzara Thank you that should work nicely! :) Do you know what's the best way to automatically calculate if let's say there isn't a date in the the Saturday spot... b/c the last day was Friday for that month instead and so on.
@@lauraswansondesign9729 We would have to add that logic to the formula. If you have tried that and need us to assist, please email file with question to support@indzara.com Best wishes.
Dear Sir, Great Video but I am facing some trouble in getting values in January after using match function (17:00 time). My cells are showing blank value. Please help me with problem. Thanks in advance.
I have followed your video right up until 19:22 then when i go and copy the formulas from January to February it will not start on the proper date. In January 2019 it ends on Thursday the 31st. But when i copy the cells to february and change the cell dates to match the month and column values as you stated, it will not start February on the right date, it just copies the January information. Any help would be good. I am lost. I triple checked all the formulas and mine are identical to yours but it will not work
Ensure the range is selected from B5 and then press F2 or click formula bar to enter edit mode. Then press CTRL + ENTER key to extend the formula. Best wishes.
Hoi Indzara, i watched you video with much respect. But, i have a problem. In Europe the week starts on mondays so i want to change de lay out. This was not a problem but the date's are,nt correct now. Can you help please to solve my problem? Thanks!!
Thank you for watching our video. You can also do CTRL+R and CTRL+D to duplicate the formulas to right and down. To check further on your issue with F2 and CTRL+ENTER, requesting to share your sheet with some screenshots showing the steps you follow to support@indzara.com to check further. Best wishes.
At 17 minutes, when I hit enter, it comes out to zero and I can't get the calendar days to come up. The only difference between yours and mine is the first formula. It wouldn't let my put the (i_yr) instead I had yo put the actual year. Would that cause me to have this problem? My headers are also a Monday start.
If you put the actual year as hard coded value, it should work for that specific year. Is it possible to email the file to support@indzara.com for review? Thanks & Best wishes.
Hi Indzara, Do you have a template to manage gym membership with payment due days etc PS I know this is the wrong place to ask but cant find anywhere else
Hi @indzara, first of all thank you for your respond to my email last week, really appreciated it; and the issue is solved now. Second, can you please make a video of of how i can display a cell value in another cell once i click on it. For example: If i click on date 4th Jan in the calendar, i want this date to display in cell AA5 and if i click on date 7th Aug in the calendar, i want it to display the value in cell AA9...and so on. Regards, Samura
Thank you for watching our video. In excel all dates are numbers by default, hence the formulas will be common for all year. If you have changed the weeknum formula to start on Monday, Ensure both weeknum formulas are edited to start on Monday. If you have both weeknum formula to start on Monday and still facing issue, requesting to share your sheet to support@indzara.com to check further. Best wishes.
It will be added automatically with the existing formula. Ensure you have 366 days in the Calculation tab. If it is not displayed, requesting to share your sheet to support@indzara.com to check further. Best wishes.
hi, is this work for google sheets I tried but not working "=INDEX(Date!A2:A366,MATCH(CONCATENATE(MONTH($B$3)," ",ROW(B5)-ROW(B$4)," ",COLUMN((B5)-COLUMN($A5)),Date!C2:C366,0)))"
Thank you for watching our videos. Yes, this will work for Google Sheet as well. You can share your sheet to support@indzara.com along with some screenshot highlighting the error to check further. Following is the link to the finished Google Sheet Calendar template for your reference: indzara.com/2020/03/2020-calendar-free-google-sheet-template/ Best wishes.
![[LIVE] MCHOICE & MINT AWARDS 2024 at TRUE ICON HALL, ICONSIAM](http://i.ytimg.com/vi/wMewUeT5nbY/mqdefault.jpg)
Great Excel Tutorial, this is the best Dynamic Calendar I have seen. However, a bit too fast on the functions for a person trying to learn for me, and would like to suggest making those formula function larger. Looking on a laptop is way too small on our eyes unless we have a giant monitor. Would suggest taking it more step-by-step on the calculations page and also the calculation for the month of January as a bit too fast. Or even do a series that specializes in the calculations and functions itself like a step by step video approach to help make dynamic calendars and how the calculations can apply to other parts of the same file in Excel or sheets, like sale dates that can be changed just by changing the year. You did a great job explaining the formatting and it works for the dates and the weeks. February through April really made it clear on how to take care of re-entering. If possible slow down on what exactly you are doing when you take action on the keyboard as we don't see it. May till the end you could probably just fast forward as it repeats February through April. Awesome video.
Just created my calendar. Thank you so much for this informative video. Definitely subscribing!
Congratulations. You are welcome and thank you for subscribing.
Best wishes.
This is one of the best Excel tutorials I have ever seen. It helped so much! Flawless presentation skills, as well. Subscribed.
Just came across your video and thought I would try it like so many others I have tried.
At last one that seems to work. Even able to print on A5 landscape a (six month on each of two pages as I am a bit sight impaired) version that suits me fine. Thank you so much for your time and effort.
Thank you for watching our video.
We are glad that your requirement has been fulfilled and you are welcome.
You can also download our template from the below link which has 23 different calendar designs:
indzara.com/2023/01/excel-calendar-2023-with-23-formats-free-download/
Best wishes.
this is exactly what I needed! thank you so much!
You are welcome.
Best wishes.
Excellent tutorial! Thanks for detailing every step and explaining clearly. Much appreciated!
We are glad that you liked our content and you are welcome.
Best wishes.
Great video! Yes, very useful. Thanks so much! I also learned a lot from it. Speed was great, not too fast and not too slow.
Thank you for sharing your valuable feedback and you are welcome.
Best wishes.
Thank you! I was unable to get my I_YR function to work, I had to type the year in manually as the first month kept giving me a #VALUE error. I know I will have to change it yearly but this is still a big help. I was making a school year calendar (August-May) and it still worked great. I did have to slow down my playback to .75 but that is only because I don't know the functions and had to pause and flip back & forth several times. Thanks for the video!
You are welcome.
The I_YR is a named range of the cell V1. This name range is set on time line 2:27 in the video left to the formula bar.
The named ranges can be checked and modified from Formula ribbon -> Define Name or name manager.
You can share your sheet with some screenshots highlighting your concern to support@indzara.com for more support.
Best wishes.
Amazing help! Thank you!
We are glad that the video was helpful. You are welcome.
Best wishes.
So far this tutorial has been wonderful. I am stuck at 17:28 in the video. When I select the 42 square and then put in edit mode I press control, Enter but all it does is add a line to my formula. It does not populate the ## signs that you show. What am I doing wrong? Thank you!
Great video! Very useful! I've learned a lot from it!
Glad it was helpful!
oh my god, it's so informative
thank you
Thank you for sharing your valuable feedback. You are welcome.
Best wishes.
Do not know how you got to edit mode before placing days in calendar. I'm on a Mac & when I push control & enter. Nothing happens. Otherwise this is an awesome tutorial. I'm just hung up. Thank you. You are a great teacher!
You will need to press F2 (Mac fn+F2) to enter edit mode and then press CTRL+ENTER to duplicate the formulas. You can also copy paste the cells to duplicate formulas. I just tried in Mac and it works.
Best wishes.
Thank you SO much! January is perfect. Then I went to copy and paste & a few random numbers appeared in February. The copied formula appears to be there though. Any idea what I may have done wrong. Thank you in advance. I really appreciate your quick response so far.
@@indzara Got it! This is GREAT! Thank you!
@@JeriSchultheiss We are glad that your concern has been resolved.
If you face any further issue, you can share your sheet to support@indzara.com to check further.
Best wishes.
@@indzara Hi There! How do you enter edit mode in windows?
What would be the formula to put on the 367th row if we are creating a calendar for the leap year. example 2024?
You will need to have the formulas for 366 rows for leap year and the formulas will automatically show 28 days for Feb if it is not a leap year and 29 days for Feb if it is a leap year.
Best wishes.
Thank you so much 🥲☺🧡🙏😍😘
You are welcome.
Best wishes.
@@indzara thank you 🧡☺
I think you did very well. I do have a question. I have issues when trying to do the day by formulas in merged cells. Do you know why this is happening? It works perfectly in a single cell as i have done it both ways. Thanks Awesome video.
Thank you for sharing your valuable feedback.
If cell A1 and B1 are merged and when a formula is referring to a merged cell, it might be referring to B1 or both A1:B1. Where as it should be A1, which will hold the actual formula.
Requesting to check the same in your formula and for further queries, requesting to share your file along with some screenshots highlighting your concern to support@indzara.com.
Best wishes.
hI.. thanks for the walk through. found it very useful. what changes would I need to make to have my calendar start on a Monday, instead of a sunday?
You are welcome.
In the Help sheet, column B where we calculate Weeknumber please use parameter 2 in WEEKNUM function. Similarly column C when we calculate the weekday use parameter 2 in WEEKDAY function.
Then, change the labels in the calendar sheet to start week from MON instead of SUN.
Best wishes.
For anyone struggling to do this:
Column B: =WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1)+1, 2)+1
Column C: =IFERROR(CONCATENATE(MONTH(A2)," ",B2," ",WEEKDAY(A2,2)),"")
@@aldomir thank you, i was trying to do something like this, but i think i was just putting the 2 in the wrong place
Thanks a lot sir
You are welcome.
Best wishes.
Very nice tutorial, I was able to build the exact calendar I was looking to create. You are very knowledgable with Excel operations, the video was very fast, but through pause and rewind I was able to make it work. One question though, maybe the difference is in your version and mine. I have version 16.43. In the beginning when your created Sheet 1, you used a formula =DATE(I_YR,1,1) which allows the year to be changed from year to year. When I entered this formula in my version I keep getting an error. I had to use =DATE(2024,1,1) just to keep it moving. Because of this my dynamic year does not work. How can I fix this in my version?
Thank you for watching our video and sharing your valuable feedback.
Regarding I_YR,
It is a named range. To create the same select the cell containing the year value in the calendar sheet, then goto Formula ribbon -> Named Range -> New and name it as I_YR.
Best wishes.
Hi, thanks for guidance us in Excel. I want to highlight 3 days in a week on my own required basis. As I already highlight Saturday and Sunday but additional 3 working days highlight.
You are welcome.
Please check our next part of the video at the below link to learn how to highlight events:
th-cam.com/video/b0lWFlhAj3k/w-d-xo.html
You can also download the free template at the below link:
indzara.com/2023/01/excel-calendar-2023-with-23-formats-free-download/
The premium version of the template is available at the below link:
indzara.com/product/event-calendar-maker/
Best wishes.
This is an amazing tutorial. Can you do this with google sheets?
Thank you for sharing your valuable feedback. Yes, you can do it in Google Sheets as well.
Following is the link to the same template in Google Sheets:
indzara.com/2020/03/2020-calendar-free-google-sheet-template/
Best wishes.
i tried to change it to make Monday first day of week , but it completely messes up EVERYTHING, how do I solve this..?
Thank you for watching our video.
Requesting to share your sheet and share some screenshot highlighting the changes done to support@indzara.com to check further.
You can also purchase our Event calendar maker template at the below link where you can change the week starting day:
indzara.com/product/event-calendar-maker/
Best wishes.
Hello. What a great video. I loved it. Every step in detail. Congratulations. I just would like, if possible, for you to help me somehow to set the calendar to start on a Monday instead of a Sunday. I've tried already, but there are blank cells in the middle. Once again, congratulations on your work.
Thank you sharing your valuable feedback.
In the timeframe 11:53, the formula syntax will show the return type. On entering , you will be provided with the list of possible return type. In which you need to choose the return type as Monday (2). Note: the same return type need to be provided in both the weeknum formula.
Best wishes.
@@indzara 👋 I'm going to try it. Then I give you feed. Thank you so much
@@indzara Hello, good evening. I tried to change but couldn't get the desired result. I used this formula =IFERROR(CONCATENATE(MONTH(A2)," ",B2," ",WEEKDAY(A2,2))," ") and also this one =WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1)+1)+1. It puts the 1st day on a Sunday. Perfect. But the month should end on the 31st of January, but it doesn't, it reaches the 30th and fills with 30 until the end of the blank cells (42 cells). I would really love to have the week start on Monday. I've never seen a week starting on Sunday. Thank you for your help and sorry.
There are two WEEKNUM formula in the below formula present in timeframe 11:53:
=WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1)+1)+1
You need to ensure both the WEEKNUM formula to start from Monday as shown below:
=WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1)+1,2)+1
Best wishes.
Very good video! May I also know how what will be the formula if I were to start from Monday? It would be a great help for me to know. Thanks!
Thank you for watching our video,
Requesting to share your sheet to support@indzara.com to modify the start date as Monday.
Best wishes.
Change WEEKNUM(X) to WEEKNUM(X,2) in Calculations sheet, both in Column B and C
Thank you for the walk through :-) It would be useful to know how to add colour coding (eg for weekends and important dates) on this i.e. similar to your main multiple calendar template. Many thanks
You are welcome.
I covered one part - weekends - in a previous video.
th-cam.com/video/XcdvOlimG3k/w-d-xo.html
I can post one in the future about event dates as well.
Best wishes.
@@indzara Many thanks
How do the formulas need to be modified to reflect a year-long ACADEMIC calendar that has months from different years? I.e/, typically in the USA, school starts in August of one year, but continues through May of the following year. I appreciate your insight. Thank you.
=IFERROR(INDEX(C_DATES,MATCH(CONCATENATE(MONTH($B$3),"",ROW(B5)-ROW(B$4),"",COLUMN(B5)-COLUMN($A5)),C_Key,0)),"") there were no number display in January format can you help me to do this Thanks!!
Thank you for watching our video.
Please share your sheet at the below link to assist further:
support.indzara.com/support/tickets/new
Best wishes.
Can you type the formula for leap year please on for the date formula sheet around 9:42? thanks
Thank you for watching our video.
Following is the formula used for the 366th day of a year in row 367:
=IF(Month(A366+1)=Month(A366),A366+1,"")
Best wishes.
@@indzara Thanks ! just what I needed.
@@ShellyCreatesIt You are welcome. Best wishes.
What if I change it to year 1600 or 2050 will it change or not?
Thank you for showing interest in our template.
The calendar will work for any year from 1900. The minimum valid date in excel is 01-01-1900 also equivalent to number 1.
Best wishes.
Around 17:23 in the video, when I select the 42 cell and pressed Ctrl + Enter, my cells all appeared blank unlike yours which had ##. Please, how can I change this?
Please email your file to support@indzara.com. I can review and respond
Best wishes.
@@indzara Thanks
Hi did you find a solution to this problem? Mine is the same . THANKS
@@jacdru5888 I am having the same issue. Formula has been entered but no numbers are appearing. Did you get an answer?
nice
Thank you for sharing your feedback.
Best wishes.
Hello sir i totally followed your videos and instructions and made a calendar for my project but there is a problem after getting dates it is showing 1 day ahead..means 1 January 2019 starts with tue but in this calendar start with Wednesday, similarly goes with every month... please help me sir
Thank you for watching our video.
Please share your sheet to us at the below link to assist further:
support.indzara.com/support/tickets/new
Best wishes.
Great video but I’m stuck at the edit mode enter part. It never seem to work. I’m using a Mac and I’ve pressed the fn+f2 then Ctrl and enter but still I’m seeing blank cells unlike yours with the ## what is the problem?
Thank you for sharing your valuable feedback.
I tested in mac and it works. Following are the steps:
1. Enter the formula.
2. Select the 42 cells as instructed starting from the cell where the formula is entered.
3. Then press fn+F2 to enter edit mode in mac.
4. Then press control+Enter or command+Enter key to duplicate the formulas to the selection.
You can also select the cells and press CTRL+D and CTRL+R to duplicate the formulas column wise (CTRL+D) and row wise (CTRL+R). You can also copy paste the formula cell in the 42 cells and it will provide you the same output.
Best wishes.
First, thank you for this amazing tutorial. I've learned a lot of new things while following along with this tutorial. Next, I want to mention that I encountered that the formula =IFERROR(INDEX(C_DATES,MATCH(CONCATENATE(MONTH($B$3)," ",ROW(B5)-ROW(B$4)," ",COLUMN($A5)),C_KEY,0)),"") would end up showing the week numbers instead of the actual day(s) of the month. So, to better explain, I did this tutorial for the year 2021. And the result I got after entering the above-mentioned formula is that Friday and Saturday of January 2021 would show the number 1. And the following week would show number 2, and so forth. Can you or anyone help me, please? Thanks in advance.
Requesting to share your sheet with some screenshot highlighting your concern to support@indzara.com to check further.
Best wishes.
@@indzara I've noticed the same thing going to year 2022. January is supposed to start on a Friday (which it does in the calendar) but I also get Sunday 2021 showing as 1 and Monday as 2. if you have found the issue can you please post the solution? Thank you.
@@oscarbecerra8151 January 2022 starts on a Saturday ...
@@oscarbecerra8151 maybe you need to change the parameter of the start of week (mine is working mine, i have mine starting from monday)
@@oscarbecerra8151 For weekdays starting with Monday (change the start day on actual calendar to monday,
For anyone struggling to do this:
Column B: =WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1)+1, 2)+1
Column C: =IFERROR(CONCATENATE(MONTH(A2)," ",B2," ",WEEKDAY(A2,2)),"")
i did this and mine is working fine
I really enjoyed this video. It has helped me a lot but the only part I'm stuck is when it comes down to edit mode. Can you please help?
Thank you.
To enter into edit mode, select a cell and press F2. Or click inside the formula bar.
Does this help? Can you please explain what you mean by stuck?
Best wishes.
This was super helpful. Any chance there is a way to insert rows between each week to allow for text, or make the dates every other row?
Thank you for showing interest in our template.
Yes, you can insert rows between each weeks but method of taking the row number inside the formula will differ.
Best wishes.
@@indzara What does that look like inside the formula?
@@indzara Do you perhaps have an example for that? It got it's part of calculation to get the second number of the lookup key. But when i move the table down it either disappears or cant get it to work. Or course i can fill in it manually but that defeats the purpose :].
I love this calendar, but what if i wanted to enter some kind of event for a specific day because when you doubler click on the cell, it doesn't let me enter any text as there is a formula there. I tried to have the dates skip rows but doesn't work. How do i resolve this problem?
Thank you for showing interest in our templates.
Requesting to check our following video to highlight events:
th-cam.com/video/b0lWFlhAj3k/w-d-xo.html
You can also download template from the following link to highlight upto 100 events.
indzara.com/2013/06/all-purpose-calendar-maker-excel-template/
You can purchase our premium Event Calendar Maker from following link:
indzara.com/product/event-calendar-maker/
Best wishes.
This is a great video! Thank you so much! Do you have a video that shows how to add the week # to the left of each month row? So I can show week 1-52 and see those? :)
You are welcome. Thanks for your feedback.
For week number in year, please use weeknum function. Example =WEEKNUM(H5,1)
where H5 is the last day of the week. We use 1 to reflect that Sunday is the start day of each week.
Please let us know if this addresses your question. If further questions, please email support@indzara.com with your file.
Thanks & Best wishes.
@@indzara Thank you that should work nicely! :) Do you know what's the best way to automatically calculate if let's say there isn't a date in the the Saturday spot... b/c the last day was Friday for that month instead and so on.
@@lauraswansondesign9729 We would have to add that logic to the formula. If you have tried that and need us to assist, please email file with question to support@indzara.com
Best wishes.
Dear Sir, Great Video but I am facing some trouble in getting values in January after using match function (17:00 time). My cells are showing blank value. Please help me with problem. Thanks in advance.
Requesting to share your sheet to support@indzara.com along with the screenshot of your concern to check further.
Best wishes.
GREAT
Thanks for your feedback. Happy New Year!
Best wishes.
Also when manually endtering edit mode what keys are you pressing to enter this mode
Thank you for watching our video.
Press F2 to enter to edit mode. In some laptops it will be Fn+F2.
Best wishes.
I have followed your video right up until 19:22 then when i go and copy the formulas from January to February it will not start on the proper date. In January 2019 it ends on Thursday the 31st. But when i copy the cells to february and change the cell dates to match the month and column values as you stated, it will not start February on the right date, it just copies the January information. Any help would be good. I am lost. I triple checked all the formulas and mine are identical to yours but it will not work
Thank you for watching our video.
Requesting to share your sheet to support@indzara.com to check further on your concern.
Best wishes.
I lost at 17:27. You said you chose edit mode but I can't follow it. Please guide me. Thanks
Ensure the range is selected from B5 and then press F2 or click formula bar to enter edit mode. Then press CTRL + ENTER key to extend the formula.
Best wishes.
Hoi Indzara, i watched you video with much respect. But, i have a problem. In Europe the week starts on mondays so i want to change de lay out. This was not a problem but the date's are,nt correct now. Can you help please to solve my problem? Thanks!!
Thank you.
Please email the file to support@indzara.com and specify the formula which needs review.
Best wishes.
Hi , When I F2 edit mode ctrl&enter nothing populates . Please advise
Thank you for watching our video.
You can also do CTRL+R and CTRL+D to duplicate the formulas to right and down.
To check further on your issue with F2 and CTRL+ENTER, requesting to share your sheet with some screenshots showing the steps you follow to support@indzara.com to check further.
Best wishes.
Can you plz tell me how did you do edit mode and control enter. thanks
When you have clicked on a cell, press F2 to enter Edit Mode. Or you can use the mouse to click inside the formula bar to edit.
Best wishes.
At 17 minutes, when I hit enter, it comes out to zero and I can't get the calendar days to come up. The only difference between yours and mine is the first formula. It wouldn't let my put the (i_yr) instead I had yo put the actual year. Would that cause me to have this problem? My headers are also a Monday start.
If you put the actual year as hard coded value, it should work for that specific year. Is it possible to email the file to support@indzara.com for review?
Thanks & Best wishes.
Indzara yes! I will do that! Thank you so much
Hi Indzara,
Do you have a template to manage gym membership with payment due days etc
PS I know this is the wrong place to ask but cant find anywhere else
Sorry, I don't have one designed for that. However, what features should be in such a template? Please specify.
Best wishes.
Please tell the key bindings to go to edit mode
F2 key
Calendar Design 16 How do I get this Free Template. I went to your link but there is no download available
Thank you for showing interest in our template. You will get a response from our support team shortly.
Hi @indzara, first of all thank you for your respond to my email last week, really appreciated it; and the issue is solved now.
Second, can you please make a video of of how i can display a cell value in another cell once i click on it.
For example:
If i click on date 4th Jan in the calendar, i want this date to display in cell AA5 and if i click on date 7th Aug in the calendar, i want it to display the value in cell AA9...and so on.
Regards,
Samura
You are welcome.
That would require macros and I am not familiar with them yet.
Best wishes.
Formula does not work for 2021. It returns the firs week of August as '0' week rather than '1' in the Lookup Week Number of Month.
This conclusion may only apply when Monday is the first day of the week.
Thank you for watching our video.
In excel all dates are numbers by default, hence the formulas will be common for all year.
If you have changed the weeknum formula to start on Monday, Ensure both weeknum formulas are edited to start on Monday.
If you have both weeknum formula to start on Monday and still facing issue, requesting to share your sheet to support@indzara.com to check further.
Best wishes.
Can you post the formula for the 367 line, I cannot see it??
Can you actually post all the formulas, they are difficult to read
Can you please send your request to support@indzara.com ? - Our support team will get in touch with you within one working day.
Hi,
How to add Feb29th date in calendar
Share me the formula
It will be added automatically with the existing formula. Ensure you have 366 days in the Calculation tab. If it is not displayed, requesting to share your sheet to support@indzara.com to check further.
Best wishes.
must we highlight
please clarify your question.
Best wishes.
hi, is this work for google sheets I tried but not working "=INDEX(Date!A2:A366,MATCH(CONCATENATE(MONTH($B$3)," ",ROW(B5)-ROW(B$4)," ",COLUMN((B5)-COLUMN($A5)),Date!C2:C366,0)))"
Thank you for watching our videos.
Yes, this will work for Google Sheet as well. You can share your sheet to support@indzara.com along with some screenshot highlighting the error to check further.
Following is the link to the finished Google Sheet Calendar template for your reference:
indzara.com/2020/03/2020-calendar-free-google-sheet-template/
Best wishes.
can't find column
Please clarify what column is missing.
Best wishes.
Date(i_yr,1,1) not working
What is the value for I_YR?
Best wishes.
Way too fast for me
Sorry about that. Is there a specific step you would like clarification on?
I will try to do better in future videos.
Best wishes.
You cant even see the formulas that is being put in.
Thanks for your feedback.
Best wishes.
What's that clicking noise. Really annoying. Fix your mic
Thanks for your feedback. Will take care of it in future videos.
Best wishes.