Employee Attendance Calendar
- Version
- 101'030 Downloads
- 85 KB File Size
- January 4, 2022 Updated
- 91 Number of comments
- Rating
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.
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.
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.
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.
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.
Hi, How can I alter the Payroll monthly period from 1 to end of month to 25 to 26 of every month?
Please resolve my problem in our office only sunday is a eorking day but in your older version Sat & Sun both are holidays, kindly help me how i can change it. your new version is good but so many difficulties occur for example when i save it and open it again the wroking days formula is not working. plese help
Hi, This is Kanwal i like your Attendance Calendar V1.1 , can you please tell me how i can change 5 working days into 6 working days, i also use your next version but whenever i use it , it gives error. pls help me in this regard i wanna to stick on old version, its not give error. pls reply me asap
thanx
Hello,
I’m currently having difficulties changing the background color for days marked as the weekend. I understand that conditional formatting is to be used, but don’t quite understand the formulas needed to make the change. Any help concerning them would be greatly appreciated. Thanks.
Hi i really like this template but when i save it and open it again the wroking days formula is not working. plese help
@DK : In this version, you can’t sort employee names, because it will break the formulas inside those cells which cause other formulas follow incorrect references.
Hi Mr Musadya, can you make can you make customized employee tracker sheet, if yes , can send me the details for it
First of all I would like to thank you for your hard work by making this excellent peace of spreadsheet for calculating staff attendance.
I have modified the spreadsheet to suit my staff level and it is working like a charm. just noticed a small flaw and seeking your assistance to rectify it. The problem is that in employee data worksheet, the ID column and name are not dependant on each other. For example if I enter 5 user names and sort the order alphabetically, the month worksheet is not retaining correct leave information against their respective user names.
is there any way to sort this out?
cheers
Thank you for the update, it really made it easier.
Your quick reply was really appreciated.
Thanks and regards,
Melber
Hi,
This sheet is exactly what I was looking for, but I have a question: Since we are in the middle east are weekend falls on Friday & Saturday, how can I change it from Saturday & Sunday to Friday & Saturday.
Thank you,
Kind regards,
Melber
How do you use the half day function for vacation. When i put in .5 for the day, it automatically places that half day in the personal time field. If you put in .5 V it does not rec. the value.
Thanks in advance
Hi,
Wanted to understand how I can modify holidays as per different dates. Since in India the public holidays are not fixed. Also if i want to make any changes the working days per month are not getting calculated.
Regards,
Mitesh
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
This is exactly what I search for. Would you mind explaining how to create the sheet”setting”
Tks a lot.
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!
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!!!
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?
Can you add and additional column or sheet for List of Planned leaves for the employees
Can you help me getting a chart for daily work progress,
Hi,
Thanks for Attendance Calendar V1.22, it is really helpful for me to maintain the attendance now.
NW GJ,
TY
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.
need to be able to enter sick time taken by the hour not the day ??? Is there a way to do so?
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.
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.
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
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!!!!
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!!
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 ????
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
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.
giving different colors to vacation or sick or other enteries.
dear admin
this is very good. but its not work in Indian calender can u plz help me for that
You just need to modify the dates on the setup page.
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.
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
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
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
Quick question does anyone know how the grey highlight for the input holidays works?
Regards
Lauren
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
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
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.
Any way to get it to see more names. I’d like to be able to add over 200 names.
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
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.
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. ????
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..
How can i change the year
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!
Thank you – very easy to use…I went in and changed the setting to 2011 and everything updated automatically – Brilliant!!!
Hi is there any way of editing the spreadsheet to make it from April to April? Best attendance spreadsheet i’ve found thank you
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?
I got the sheet to calculate past 50 now. However, I can’t get it to add additional employees beyond 100.
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.
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 .
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
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?
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
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.
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.
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?
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!
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?
hi
would plz how to input our holidays in 5,or3.6…. different days example our holiday in jan from 1-4
thanks
@Rene : Just type the number (0.5) and the formula will calculate it together with P for Present. It only works in V1.22.
Hey that is a brilliant 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
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.
@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
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..
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.
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
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!
Was there a fix for the error with Work Days? If anyone has figured these out, please help me!
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.
I am also getting the error in the Work Days. Can someone help me in fixing. Thanks!!
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!
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!!!
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.
I Want To Know How We Change 5days Working to 6 days
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.
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
How do you adjust the holidays or add new ones?
I am facing “#NAME?” error in “Working Days”. Plz tell me the solution.
Rachel or Charles – I also have the need to include half-days in my employee calendar, so please share if you have updated this!
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?
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
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,
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 ????
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!
@ 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.
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.
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