Excel Templates | Excel Spreadsheets | Attendance Calendar


Free Excel Templates

Attendance Calendar

Posted on August 7, 2009
Filed Under Calendar | 96 Comments



Perhaps this excel file is a combination between monthly and yearly attendance record where you can use some worksheets of this template as a printable monthly attendance forms and collect all monthly data to form employee attendance record for one year period.

Attendance Calendar

This excel file consist of one worksheet of one year attendance worksheet, 12 worksheets of monthly attendance, one worksheet of holiday and one worksheet of employee data. You can enlarge a picture one year attendance worksheet above to see it clearly. In this worksheet, all you have to do just fill the ID of the employee and your targeted year. And all the employee attendance information will reveal automatically. And to make sure that all information will be revealed correctly, you have to fill correctly the rest of the worksheets.

The first worksheet that you have to fill correctly is your employee data worksheet as you can see in the picture below. Try to follow my sample inside the worksheet to understand the purpose of its column.

Employee Detail
The second worksheet that you have to adjust after finishing the employee data worksheet is the holidays worksheet, where the data will be used to calculate the number of working days in each month.

Attendance Holiday

Now, you ready to use your monthly attendance record form where you can see your employee information, number of working days and holidays mark created in those worksheets. These monthly attendance records are based on 5 working days in each weeks, from Monday to Friday. If you needs a record for 6 working days, you can adjust the formula by yourself or you can wait the next version.

Attendance Record

—————————————————

Based on comments and emails I received, there are many request on half day  and customize holidays feature. I have made the new version, V1.22, that can accommodate those requests. There are instruction and sample data inside the spreadsheet. I haven’t fully tested this version, so let me know if you have any problem on using this spreadsheet. But, I still leave the old version, V1.1, to be downloaded, just in case you don’t need those new features above.

You can download the file here :

  Attendance Calendar V1.1 (322.5 KiB, 30,062 hits)

  Attendance Calendar V1.22 (334.5 KiB, 16,047 hits)




You can read and download my other templates below :

Comments

96 Responses to “Attendance Calendar”

  1. Joan Pit on August 12th, 2009 10:04 pm

    Hey, that’s a pretty useful calendar. Did you create it yourself?

  2. sj marrero on August 19th, 2009 4:48 pm

    Can’t get the summary page to pick up postings on the months. Is there a secret somewhere. When I use your template it doesn’t work either.

  3. Adnan Zaveri on August 30th, 2009 4:12 am

    Hi Dear,

    Sj is right Summary page is not working correctly and we are unable to get summary of Present Sick Vacation Other Not Working Yet/Anymore

    Thx

  4. admin on August 31st, 2009 1:39 pm

    Hi Adnan,

    Thank you for your reminder. I read comment written by Sj, but I forgot to fix that up quickly. There is some error in the vlookup formula, but it is fixed now. You can download the new version.

    And for Sj, sorry for late response.

    Regards

  5. mm on September 1st, 2009 7:26 am

    can you teach me how to do it in excel.

  6. Jon on September 12th, 2009 5:55 pm

    Thanks!

  7. Michael on October 17th, 2009 1:36 am

    Thanks so much for this! This is very useful, Ive been looking for semething like this for a long time.
    Thanks again!

  8. Indermohan Rawal on October 23rd, 2009 3:31 pm

    As pointed out in the comments from where can i download the latest version of this calender? as summary page is not bringing up th data entered in other pages in this version.

  9. admin on October 25th, 2009 1:59 pm

    It’s already fixed. You can download through the same download link. Tks.

  10. Rachel on October 27th, 2009 3:22 am

    Hi there,
    This has been fantastic, however, how do you calculate half a day in the month spreadsheets? Ie – S totals one day, how would you total 0.5?

  11. Mathi on November 4th, 2009 10:44 am

    Hi,

    This is Mathi from India, Chennai. I find your attendance very useful.

    Would it possible to share the macros code as to who you are capturing and validating the data for the particular “employee id”.

    I have about 1000 records for whom i need to fetch relevant details for salary revision etc.

    It would be great if you could share the same.

    Regards,
    Mathi

  12. Charles on November 6th, 2009 4:27 pm

    In response to Rachel (Oct 27th 2009). Regarding the need for processing 1/2 days; I have taken this workbook and ammended it to account for 1/2 days as well as showing holidays in blue. I would be glad to share.

  13. admin on November 8th, 2009 4:38 pm

    @ Mathi : There is no macro inside the spreadsheet. You can modify it to suit your needs.

    @Charles : Thanks for sharing. You can also put your link here, so everybody can download through your link.

    Regards.

  14. Rachel on November 9th, 2009 1:36 am

    Thank you so much!
    That would be great if you could pls let me know how you have added the half days, I have already formatted the spreadhseet to suit my work place, so dont want to have to re do it all witha new one.
    Also how come the weekends cant not be chanegd in colour?
    I want to copy the spreadhseet I have created for 09 and amend it to 2010..I await your reply

    Thanks again for your assistane!

  15. Rachel on November 9th, 2009 1:46 am

    Sorry to be a bit more clearer I want to just use this spreadhseet each year and manually add the weekends etc…But the 09 one I have used I can not do this.
    Can youu please assist.

    Thanks again :)

  16. Virgie on November 11th, 2009 10:17 pm

    I would like to add to the attendance calendar columns titled Unexcused Absence (UE),Tardy(T), Personal(PD) and have those columns that I add to tally the number of each of those accurances thoughout the year for each month. I can’t seem to get the formula or VLOOKUP function to work correctly. can anyone help with this?

    Thanks,

  17. Ken on November 18th, 2009 7:15 pm

    You answered Rachel & said you made a modification to the attendance spreadsheet so a half day can be calculated. Can you send that new spreadsheet to me. I will make the appropriate changes in my company populated version. Also my working days comes up (#Name?)what may be causing this, I have put in my holidays; it looks like it is looking for another worksheet or something. Thanks and good job on this sheet.

    Ken

  18. Rachel on November 24th, 2009 1:51 am

    Was there any luck with the above requests?
    Thank you

  19. Jodi on November 30th, 2009 2:30 pm

    How exactly do you download off of your site? I have tried repeatedly and it doesn’t seem to want to download. Am I doing something wrong?

  20. Maggie on December 2nd, 2009 9:44 pm

    Rachel or Charles – I also have the need to include half-days in my employee calendar, so please share if you have updated this!

  21. Rizwan on December 4th, 2009 4:41 am

    I am facing “#NAME?” error in “Working Days”. Plz tell me the solution.

  22. Christine on December 7th, 2009 7:44 pm

    How do you adjust the holidays or add new ones?

  23. Paula on December 21st, 2009 5:46 pm

    I am also getting the #NAME error in “Working Days”. I have not been able to follow the formula to correct it. I am also in need of help with the Holidays calculation, 1/2 day calculation and getting rid of the gray background weekend columns. If anyone has figured these out, please help me! Thanks! vidahlia at yahoo dot com

  24. Laura on January 5th, 2010 8:34 am

    This is brilliant – just what I needed – thank you.

    The only thing I am now struggling with is putting in the UK Bank Holidays for 2010 which I have tried to look at the code to re-write, but it seems quite complex. The UK 2010 Bank Holidays are as follows:

    New Year’s Day January 1
    Good Friday April 2
    Easter Monday April 5
    Early May Bank Holiday May 3
    Spring Bank Holiday May 31
    Summer Bank Holiday August 30
    Christmas Day Holiday December 27
    Boxing Day Holiday December 28

    Any help would be very appreciated.

  25. Amol on January 5th, 2010 10:37 am

    Thanks,. it works.

  26. Zoe on January 5th, 2010 3:35 pm

    Hi, this is brilliant, but how do I modify it to work for for 2010

  27. Sathish on January 7th, 2010 9:59 am

    I Want To Know How We Change 5days Working to 6 days

  28. TJ on January 7th, 2010 3:10 pm

    Very good calendar. How do you get the # of days to calculate for each month? The summary page displays #NAME for working days? Also, all months end on day 28 and just shows # on top and bottom of cells for continuation. I did discover I can copy the previous cell to correct it but I should not have to do that for each month. Suggestions appreciated.

  29. Johnny on January 7th, 2010 9:19 pm

    Did anyone ever get any help with the 1/2 day (or partial day) issue? I also need the capability for occasional Saturday work. Otherwise, this is a great spreadsheet!!!

  30. Karen on January 11th, 2010 7:22 pm

    How do I translate to 2010.

  31. Kris on January 22nd, 2010 4:20 pm

    This spreadsheet is great, very helpful – but like some others above, we need help tracking 1/2 days, is there a way to do that? Thanks!

  32. Veronica on January 30th, 2010 4:29 pm

    I am also getting the error in the Work Days. Can someone help me in fixing. Thanks!!

  33. mohamad on February 2nd, 2010 3:44 am

    thx.. this is very good, your effort is much appreciated.
    Q. we work Mon-Sat. how do i make the changes? cause the networkdays formula calculates sat & sun as public holiday.

  34. Shea on February 2nd, 2010 9:53 pm

    Was there a fix for the error with Work Days? If anyone has figured these out, please help me!

  35. Kris on February 19th, 2010 5:22 pm

    Hi again, as mentioned above, we’re trying to set this up to use but need to know what to do about 1/2 days – it looks like there is a fix from comments above, but I can’t find a link on here, what can I do? Thanks very much for your help with this!

  36. admin on February 21st, 2010 6:14 pm

    Thank you for all of your feedbacks. I have created the new version that can accommodate your needs on customize working days and possibility of putting half day working time.

    Regards

  37. Patti on February 26th, 2010 9:45 pm

    Hello, I am trying to input our holidays in the second version, and they are not populating in the pages, it also changed the formula of “working days” per month to “value” and I can’t change it.
    Also, I need to add personal birthday per employee, because they get that day off, or, a day near it.

  38. mohamad on March 3rd, 2010 10:43 am

    Dear Admin,
    thank you for the second version
    i only changed the working days in the setting and saved the file when i re-open it, it shows “#VALUE!” in the working days column for all the months. i even tried a fresh copy by saved it on the desktop, opened it and saved it without making changes. still shows the same error#VALUE!
    please help..

  39. admin on March 3rd, 2010 3:23 pm

    @Patti and mohamad,

    I think it related to a default setting in your excel software. But, could you send me your file to my email so I can see whether your problem is caused by your excel setting or other problem?

    Regards

  40. mohamad on March 4th, 2010 3:16 am

    Dear Admin,

    What is your email address

    regards

  41. Lisa G on March 4th, 2010 10:12 pm

    Hey all,
    To fix the “Name” problem under working days go to tools and unprotect the sheet. This is what I did and it worked.

  42. René Wenger on March 8th, 2010 10:40 am

    Hey that is a brillant spreadsheet. Just love it. But can anyone help me how to enter 1/2 P (Present) or 1/2 day S (sick)??? Thank you. Rene rene_wenger@hotmail.com

  43. admin on March 8th, 2010 4:11 pm

    @Mohamad : info@exceltemplate.net

    @Rene : Just type the number (0.5) and the formula will calculate it together with P for Present. It only works in V1.22.

  44. wally on March 22nd, 2010 8:30 pm

    hi
    would plz how to input our holidays in 5,or3.6…. different days example our holiday in jan from 1-4
    thanks

  45. Mike on March 31st, 2010 7:29 pm

    Love the calendar. I need the year to run for an academic year, July 2009 to June 2010. Are you planning an update that would accommodate this?

  46. SMS on April 12th, 2010 4:48 pm

    This is just the type of attendance tracker I’ve been looking for! However, our year runs from April 2010-April 2011 and our company only uses paid time off (PTO) instead of vacation, sick, personal, etc. time. How can I adjust this template to fit my needs?? Thanks!

  47. Julie on April 12th, 2010 10:45 pm

    To change the holiday go to the settings tab, click on the holiday date and edit the formula =DATE(G2,4,2)
    G2 is the year, 4=month this is April, 2=day this is the second. This is the formula for Good Friday. It will change the holidays on the monthly tabs.

    Now if I could only figure out how to do half vacation and half sick days. When you type in .5 it just decreases the time present. How do I make it appear in the vac/sick column?

  48. Julie on April 12th, 2010 11:41 pm

    One more request. Our vacation is tracked in hours not days. It doesn’t look like this can accommodate as it is rounding up my decimals. I’m bummed, I thought this would work. Tell me there is a simple formula change I can make to fix it. please please.

  49. Kathleen on May 3rd, 2010 2:44 pm

    I downloaded the old and new versions of the Attendance calendars. In the the 2nd version, I entered all the employees for a total of 62. It only will copy 50 on the monthly’s. Under attendance, I can enter any number up to 62 and it brings up the employee, however, in the boxes under the tracking area is has #n/a. How can I get all of the employees incorporated. I downloaded the addins hoping that would solve the problem but it didn’t. I would really like to utilize your program because otherwise it seems very easy to use.

  50. Maggie on May 4th, 2010 1:55 pm

    Hi is there a way to reflect a half a day taken as a sick day. One of my employees is taking July 30 as a sick day but only half a day, If i enter .5 as the time worked it will only reflect it in the present colum but i can’t take it off his allocated sick days
    Thanks

  51. Jackie on May 18th, 2010 9:41 am

    hi! not sure if this is workable.. but what if employees have different days off.. also is it possible to have a column to reflect remaining leaves?

    Thanks!

  52. Gary on May 23rd, 2010 9:29 am

    CANT GET THE HALF DAY HOLIDAY OR HALF DAY SICK TO WORK IN THE NEW VERSION AND HELP PLEASE. THIS IS A FANTASTIC SHEET I JUST NEED THE HALF DAY FUNCTON

  53. Admin on May 25th, 2010 5:58 am

    This is fantastic – this is ths one i need- thank you.

    The only thing I am now struggling with is putting in the singapore Holidays for 2010 which I have tried to look at the code to re-write, but it seems quite complex. The singapore Holidays are as follows:

    New Year’s Day, Friday,1 January 2010

    Chinese New Year Sunday*,14,15,16 February 2010

    Good Friday Friday ,2 April 2010

    Labour Day Saturday ,1 May 2010

    Vesak Day Friday 28 May 2010

    National Day Monday 9 August 2010

    Hari Raya Puasa Friday,10 September 2010

    Deepavali Friday,5 November 2010 **

    Hari Raya Haji Wednesday,17 November 2010

    Christmas Day Saturday ,25 December 2010

    can u help me to rewrite .

  54. Kathleen on June 3rd, 2010 12:31 pm

    Is anyone monitoring this anymore? We don’t seem to be getting any response.

  55. Jill on September 22nd, 2010 3:54 pm

    Kathleen, did you get your question answered? I have the same question. I’ve added nearly 200 employees, but it doesn’t calculate anything past 50, even though I can see them all of them on the calendar sheets.

  56. Jill on September 22nd, 2010 5:36 pm

    I got the sheet to calculate past 50 now. However, I can’t get it to add additional employees beyond 100.

  57. juan gonzalez on November 24th, 2010 4:34 pm

    what about working with acrual dates that dont fall in one full calendar year. ie.. i was hired on october 22 and on every october 22 of the year i start my vacation and sick days all over again. is there a way to automate it so that it automatically changes the amount of sick and vacation according to the aniversary date?

  58. Lauren on November 30th, 2010 4:37 pm

    Hi is there any way of editing the spreadsheet to make it from April to April? Best attendance spreadsheet i’ve found thank you

  59. Lisa J on November 30th, 2010 6:40 pm

    Thank you – very easy to use…I went in and changed the setting to 2011 and everything updated automatically – Brilliant!!!

  60. Fran on December 9th, 2010 9:28 am

    Got the 1/2 days to work on the old sheet its great! Is there anyway of having the cells split in half so we can see if a person is taking 1/2 day in the morning or afternoon? Overall brilliant spreadsheet!

  61. Elaine on December 9th, 2010 4:03 pm

    How can i change the year

  62. Chirag Patel on December 30th, 2010 6:57 am

    Dear Admin,
    thank you for the second version
    i only changed the working days in the setting and saved the file when i re-open it, it shows “#VALUE!” in the working days column for all the months. i even tried a fresh copy by saved it on the desktop, opened it and saved it without making changes. still shows the same error#VALUE!
    please help..

  63. Aman on January 2nd, 2011 6:57 pm

    Dear Admin:

    really nice and helpful worksheet created. I am a programmer too but working as admin in an office so needed immediate help in monitoring employees’ vacation. I had used your excel sheet in 2010 and now have downloaded 2011.

    i modified few things to suit my needs e.g. giving different colors to vacation or sick or other enteries. i am excited to use your half day rule since last year i just created a different symbol to calculate half days.

    This year i will try but request you too to please see if you can start calculating vacation time from particular hire time of an employee. Also soon user put an resign date all dates after that date for that particular employee should have X rather than doing it manually.

    Big thanks for this wonderful excel sheet and have a happy new year. :)

  64. myrdden on January 3rd, 2011 5:57 pm

    If you are getting the “?NAME” error for the number of working days, you need to install the Analysis Tool Pak under Tools>Add-ins.

    Great spreadsheet by the way. Really well done.

  65. Joe on January 4th, 2011 3:06 am

    Is there a way to change the days into hours? Instead of 1 day you would enter 8 hours.This would be very helpful. Awesome spreadsheet

  66. Henry Hernandez on January 12th, 2011 9:05 pm

    Any way to get it to see more names. I’d like to be able to add over 200 names.

  67. Henry Hernandez on January 12th, 2011 9:28 pm

    I would also like to change the ledged to read.
    P = Full Day / H = Half Day / CT = Company Travel /HCT = Half Day Company Travel / TR = Training / htr = Half Day Training / O = Offsite meetings

    Can anyone help.

  68. Michael on January 21st, 2011 5:56 pm

    I’m looking at a way I could print the summary sheet for all employees at once. Also how could I get the vaca and sick time to calculate to the employee data page by itself

  69. lauren on January 26th, 2011 1:57 pm

    Hi
    Henry Hernandez I managed to change the ledgers. all you have to do is unhide colums I-R on each month worksheet in the rows 8+9 you will see the letters that coinside with the ledgers you simply change it to the letter you wish to input.
    Lauren

  70. lauren on January 26th, 2011 1:58 pm

    Quick question does anyone know how the grey highlight for the input holidays works?
    Regards
    Lauren

  71. Henry on January 26th, 2011 9:37 pm

    Hi Lauren,

    Thanks for the help with the ledged. I managed to change it, but now I would like it to recognize upper and lower case. Right now it doesn’t. Any idea’s?
    As far as the grey highlights go, that’s caused by “Conditional formatting” there are a couple of rules in place to turn it grey if it’s on a weekend or holiday, they can be changed to any color you like. All you have to do is look for “Manage rules” under Conditional formatting.

    hrh

  72. lauren on January 27th, 2011 8:28 am

    Hi Henry brilliant thank you. In my case I did a countif and used say a formula =COUNTIF(July!AA10:July!AG10,”upper”"F”) if your wanting to change it in the ledger, in the formula where it look for the cell that is on row 8 add “upper” or lower before.
    Lauren

  73. lauren on January 27th, 2011 12:40 pm

    Hi still struggling with conditional formatting I’m wanting to be able to add employees birthdays onto EmployeeData work sheet and have the cell that corispondes to their birthday be highlighted. Any help much appreciated.
    Lauren

  74. Ivo on February 1st, 2011 9:54 am

    Hi I came across this wonderful template and am trying to adapt it for our needs. I seen some comments to which I have not seen answers. Ex how to change the Holidays. The formula is quite and complex and if you give an example (like Good Friday) it would be great.

  75. Henry on February 1st, 2011 7:21 pm

    You just need to modify the dates on the setup page.

  76. pratik on February 8th, 2011 7:28 am

    dear admin
    this is very good. but its not work in Indian calender can u plz help me for that

  77. pratik on February 8th, 2011 7:31 am

    giving different colors to vacation or sick or other enteries.

  78. Tina on March 2nd, 2011 3:57 pm

    Hi Admin

    Brilliant spreadsheet, thanks so much for sharing it. I have a problem where if I put in 0.5 for half a day or 7.00 hours, it only puts the result in the Present column where I would like to record half day sick or holiday, please can you let me know how to fix this, also, my year runs April to April and I would be so grateful if you advise if it is possible. Thank you for your time.

  79. Christina on April 6th, 2011 6:18 pm

    Brilliant!

  80. Christina on April 7th, 2011 12:12 pm

    I need to add additional company holidays… I am completely baffeled on how to do it… Can anyone help? I can’t seem to find the e-mail address for the admin to send him an e-mail.. HELP please.
    Thanks,
    Christina
    christina0925@ymail.com

  81. ryan cris on April 30th, 2011 11:25 am

    hi sir,

    i am quite fascinated how you were you able to change the color from one cell to another when you choose yes or no on the working days.

    could you please tell me what is the formula behind or share how you made it

    your prompt reply is highly appreciated..thank you in advance ;)

  82. Mary Ellen on May 9th, 2011 7:51 pm

    Can you advise–is there a way to modify the spreadsheet for hourly employes that take sick and vacation time in half hour increments? I need to be able to track time in half hour increments, for instance Susie worked 4.5 hours today and left early, using 3.5 hour of sick time….

    Thanks!!

  83. Mike on August 10th, 2011 11:49 pm

    I need to have a rolling calendar for attendance. How do you add more months to the calender and to the main sheet without messing up the formulas

    Thanks!!!!

  84. Ken on September 30th, 2011 11:48 pm

    I used the previous version and liked it. I like the revisions that you made to this one and I am understanding how it works. The only problem I cannot fix is that if I add a new employee to the list at some point during the year and I want to sort it by name it messes up all the data that was input for the days. Is there a way to sort the employee page and it would sort the months with the employee name and their data?

    Thank You Very Much

  85. Yona on November 11th, 2011 5:39 am

    Dear Admin,

    I went through the above comments but still can’t figure out how to differentiate 1/2 day P and 1/2 day S and also sum of P & S respectively. Can give the formula? Thanks.

  86. Ben Jauert on December 15th, 2011 8:59 pm

    Awesome spreadsheet! I have over 100 employees. How do get them to show up on the attendance page. It will only calculate the first 50 empployees entered. Thanks so much.

  87. Emily on January 16th, 2012 4:39 pm

    need to be able to enter sick time taken by the hour not the day ??? Is there a way to do so?

  88. Susan on January 30th, 2012 9:56 pm

    I need the same as Emily. Sick time and Vacation time entered by the hour. This is a great tool for me to use if it can be adjusted for hourly instead of daily.

  89. Umar on February 29th, 2012 6:19 am

    Hi,

    Thanks for Attendance Calendar V1.22, it is really helpful for me to maintain the attendance now.

    NW GJ,
    TY

  90. Umar on February 29th, 2012 6:20 am

    Can you help me getting a chart for daily work progress,

  91. Umar on February 29th, 2012 6:32 am

    Can you add and additional column or sheet for List of Planned leaves for the employees

  92. Betty on March 2nd, 2012 5:01 pm

    How can I have the spreadsheet calculate the days and the hours of an absence. For example when you input “V” as the code it automatically inputs a 1 in the column but what if an employee uses only 2 hours of “V” for a specific day?

  93. Beth on March 5th, 2012 3:47 am

    I really love this spreadsheet. However, I seem to be with an awful lot of people as to how to calculate (i) in hours as we track our time by hours to the second decimal (i.e., 8.00=1 day); and I need to be able to calculate both Sick, Vacation and Present times to equal that 8 hour day. Please help. Great spreadsheet and I would LOVE to utilize it!!!

  94. Beverly Heard on March 21st, 2012 8:51 pm

    This spreadsheet is great!!!!Just a few questions, I have about 300 employees and need to copy the formula on each month. How do I get this to calculate in the attendance calendar without showing the “NA” error message? Please help me….

    Thanks!

  95. Ntum on March 22nd, 2012 11:32 pm

    This is exactly what I search for. Would you mind explaining how to create the sheet”setting”
    Tks a lot.

  96. Rawan on April 22nd, 2012 11:11 am

    hello
    I would like to tell you that february 2012 it is 29 days and not 28 so how can i fix that problem

    Thanks alot

Leave a Reply