Annual Leave Tracker with Daily/Monthly View in Excel
HTML-код
- Опубликовано: 14 окт 2024
- #LeaveTracker #ExcelTemplates
Hello Friends,
In this video, you will learn how to create an Annual Leave Tracker with Daily and Monthly View. This is a ready to use Template.
Subscribe our new RUclips Channel:
/ @neotechnavigators
Watch the Step by Step tutorial for Project Plan (Gantt Chart) in Excel
• Project Plan(Gantt Cha...
Download the practice files from below given link:
www.pk-anexcel...
Download the Free Project Management Dashboard
www.pk-anexcel...
Download the Calendar Control in VBA from below link
www.pk-anexcel...
Download our free Excel utility Tool and improve your productivity:
www.pk-anexcel...
See our Excel Products:
www.pk-anexcel...
Visit to learn more:
Chart and Visualizations: www.pk-anexcel...
VBA Course: www.pk-anexcel...
Download useful Templates: www.pk-anexcel...
Dashboards: www.pk-anexcel...
Watch the best info-graphics and dynamic charts from below link:
• Dynamic Graphs
Learn and free download best excel Dashboard template:
• Excel Dashboards
Learn Step by Step VBA:
• VBA Tutorial
Website:
WWW.PK-AnExcel...
Facebook:
/ pkanexcelexpert
Telegram:
t.me/joinchat/...
Pinterest:
/ pkanexcelexpert
Visit our Amazon Store
www.amazon.in/...
So easily you explain the complicated stuffs proves really you are genius and have depth knowledge in Excel 👌.. Thanks for educating the world 🙏
Thanks for your valuable feedback
Great work.!! I had one question can I add date in range like 5/31/2021 to 6/11/2021 in one cell against one employee.
Very useful video! I just created my own. Can you please show how to mark leave range? More than one day. It would be very tedious to mark each day separately.
any update on this. want to learn also how to mark range of leave.
Thank you so much for this video! I juts created the leave tracker for my team! Is there anyway to add half day leaves for all the leave codes?
Please watch the next version of this.
ruclips.net/video/WSq_L8nxLUk/видео.html
Fantastic video, would be interested to see the formula to remove weekends from the total (i.e. if chose 2 week period of leave, id like to exclude the weekends)
Grate video but i will create it for more than 5 years so i will create it as drop down list for month and year separately. i was struggling with the leave tracker with my organization i have idea now and i am going to use it. thanks PK for this video.
My favorite dynamic tracker, thank you for this!
Glad you like it!
why my formula show #NA when doing VLOOKUP
very nice sir and clear n easy teaching
Thanks 👍
Hello Sir! i appreciate the detail and the time you put into this video!
My pleasure!
Thank you so much I succeeded by following your steps
Thanks for your valuable feedback
@@PKAnExcelExpert can I have your contact number to get some immediate help in excel
Wow this video is so good. Very Useful video.
Many many thanks
This is what I really look for. Thanks
Most welcome🙏
Thank you sir for sharing these tracker
My pleasure
Great info. Many thanks!!! P.S: is possible to somehow add multiple dates for holiday? example:to add that someone is in holiday from 12-26 aug? and not to add by hand each day for each employ?
Thank you very much its realy help full
Glad it helped
Great sessions, Thanks
Most welcome
Very useful. Just prepared it. Thanks!
Thanks for your valuable feedback🙏
Hi Pk, Fantastic work. How can I do a date range, say a member of staff wants a week holiday? is there a way I can have the summary populate cells in between a start date of holiday and end date?
Awesome tutorial. Thank you very much.
Most welcome🙏
thank you! this is very helpful. I was successfully able to do this in Excel. What should I do to replicate this in google sheets as the developer button is not available and I cannot toggle between different months?
Thank you for the guide, It was really helpful. Would you be able to help me to add an extra colume in Mark Leaver to allow tracker to auto fill Date from to Date To instead of adding several dates / lines on the Mark Leaver tab. Thank you
Thank you ....learnt a lot
Most welcome🙏
Thank You Sir it was very helpful
Thanks for your valuable feedback
Thankyou so much to this work, i did it, again thankyou so much..
Most welcome
you are real expert
Thanks for your valuable feedback
Great video. If I want to change the year then what would I need to do?
Thank you, it is really useful.
Thanks🙏
Thank You so much! I just finished mine. Thank you for sharing your knowledge. Done Subscribing!
Thanks for your valuable feedback
Wowwww!!! it was great a few tweaks and I was able to mark weekends as well. Thanks for the detailed explanation.
Glad it helped!
Hi! Any chance you could let me know how you marked weekends/bank holidays?
@@14butterfingerz70 select the first day and the last cell. for this sheet its C5:AG23, then use conditional formatting by formula and use this formula to highlight Saturdays =IF(C$4:AG$4="Sat",TRUE,FALSE) and for Sunday reapply the formatting with formula =IF(C$4:AG$4="Sun",TRUE,FALSE) in same selection
@@primeplays7851 thank you so much! :)
@@primeplays7851 hi, did you managed to mark the public holidays by using the conditional formatting by formula? I have tried but it seems that it did not work using the same formula for sat/sunday
This is awesome, thanks lot for such a detailed video
Glad it was helpful!
This was really helpful to mainatain the employee leave data. But i have one question, in the mark leave sheet we can just mention only one date , what is an employee is taking 1 week of annual leave which includes from date and to date. how do we calculate it?
I mean it's just like a wow! Sir thank you sooooo much.
Most welcome
@@PKAnExcelExpert I have completed almost 95% just not able to lock the cell properly as I'm using 2013 excel. Please advise
Very Helpful video just created one form my Large Team same like it is shown here.. Thanks a ton
Thanks for your valuable feedback🙏
great work sir , really helpul
Glad to hear that
thank you for this video. i have a question, staff usually take long annual leaves such as 30days, 45 days. How to incorporate that?
Great stuff thank you, can we use a range of dates to mark leave
Please watch the next version of it
ruclips.net/video/WSq_L8nxLUk/видео.html
Thank you so much for this, I have created my own just wanted to highlight weekends and remove them from total counts, Please advise
Great idea! I will upload a video soon on this
This was great! Thank you so much! Since I'm using google sheets and not XLXS, I just modified a few things because some formulas and tools were unavailable in Google Sheets. I used alternatives. It's still working, though!💪🙌
Glad it helped!
Pls help me with the alternatives in Google sheet
Thank you ❤
You're welcome 😊
Thank you !
You're welcome!
That was very helpful. Thank you
Most welcome
very useful information thank you sir but if i take HALF CL/SL Then How to Calculate ?& Which Formula can be avail For HALF LEAVE in this sheet.??
Sir Thank you very much for your effort . if you add available leave details in the leave management excel will be great.
Thanks for your valuable feedback. I will try to add it in next version.
@@PKAnExcelExpert Thank you very much for your reply. I am working for NGO if you add that I will use excel for our office work and very much helpful. So, I will be able to share the reports to our employees and they can know how much balance leave in their account. Accordingly, they will plan for leave.
For your information in our organization there are four types leave 1) Casual Leave yearly 10 days, Sick leave yearly 10 days and maximum carry forward up to 30 days, Privilege leave 21 days and maximum carry forward up to 30 days and compensatory leave if they work in holidays they should take within two months.
That is great,
In Mark leave there is no a range of leave days, you just insert single day ? how can i insert range of days
Great video lots of information, and very helpful thank you so much for this. Keep up the good work👍
Glad it was helpful!
Great video, however, I notice the calendar plotting is applicable for single date, I am wondering how you plot for a date range? for instance, employee A applied annual leave from 1 Jan to 3 Jan 2024. Using If + index match?
It's really useful video, Thank you so much
You are most welcome
well that was so nice work man .. good job
but I want to ask you a favour that to teach us how to insert more days .. like what if the worker want to go for 10 foe CL for example
Please check our next version also:
ruclips.net/video/WSq_L8nxLUk/видео.html
Amazing tracker…
Is it possible to get a quartely and a year to date review.
Yes, we can do that
VLOOKUP only seems to check the left-most column. Why does this work for you at 17:44 and not normally?
Hi, thank you so much for the guide. It really helps.
Also, would like to check with you regarding the Holiday in the monthly summary view.
I managed to highlights the weekend in the monthly summary view spreadsheet.
However I'm having issue to highlight the Holidays in the Monthly Summary sheet. Can you advise if you able to guide on that?
Tried the conditional formatting but it doesn't work
Again trillions of thanks sir 🙏
Most welcome
Thank you it was very usefull. Can you please add a step where we can highlight weekends
Great tracker! For some reason when i changes months in Monthly Summary Tab, March and OCtober show as ########. The rest work fine. I even downloaded your template from your site and it was the same thing. How can I fix that?
I have the same issue! I tried the format of ddd-mmmm and displays ok but then when I close the file and open again it’s all ###### it is really infuriating! I can’t sort it! Any ideas?
I found out. First select the columns and rows, then Increase the width of columns and rows . The problem is fixed. That's how I fixed mine. The excel sheet looks bit big otherwise it's okay to do.
I am following it and created with help of yours. Now having a difficulty how willi calculate half day? Please advise.
Thank for your all great work.
Most welcome
Thats really awesome !! Can we have many things in same cell ? Same day ?
Same format, but do you have a formula with drop down list in choosing type of leaves? Thanks
Hello, thank you very much for this
You're welcome 😊
Hi, the video was really helpful. I have a doubt. I want mandatory leaves to be already highlighted in the sheet. When I try to add Jan 15 as an ML, all months 15th date are getting highlighted. How can I add MLs? Please help me with this?
Sir, in this excel sheet we need to insert employees each day leave. what if someone is proceeding for longer period. There should some easy way for input from/to period also. 🙏🏻
Those who find #### when you change months, just increase width of columns and rows. The problem gets fixed also it worked for me just now 😁.
Thanks for your lessons 🙂🙂🙂🙂
Most welcome
Thanks for this beautiful tutorial
Thanks for your valuable feedback
مرسی
very amazing file I do like it so much PK, thank you for sharing such a magic knowledge !
Thanks for your valuable feedback
Thank you so much!
I am just missing weeknummer. is it possible to add?
Yes we can add
Very impressive! I learn so much from your tutorial. Keep it up!
Thanks🙏
Hello Sir, can I only update employees leave directly in monthly summary, without doing in mark leave tab? As some employee might take long leave and it's easier if just mark in monthly summary tab.
Awesome video! Thank you so much! How can I view a quarterly calendar instead of a monthly view. I would greatly appreciate your guidance.
Great suggestion! I will try to add this in next version.
@@PKAnExcelExpert hi , I am looking for quarterly calender, please advise which formula we need to use
hello, my excel is 2016, and i dont have the (Developer) to add a combo box, is there another alternative or what do you suggest should i do?
Please watch the below given video:
ruclips.net/video/q8AZ2TGbY7M/видео.html
@@PKAnExcelExpert thank you for your quick reply, i actually figured it out and am about to create your leave tracker , 😍
Thank you, cud u pls adv how to add a colunn to show the total leave balance
will add in next version
It was every helpful sir,How to add new employee in sheet.Do I need to make again from first.
Wow so useful.
Glad it was helpful!
Useful , thanks for sharing
Thanks for your valuable feedback
Thanks for the useful video!
Thanks for your valuable feedback
Great job!!!
Thank you!!
How can you modify the Form Control for Month on row 1 (Column F&G) to work with Sharepoint? I uploaded the spreadsheet onto Sharepoint and all works well except for the Form Controls. How would you modify this to make this work?
Nice! Thank you, it is a lot easier to track the leaves 😊
Thanks for your valuable feedback
Total excellent boss. but i need summery each employee with date like leave card
will try to incorporate in next version
@@PKAnExcelExpert sure boss, i am waiting
This is helpful. Thank you. I tried this earlier and I got an error when inputting the formula for leave type in the monthly summary sheet. I noticed that whenever I include the date as part of lookup value it gives me an error. Any advice on this?
I get an error as well
I decided to use his template as it's more convenient. Thank you for the free template sir PK.
Hi this is very useful. Thanks for this
But just wanted to ask if you have formula for the half Leave, or hourly leave, because they do this mostly. Thanks again
If this done before, can someone send me the link please. Badly needed huhu
Please watch below video:
ruclips.net/video/WSq_L8nxLUk/видео.html
Download link has been given in video's description box or visit our website WWW.PK-AnExcelExpert.Com
Thank you so much.🥰
If I have 5 days leave at a stretch and if I mark leave start date and end date in mark leave sheet, then which formula can help to visualise all the leave dates in monthly summary sheet?
Great work sir.
Thanks for your valuable feedback
This is amazing & so useful. Thank you so much!
You're so welcome!
thanks
You're welcome!
Hello PK it's helped me alot but for me alternate month date is coming in xxxx format.Could you please with it.
Hi tq so much for this, but i want to ask if I share document with this formula to google drive, why they can't open with the same formula too. Some of formula doesn't show.
HI SIR GREAT WORK SIR. APPRECIATE THE TIME AND EFFORT. JUST A SMALL QUESTION -HOW TO PROTECT TAMPERING OF THE MONTHLY SUMMARY AND INDIVIDUAL SUMMARY PAGES AND KEEP ONLY MARK LEAVE PAGE AS OPERATIONAL
Please confirm, How can I reach out to you, leave tracker is very helpful and appreciated by my management, but needs to enhance one more feature...
My Mail id is info@PK-AnExcelExpert.Com
super thank you for this tutorial! 😄
Thanks for your valuable feedback
Great file BTW
Thanks
really an awesome file PK I do like it so much!
Thanks for your valuable feedback
Thank you sir 🙏
Most welcome
Hi there
I have a question for the vlookup formula instead of including 2 columns I want to include 3 columns for employees I'd, social number , and their names how can I write it.
Thank you so much
Hi, Can you pease upload a video about how to request for multiple vacations in one segment. thank you.
Hi, thanks for the sharing. Can you guide us how to adjust for half day annual leave?
Thts exactly what I want too. if you are able to find the video on that, please do share.
@@meherdad8 Add a new leave type for half day. Then when you add up the totals for that leave type, divide by 2.
@@ruthlorimer2161 I did exactly the same thing. Thank you. 😀
can you do it without an emp id? for instance, can you get away with only last name? or does it have to be an assigned number for each employee? TIA
We can do that. Need to make the changes accordingly. will try to consider this in next version
@@PKAnExcelExpert thank you
hello Sir,this is very helpful, I am struggling with one thing, when I move on to the next month, the leaves marked do not disappear . Maybe I am doing something wrong
Can you please help how to give date range if someone is taking leave for more then 3 days instead of tying each day