Build an Automatic Calendar with Macro in Excel
HTML-код
- Опубликовано: 9 мар 2018
- Welcome to our Excel tutorial where we'll guide you through building a dynamic calendar with Excel, where weekends are automatically highlighted in vibrant colors!
In this step-by-step guide, we'll demonstrate how to set up conditional formatting rules to effortlessly distinguish weekends from weekdays. With just a few simple formulas and formatting tricks, you'll be able to visualize your calendar more intuitively, making planning and scheduling tasks a breeze.
Whether you're a student, professional, or simply someone who loves organizing their time effectively, this tutorial is perfect for anyone looking to enhance their Excel skills. Follow along as we unlock the power of conditional formatting to make your calendars functional and visually appealing too!
excel-tutorial.com/calendar-i...
If you want to save your data and also recall them, you can use this file: excelexercise.onfastspring.co...
Thank you for a very clear and easy to follow tutorial. I was able to follow along and create an attendance log of my own. It was a great learning experience. I greatly appreciate it.
Thank youuuu so much!!!! I’ve been looking this for AGES!!! Very helpful guidance! Please teach more! Love your tutorials
one of the best and most useful videos, thank you sooooo much I would have given a SUPER LIKE for this
Excellent ! Meme après 4 ans, toujours extrêmement utile. Merci Frédéric !
Thank you!!!! This is exactly what I need to make things so much easier. Last year, I made individual worksheets for each month. At some point, I decided to keep a running total of everything I was capturing. I found myself having to input the information into ALL of the sheets to keep the formula accurate. So much easier!!!
Hi Luci, thanks a lot for your message. I'm glad if I could help you and save your time.
I have created a similar calendar but this one save the data when you move to another month. The file cost USD 9.99$. Will you be interested by such program (I will make the video tomorrow)
Thank you for creating and sharing the process step by step.
Very Very Clear Explanation and to the point Explanations of what is needed
Great video! Very clear and I followed it properly. I have a timesheet but not automated and now it is, thanks for this.
Thank you for the very easy to follow instruction! I really loved it.
Thanks 😊
Thank you very much for your informative video, this helped alot with making my own personal calender.
Thank you very much, found it extremely useful.:)
Thanks a lot, very appreciated it!
Awesome buddy Thank you so much.
Good thing is to clear data after changing the year or the months. Good job Sir.
I have applied the same but my previous month data also getting cleared. Can you advise how to fix it?
@@bebeautifulbeu3259 do u know now how to fix it😢😢
Hi, fantastic tutorial, thank you! Do you have a tutorial or any guides on writing the formula list for public holidays? I am based in Australia and would like to include them.
Excellent l'ami !
Thank you a lot for excel auto calendar making. After preparing the calendar, how do we able to see whole 12months calendar in single sheet at a time without selecting the picklist and go to every month by month?
Hi, Thanks for sharing! very clear :) but how to apply the bank holidays formula when the employees of a same team are leaving in different countries? As an example, I have a team of 7 remote worker dispatched all over the world, how can I apply the bankholiday for each? Thanks in advance for your help
I appreciate your effort in this video. When I finished the calendar, I put in information in a month but when I change the month and return to the month I put in the information, I don’t find the information. Please help, thank you.
Hi there: I am having the same issue, did you find the solution to it?
Fantastic and Thanks
Thank u so much bro!
Very good an clear
Hello! Thank you so much for this very helpful tutorial. I just have one tiny issue and hope that someone could help point me in the right direction: I would like to share this excel sheet with my teammates so that we can keep track of everyone's vacations. When I want to add "x"s in the month of January they also appear in all the other months. Is there a way for the "x"s to just show on the month we are adding them in? Thanks :)
I done it today. However, I got problem with clear contents. example.. I add data in Feb and I go to Marc the contents are cleared but once I go back Feb, the data on Feb is cleared too.. It's suppose to remain right? please advice ... Thanks.
Need your support if we have alternate Saturday off than how can we select conditioning format in that file please reply
Great stuff! Is it possible thou to save data that was filled in lets say October, to stay locked in October? The current macros cleans the previous content, and if you switch to November and then go back to October it will clean any data that was filled inside that month. Thank you!
Евгений Мейран did you solve it yet?
Did you solve it?
Hello. I was able to keep the data by deleting the lines in the macro relating to the deletion of the entries. I then copied the sheet to create individual sheets for each month within the workbook. Then you just need to run the saved macro within each of the sheets, and it takes care of the extra columns you don't need. Hope this is of help to you.
@@dig5166 please post a tutorial with your explanation please
@@dig5166 That's not automatic. You have to do a bunch of extra steps. You didn't solve anything.
How did you plug in the formulas for public holidays?
Awesome Tutorial sir. Excellent job... i have a question, how do i get back my entered data month-wise or year-wise when i move to February to January or year to year,
that means when i move from January to February content to be cleared but when i move to February to January i want to retain my January data :)
@excelexercice same question. how can i retain the data in a certain month?
@@johnmarimarcelo - did you find a solution to this problem? I figure that data entered into any given month will need to be stored in another table someplace. This begs the question: when we switch back to any given month, how does the calendar pull data from that month's storage table and how does this read/write action get managed?
How to set shift pattern in this file
Thank you very much, you are GREAT! This video was really useful for me..please could you help me? How can I keep 1 for each month? When I switch the month the data has lost. I will be grateful for your answer :-)
This is not simple and it's a real development project. There is so many situation and rules that it's impossible to do a tutorial video for that
@@FredericLEGUEN-Excel A video about would be cool even if it is difficult!
Hi i want to do the same.i am using Microsoft 2007.i tried a lot but stock in a scenario.when i select january or any other month or year from dropdown menu,in A1 it is showing as same.Not as 1,2,3.Don't know what to do.
Please help me.
Thank you
This is very helppul however I wish he shared how to retain the data from previous month.
Hi please can you do a video explaining how to retain data entered in each month. I get it's more complex but would be very helpful to learn. Thanks!!
Doing a video? Impossible because it took me 3 weeks to create the tool to do the job. In the description of the video, you have the link to download the workbook to save and recall the data. You can also read the article that explain all the feature of the workbook
Thank you for the ideas. While we input the numbers in Feb and move to Mar - it is clear content, but when we come back to Feb again values become clear content. how to make fix the values in each month ???????
It's something possible but it's much more complex to build because you must manage the process to store somewhere the previous data. The best is to use Planner
Is there a video on how to create the Public Holiday spreadsheet ?
Looks great, but unfortunately I couldn't find a way around it not saving any information in the cells - so it was ultimately useless.
Thank you for this! Appreciate it ☺️ but how to save this month’s data November. Thank you.
That's not possible or you need to create a long and complex vba code
I have the same thing with not keeping it’s data and overwriting to the next month.. is there a macro for that too?
Yes, but the macro is too complex to be shared in a tutorial video. It's a real computer programming project
I love it thank you, but how can I set VBA to save all data for each month without deleting it when I go to another month or year
Hi Lina, I am looking the same thing. Did you get an answer to this one?
Timothy Rolston
Yeah I did and you can find it here
You need to follow the same steps on VBA
ruclips.net/video/iyr9WIyxlJc/видео.html
@@linan3656 please share how you retain data of previous month calendar.
SUPER HEROOOOOOOOOOO. THANKS ALOT, WILL YOU PLEASE WITJOUT MICRO???♥
Thank you so much, i have one question , how to include public holiday for several country?
Ouch, this question is tough. Not easy to reply without seeing the context.
merci
Thank you !!
I have a few questions.
How to access previous months' data?
In this video I did not learn how to store previous months data. So please help me.
With this demo, you can't.
But in the description of the video, you have the link to download the workbook which save all the data and re-display them for the previous month
Hey! Is it possible to show week number above the date? Like it goes from monday to friday and above that is week number? Thanks in advance
Good question, this will be the subject of another video 😀
Hi brother, Thank you for the tutorial,
I am having a bit of an issue, everytime i change the month the content is deleted. but I want to keep the content as a record form previous months.
can you please help me,
Thanks
Nice one! But whats the sense uf it deletes whole data everytime u change month. Not practical
How can put from date 26 to 25 every month pls make a video or formula pls
When you create a loop half the screen went blank. Please explain how did you create the loop?
Hi .... were you able to get the response?
Merci, très utile ! auriez-vous une astuce pour garder les données pour chaque mois? ex, je passe au mois d'aout, les données juillet s'effacent.
Dans la description de la vidéo je renvois vers un article qui explique le détail du code, dont le point que vous soulevez.
What if we want to eliminate weekends completely and keep data history.
Hi i missed something. The data got erased after changing the new month. but its not able to record the previous month attendance. how to do that? if possible please share that too.
Can I download this somewhere? I mean
the whole folder.
I love this tracker but I have a question. When you switch to next month, the data gets deleted, but when your rturn to the previous month, the data entered is gone as well. Is there a way to program it so that when you switch to new month data clears but stays saved in the previous month?
Did you get anywhere with this question? I've just built this calendar myself but need a way to preserve the data entered into each calendar.
@@ryanjohnston9642 Exactly. This video is somewhat a dud without that capability.
@@jupiterhaha have you guys figured out the trick? Stuck on the same scenario.
Anyone got the solution for this? Stuck on same thing
@@mohsnig How did you guys create the loop? that part wasnt clear to me
brother i need this project video file
Hi , i wanted to create something similar to this but in Google Sheet. not as calendar but only as a table. what i aim is whenever i select different month, different data will appear. That also data that i have inputed manually before. Can anyone help please?
How do I create a calendar set up just like this, but without weekends?
the date formula isn't working on my excel, when I put exactly as it shown in video it says that formula isn't correct =DATE(A2+2018,A1,1). It says that input not enough criteria
What do you have in A2and A1 ? If it's empty, it can't work
Hi thank you! I am here because I was using Validation before and that is not too helpfull. Can you please help me?? I am having hard time with Macros, in my sheet I am using Columns for Employee names and rows for Dates, how would I code that. Thank you!!
same like me. but it appear error. i dont know how to define it, to make it correctly changes by the month
Dear Sir
In the automatic calendar, February has 28 days so how to hide the remaining cells.
thanks a lot
The code is is this article
Also, how does it account for Leap Years?
It works because the test of the macro is based on the value of the month. So even for a leap year, the test will work
I did this exact thing and my macro is only holding up to 28 days rather than giving me the days of each month. What should I do?
Certainly because your date are not in row 6 or the result of the selected month is not in A1
Hello. how do you plug in for an every other weekend days off for employees?
I don't understand the question 🤔
Can someone please help me how to retain data of previous month please
this eight number in the code reference which object?
The 8th row
Thank you!!
I have got all you said, but i am facing an issue with the "Clear Content".
When I fill-in the cells for Jan and move to Feb and again move back to jan, the data entered is lost. Is there a way to retain the previous months data also along with getting clear cell for next month?
Next week, you could download the program to save your data. The program is nearly finish and we have more tests to do before to share the link.
Hi, Finally it was longer than expected (many little bugs to correct) but now the calendar with saving data is done. In the description of the video, you have the link for the file and the link for the explanation
How to make same calander on Row basis
Hello nice video! What about with no Macro? Do you have a video? Thanks
What do you mean by no macro?
Every thing is going fine but In the last macro commands, I could not get create a loop for the final code .. please share how to create a loop
Have you checked the explanation of the code in the description of the video? Because it's impossible for me to give you an explanation why you can loop on the last columns
My visual basic didnt open what to do its blank
I tried but i got some error bout VBA. I use ROW instead of column. so how to define it?
Sub Macro1()
'
' Macro1 Macro
'
'
Rows("40:42").EntireRow.Hidden = True
Rows("41:43").EntireRow.Hidden = False
Range("B13:42Y").ClearContents ---- got error with this
End Sub
I have same problem , how can make this calender on Row basis?
I noticed that after this tutorial was put online 3 years ago that the same comments/questions being asked that I have still remain unanswered by the creator. Hmmm! From the viewpoint of a noob, the other issue is the commands your using when you highlight cells and extend the data - I don't know or see how you're doing it.
There is no point of creating this calendar if you’re going to copy paste them to have multiples that have data inside...
I used to think Photoshop experts were the bomb.... Excel is like high math.
Can it be set where the previous months data is not lost but still complete if you move from month to month? Thanks
No, it's not possible easily. It's possible but with a lot of code
@@FredericLEGUEN-Excel Thank you for the reply. Great video. Even without saving the data I learned a lot.
hi there! video was good but do you know how can i retain the data on the calendar? say i have schedule in January 8 2019, it should stay at the date but whenever i go to other dates and go back, the data is also gone?? please help
Grant Philip Gloria did you solve it yet?
@@RaiyanaRR not yet, do u know some workarounds? I'm just a newb in Excel that's why these videos help me
I have the same issue, got almost everything correct except the issue you are stating, do not know a fix for this
I also have the same problem, did you solve it?
Has anyone found the solution to this issue?
How can I change name from table to other language calendar? Friday to become Vineri!
helpfull
But why when I add data to one month and change the month and go back the previous data are cleared as well, I just want the information to be saved and not giving me clear content every time I change the month
It's not the purpose of the video. Stored the previous values and reloaded them it's really not easy with Excel and Excel isn't design for that
. Yes. I am facing the same problem. Can we retain the data when we go back to the previous content??
Hi. I got past this issue by creating an individual sheet for each month, editing the macro and erasing the last lines which erased the data, and then running the macro in each of the sheets in the workbook. Hope this helps.
How do I download this? There is no link on your website!
I haven't share the workbook, it's more profitable to do it yourself 😉
Very great video as well the teacher. But I would like to as for a macro code when I would like that the data stay saved for one month when I change to next month (not clear the content or same data for all months). The second think - I tried to macro code for rows, but I have mistake somewhere - Can you do me a favor. Maybe it can be inspiration for you to create new video for us :)
'For
Row_Number = 36 To 38
If Cells(2, 1) = Month(Cells(Row_Number, 8)) Then
Rows(Row_Number).EntireRow.Hidden = True
Else
Rows(Row_Number).EntireRow.Hidden = False
End If
'Next
Range("C8:E38").Select
Selection.ClearContents
End Sub
I am having the same issue. Have you found the solution? He wasn't clear on how to create the loop
How to download this file
It's copyrighted and no way to download it
How did you format the holiday sheet?
I don't understand your question. "Format the holiday"? Do you talk about the date format or How I have build the formulas?
@@FredericLEGUEN-Excel in the few seconds that I was able to view the holiday page, I noticed that there was alot there. I wanted to know was there any code, or special function that you use on this page for any reason. Also can you explain how I can auto populate the employees to a work place positions chart?
thanks but macro (hide & unhide of last columns not working when I applied)
It should ! Nothing complex with the macro honestly. If you have respected the position of the cells (rows and columns), it works
Salam sejahtera... Nak tanya...
Saya buat semua berhasil... Tetapi kenapa maklumat di setiap hari hilang bila kita tukar bulan dan tahun
How do I loop the macro? The video skipped that part
Loop the macro? I don't understand
The link to the code is in the description to the code
excelexercice at 20mins 36seconds you said you are going to create a loop? How do I do that?
@@FredericLEGUEN-Excel Hello, thank you very much for this video.
I have the same problem. I dont have the ,,makro text" of a loop.
After changing the options in excel and returning back to Visual Basic. There is still the same sentence.
May I write the new one by myself or I am wrong in some step?
Thank you. Soňa
Can this be replicated on goggle sheets?
No idea
Can you send me the code to hide the data entered with macro
In the description of the video, you have the link to the article and also another article for the explanation of the code
excelexercice
If I change the month the entered values on particular month gets deleted can you help on this.
at duration 20:35 onwards to 20:37 the screen goes blank
On the description of the video, I give the link to the page with all the explanation AND THE CODE
The calendar is off no use if can't save the data in the months.
Because it's not the purpose of the video. What you ask is too complex and need 2 or 3 days of development
@@FredericLEGUEN-Excel is this information available on your channel or online anywhere? It would be a massive help. Thanks
Excel lent