Annual Leave Tracker with Daily/Monthly View in Excel

[@asfaquealam4923]

Thank you so much Easley to updated all yearly to yearly. its very useful for Manager & HR

[@PKAnExcelExpert]

Most welcome! I'm happy it's helpful.

[@myhobbies1288]

So easily you explain the complicated stuffs proves really you are genius and have depth knowledge in Excel 👌.. Thanks for educating the world 🙏

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@umarfarooq-if1yp]

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.

[@sreejondey1917]

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.

[@dilanlakshitha7611]

This is what I really look for. Thanks

[@PKAnExcelExpert]

Most welcome🙏

[@hugohernandez2362]

Hello Sir! i appreciate the detail and the time you put into this video!

[@PKAnExcelExpert]

My pleasure!

[@primeplays7851]

Wowwww!!! it was great a few tweaks and I was able to mark weekends as well. Thanks for the detailed explanation.

[@PKAnExcelExpert]

Glad it helped!

[@14butterfingerz70]

Hi! Any chance you could let me know how you marked weekends/bank holidays?

[@primeplays7851]

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

[@14butterfingerz70]

@@primeplays7851 thank you so much! :)

[@Thwei0102]

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

[@dandsilva5569]

VLOOKUP only seems to check the left-most column. Why does this work for you at 17:44 and not normally?

[@kunleangsortcoursemsoffice1197]

Wow this video is so good. Very Useful video.

[@PKAnExcelExpert]

Many many thanks

[@mayankdas1595]

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?

[@PKAnExcelExpert]

Please watch the next version of this. kzbin.info/www/bejne/jYTUkH9uo91_i80

[@1DCPH1]

My favorite dynamic tracker, thank you for this!

[@PKAnExcelExpert]

Glad you like it!

[@MuhammadJihadAhmar]

why my formula show #NA when doing VLOOKUP

[@josephlobo3703]

you are real expert

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@amilasankalpa6141]

Thankyou so much to this work, i did it, again thankyou so much..

[@PKAnExcelExpert]

Most welcome

[@antiebephillip9477]

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.

[@jcbatani8702]

any update on this. want to learn also how to mark range of leave.

[@VlogsbyRaviVerma]

Great sessions, Thanks

[@PKAnExcelExpert]

Most welcome

[@TheSambita20]

This is awesome, thanks lot for such a detailed video

[@PKAnExcelExpert]

Glad it was helpful!

[@edgardoborde2404]

Thank You so much! I just finished mine. Thank you for sharing your knowledge. Done Subscribing!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@abhisheknayak1381]

Very Helpful video just created one form my Large Team same like it is shown here.. Thanks a ton

[@PKAnExcelExpert]

Thanks for your valuable feedback🙏

[@koteswaraog3914]

Thank you so much I succeeded by following your steps

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@koteswaraog3914]

@@PKAnExcelExpert can I have your contact number to get some immediate help in excel

[@muhammadhashim6152]

very nice sir and clear n easy teaching

[@PKAnExcelExpert]

Thanks 👍

[@garytodd6502]

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)

[@ramanandprasad5589]

Thank you ....learnt a lot

[@PKAnExcelExpert]

Most welcome🙏

[@bhaskarbargela6548]

Again trillions of thanks sir 🙏

[@PKAnExcelExpert]

Most welcome

[@DKvilla]

Very useful. Just prepared it. Thanks!

[@PKAnExcelExpert]

Thanks for your valuable feedback🙏

[@ravindra23061988]

I mean it's just like a wow! Sir thank you sooooo much.

[@PKAnExcelExpert]

Most welcome

[@ravindra23061988]

@@PKAnExcelExpert I have completed almost 95% just not able to lock the cell properly as I'm using 2013 excel. Please advise

[@GeorgeAJululian]

Thank You Sir it was very helpful

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@johnkbroderick]

Great video lots of information, and very helpful thank you so much for this. Keep up the good work👍

[@PKAnExcelExpert]

Glad it was helpful!

[@nongnanzz362]

Thank you ❤

[@PKAnExcelExpert]

You're welcome 😊

[@suzypessoa2489]

Thank you !

[@PKAnExcelExpert]

You're welcome!

[@elsabettilahun94]

Thank you very much its realy help full

[@PKAnExcelExpert]

Glad it helped

[@xlpedia]

Awesome tutorial. Thank you very much.

[@PKAnExcelExpert]

Most welcome🙏

[@alnuzailisameer]

Thank you, it is really useful.

[@PKAnExcelExpert]

Thanks🙏

[@kinjalshingadiya586]

Thank you sir for sharing these tracker

[@PKAnExcelExpert]

My pleasure

[@ayoubabdelrahmanguelai4998]

hi , can you share with me this tracker ? please

[@MuhammadYaqoob-cd7fx]

great work sir , really helpul

[@PKAnExcelExpert]

Glad to hear that

[@raqueldivosion9422]

Very impressive! I learn so much from your tutorial. Keep it up!

[@PKAnExcelExpert]

Thanks🙏

[@sarfrazahmad9479]

Thank for your all great work.

[@PKAnExcelExpert]

Most welcome

[@KuyaP0907]

Same format, but do you have a formula with drop down list in choosing type of leaves? Thanks

[@PulsarSDPhilippines]

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

[@PKAnExcelExpert]

Glad it helped!

[@arthirensworld1163]

Pls help me with the alternatives in Google sheet

[@nnbona]

very amazing file I do like it so much PK, thank you for sharing such a magic knowledge !

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@mazenzagha8811]

Thanks for your lessons 🙂🙂🙂🙂

[@PKAnExcelExpert]

Most welcome

[@andrewcleaver6610]

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

[@tanujmandalofficial3693]

Thank you so much for this, I have created my own just wanted to highlight weekends and remove them from total counts, Please advise

[@PKAnExcelExpert]

Great idea! I will upload a video soon on this

[@rangegowd247]

Sir Thank you very much for your effort . if you add available leave details in the leave management excel will be great.

[@PKAnExcelExpert]

Thanks for your valuable feedback. I will try to add it in next version.

[@rangegowd247]

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

[@rangegowd247]

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.

[@ramonaxx]

Hello, thank you very much for this

[@PKAnExcelExpert]

You're welcome 😊

[@Pawrentlife]

Thank you it was very usefull. Can you please add a step where we can highlight weekends

[@nkululekoradebe5152]

That was very helpful. Thank you

[@PKAnExcelExpert]

Most welcome

[@sunnykamra1777]

Great stuff thank you, can we use a range of dates to mark leave

[@PKAnExcelExpert]

Please watch the next version of it kzbin.info/www/bejne/jYTUkH9uo91_i80

[@SyedMuzammilMahasanShahi]

Thanks for this beautiful tutorial

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@marsh6676]

super thank you for this tutorial! 😄

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@NeangBona]

really an awesome file PK I do like it so much!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@Brandon-hw3ok]

Awesome video! Thank you so much! How can I view a quarterly calendar instead of a monthly view. I would greatly appreciate your guidance.

[@PKAnExcelExpert]

Great suggestion! I will try to add this in next version.

[@sravs1057]

@@PKAnExcelExpert hi , I am looking for quarterly calender, please advise which formula we need to use

[@Roger-ou8je]

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?

[@AndreaUK1973]

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?

[@harshithajeevan555]

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.

[@Thwei0102]

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

[@pinaldon]

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

[@yojlemsaidolem]

Nice! Thank you, it is a lot easier to track the leaves 😊

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@justinwilliams669]

Great video. If I want to change the year then what would I need to do?

[@daudyusuf8649]

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

[@NadiaKhan-k3d]

thank you for this video. i have a question, staff usually take long annual leaves such as 30days, 45 days. How to incorporate that?

[@temyraverdana6421]

Thanks for the useful video!

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@ZeceOAse18]

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?

[@meganking4678]

This is amazing & so useful. Thank you so much!

[@PKAnExcelExpert]

You're so welcome!

[@aakifimthiyaz7186]

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?

[@rodneycbarker]

Great job!!!

[@PKAnExcelExpert]

Thank you!!

[@tehhengwee104]

Hi, thanks for the sharing. Can you guide us how to adjust for half day annual leave?

[@meherdad8]

Thts exactly what I want too. if you are able to find the video on that, please do share.

[@ruthlorimer2161]

@@meherdad8 Add a new leave type for half day. Then when you add up the totals for that leave type, divide by 2.

[@meherdad8]

@@ruthlorimer2161 I did exactly the same thing. Thank you. 😀

[@omerrah3431]

Amazing tracker… Is it possible to get a quartely and a year to date review.

[@PKAnExcelExpert]

Yes, we can do that

[@shaidadlshad1312]

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?

[@PKAnExcelExpert]

Please watch the below given video: kzbin.info/www/bejne/p2mki2WKfMeMba8

[@shaidadlshad1312]

@@PKAnExcelExpert thank you for your quick reply, i actually figured it out and am about to create your leave tracker , 😍

[@lalala622]

Thats really awesome !! Can we have many things in same cell ? Same day ?

[@jgun0075]

Thanks for this project 👍

[@PKAnExcelExpert]

My pleasure

[@vanq2226]

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?

[@TuhinadriSarkar]

Can someone please explain which key is using to change the format at 10:46

[@shielatorres5804]

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?

[@raymondstewart4548]

I get an error as well

[@shielatorres5804]

I decided to use his template as it's more convenient. Thank you for the free template sir PK.

[@aiyazlakhani]

Great work sir.

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@InspiredAnalyst]

Wow so useful.

[@PKAnExcelExpert]

Glad it was helpful!

[@abhilashh3205]

Thank you, cud u pls adv how to add a colunn to show the total leave balance

[@PKAnExcelExpert]

will add in next version

[@kavyam5239]

It's really useful video, Thank you so much

[@PKAnExcelExpert]

You are most welcome

[@sekerboys2558]

Thank you so much! I am just missing weeknummer. is it possible to add?

[@PKAnExcelExpert]

Yes we can add

[@kinsley3731]

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

[@kinsley3731]

If this done before, can someone send me the link please. Badly needed huhu

[@PKAnExcelExpert]

Please watch below video: kzbin.info/www/bejne/jYTUkH9uo91_i80

[@PKAnExcelExpert]

Download link has been given in video's description box or visit our website WWW.PK-AnExcelExpert.Com

[@kinsley3731]

Thank you so much.🥰

[@rash9366]

Useful , thanks for sharing

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@Hussatube]

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

[@PKAnExcelExpert]

Please check our next version also: kzbin.info/www/bejne/jYTUkH9uo91_i80

[@tonybyatt9232]

Great file BTW

[@PKAnExcelExpert]

Thanks

[@BronzeVBaker]

hello, how did you paste it all in 18:38, could you please show the steps too. Thank you

[@srjc98]

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

[@PKAnExcelExpert]

We can do that. Need to make the changes accordingly. will try to consider this in next version

[@srjc98]

@@PKAnExcelExpert thank you

[@mehboobshaikh285]

Thank you sir 🙏

[@PKAnExcelExpert]

Most welcome

[@harshithajeevan555]

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

[@tnssajivasudevan1601]

Great Sir.

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@k.m.vishnupriya4073]

What was done at 12:29 for the second day of the week?

[@neerajkumar-ik3qi]

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

[@tonybyatt9232]

Can the Saturday and Sunday columns be filled with a colour?

[@mariaisabeldayrit1272]

Hi again PK! 18:35 I copied the formula from cell "C5 and pasted it to entire range and I can see it in each cell that there were formula already. However, when I mark leave "02-Jan" or onwards (Column D - AG), no leave type is populating in the monthly summary. Can you please let me know the issue? Thank you for your help, PK!

[@syuace3658]

18:23 i cant copy and paste the same formula

[@monakamp5730]

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

[@ravindra23061988]

I am following it and created with help of yours. Now having a difficulty how willi calculate half day? Please advise.

[@AndreaUK1973]

Can you help to sort the ###### displaying on the the EOMONTH/IFERROR functions? The green and yellow rows! Some months display ok but others display #### on the vertical results ! I’m so frustrated that I couldn’t finish it! Please could give some suggestions?

[@WhatsUpMakkale]

Hi. This is useful thanks. One question: how do you enter half day leave?

[@PKAnExcelExpert]

I will add this feature in next version

[@kinsley3731]

@@PKAnExcelExpert Hi, Good day! May I ask if you have formula for the half day please. Just badly needed. Thanks ahead

[@sheree.i4853]

hi what to do if i need to add for 2022 onwards

[@spnn4s106]

wondering if there is a way I can export my outlook calendar into Excel so I can see all my weekly and bi-weekly meetings and make a meeting cadence spreadsheet.

[@farcisbidhan5982]

Total excellent boss. but i need summery each employee with date like leave card

[@PKAnExcelExpert]

will try to incorporate in next version

[@farcisbidhan5982]

@@PKAnExcelExpert sure boss, i am waiting

[@santoshdalal782]

It was every helpful sir,How to add new employee in sheet.Do I need to make again from first.

[@vidyas7132]

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?

[@Stevencyw88]

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?

[@tonybyatt9232]

Hi, when you enter the date on the Mark Leave, would it be possible to add another column, so you have start date and end date, which would then show up in the monthly summary? Thanks

[@leahmagee1498]

Did you ever find out how to do a date range?

[@Topcaat]

@@leahmagee1498 same issue

[@nainisaisindhureddy5157]

Same issue

[@English_News_Every_Day]

the author would not answer clearly he didn't figure it out