Automated Attendance Sheet in Excel with Formula | Download Attendance Sheet Template
Vložit
- čas přidán 7. 08. 2024
- Buy Simple Sheets Employee Scheduling Template
www.simplesheets.co/a/2147525...
All purchases help to support this channel - thanks!
Download the featured file here www.bluepecantraining.com/wp-...
In this video I demonstrate how to create an attendance sheet in Microsoft Excel. The attendance sheet has the following features:
1) You can select a month and year and it will show a calendar with non-working days formatted in grey. The non-working days include bank-holidays or any other holidays you want to specify
2) The current day is shown on the calendar in orange
3) You can specify 'P' for present, 'A' for absent or 'H' for planned absence. These values can only entered on working days
4) A count of present, absent and planned absence days for the month
5) A percentage breakdown of present and absent days for the month
Table of Contents:
00:00 - Intro
01:32 - Create the attendance report drop-downs for month and year
05:22 - Create the date headings (Excel 365)
12:51 - Create the date headings (Old Excel)
17:20 - Conditional formatting to show non-working days in grey, today's date in orange and P, A, and H in different colours
26:13 - Data Validation to prevent entry on non-working days
30:22 - Attendance record summary calculations
------------------------ - Jak na to + styl
Very complete and brilliant work. Extremely detailed and professional. Thank you Chester!!!!
all thumbs up to the greatest excel teacher. Hope everyone will subscribe for Chester's hard work. Thank you Chester for your valuable time and knowledge
Awesome Sir, I'm troubled since last week while making attendance record sheets your way of teaching is really helped me. Thanks a lot, Sir for also giving your work files in the description. 🤗🤗🤟.
Absolutely brilliant. Thank you, Tugwell. Subscribed and smashed the bell icon
This is amazing and so helpful. Thank you!
I need to add attendance types per week, example: how many P, A, or H there are from Nov 1-7, 8-15, etc.
Awesome tutorial! Thanks Chester. Thumbs up!!
Best excel channel on the planet. Thank you so much
Glad you think so!
Great work, thank you Chester!
This is excellent work. I want to include Saturday as working day
So complex, but very interesting.
Thanks for sharing. Very impressive work. What if I like to change the working days so weekends would be Friday & Saturday? How would I make changes. Thanks again
Really helpful, thanks a lot sir 🤗
Is there a way to hide non-working days, instead of just graying them out? thank you so much for sharing this!
Brilliantly done
wanted to know, How would i add or delete holidays so it effects the attendance sheet? thanks
Thank you Chester!!!!
hi This is great, how about the filter by year, month and group type? Can you show how to fix the group type filter. thx
Love this Video - Ive got a question please - If I want to add a choice to add days to blocked out / greyed - how could i add these to the formula - eg: mon and wednesday = Line 1 Tuesday and Thursday = Line 2 - im thinking of adding a line button where staff can select a drop down 1-6 and based on the result grey out the days they dont have lessons - thoughts - thankyou
Thank you for this. I was able to follow this video and create something for work. I ended up adding a row in front of the names section with the branch of the employee. I added another sheet with the table of the total of number of employees at each. I was wondering if there was a formula to count the number of “*p*” for today() by branch for the extra sheet I created
This is Great!
Thank you so much, Subscribed.
Thanks for the sub!
Really thank you for this video, can we add half day leave column in as well , customization is possible right?
Amazing, smarter approach and we'll define. As a teacher it will work too Please also add a fee collection sheet too.. 👍
Hi chester, is there away that you can make a video showing how to conditional format specific staff in this employee in case some are working on non working days and I don't want to conditionally format the Ce for that specific days. I hope I didn't confuse you with my question
you rock man!
hi Chester, not sure if you can help , I need to find out the repeated attendance vs new attendance of same event that happens on different dates with in the year with email address and have a comparison summary breakdown by the date/ year to see the increasing or reduce data in number w excel?
. Thank you
Thanks for your help. Can you tell me why the formula is not picking up the cells that are blank if the month ends like February? Its not picking up my cells at the end as blank
is there a way to have it show specific days of the week with corresponding date? example, only Sundays, Wednesdays and Thursdays? Thank you
This is awesome Chester
I love the formula that you use it Month(February&0)
Great video! Is the sequence formula not available for MAC users? I am a very experienced excel user and am new to MAC. Doesn't seem like this is possible with a Mac unfortunately. Please advise. Thanks!
Love this!!! I am wondering if it can be taken a step or two further but at a loss as how to do it. So I have the worksheets saved as each month. Is there a way to have a summary worksheet that will tally everything to the current date? And is there a way to have an employee "flagged" if over "x" number of absences?
This is awesome content, is there a way to after you enter the absent or present for the month. Can you for example….
Fill august in….switch to December by changing the month. But when you change all the absent and present marks are still there. Can you save it and go back and forth after the data is entered to reference back and forth without creating new sheets?
This is what I need to know as well
Thank you Chester! How can I update this to make the group cell function with different classes?
Perfect to use for school intervention attendance...how could I amend to show a weekly percentage please?
when I change the month, how do I get the data to change with it?
do you find an anwser?
hello, this video is very helpful! thank you, however the sequence function does not work well for me, I have the months in hebrew, I tried everything - changeing it to english, still didn't work, changing it to numbers (did work, but days didn't change according to the month). would kindly use your help 🙂
This is great
detailed explanation, thank you so much !!!! but as you done the format as explained in this video in excel(old format), can you explain this as same as in form of google sheets. pls pls pls !!!!!!!!!!!!!!!!!!!!!
Thank you Chester, Just one query, how does the sheet change ? If I change the month on top ? Could not find this in this video
I am Completely lost.
I feel completely a novice.
Thank you for propelling me to learning excel
Hey Chester, thanks for your great informal expertise. I would have a question though: The values P A H once I write them to the cells, they remain.
What idea would be appropriate to extend the functionality of the attendance sheet to store the entered values for each month and being able to switch between them?
Would be very great if you could give us a hint or you made already a video on this topic eventually?
I made a similar calendar myself a few months ago. I left the sheet blank and duplicated it for each month. I had to manually enter several things each time and this makes the process a lot easier.
@@CazzPhoenixwhere you able to figure out how to remove the “non working days?”
My company runs 24/7 so I need the weekend days but I’m not sure how to edit them
How do have the entire calendar clear when you choose your next month? All the Ps etc stay in the cells when I switch months
are you able to create interactive dashboards from this? i would like to summarize all this data to my manager when she opens this file
Hello! Can you please advise me how to show P (present) clock-in is on or before 9am and clock-out is on or after 5pm, HD (half day) clock-in/out is either no entry, late, or undertime, and A (absent) clock-in/out has no entry? I am failing to solve this.
Many thanks!
is there a way i can have a consultation session with you please!!
Thanks Sir, But I had a question, how can we get a data of employees leave with dates... Suppose employee 1 was on leave from 01 to 03 and then 09 to 15 of a month. Can we extract this with dates from this Attendance sheet.
Hello! I run a youth center and will be using this sheet to track attendance. The only difference is that my "H" value will be "X" to denote days we are not open. With this in mind, once I get to the formula in the P% cell, the resulting % is 117%, prior to even excluding the "X" values. Would you have any idea why this would be? I have tried redoing all the formulas multiple times and checked the conditional formatting and the data validation and I cannot seem to find a way to get it to a value based on 100%. Any assistance would be much appreciated!!
hI!!Thank you so much for this, i do have some additional questions though.
When i change the month or the year, the data i've entered remains. how can i clear the sheet for the new month/year selected but when i go back to the previous month i can still view the data i've entered for that month.
did you get an answer to this? I am looking to do the same thing.
thank you but in my case i want start the month from last month 26 to next month 25 how can i do that
Hi Chester, tried the formula =sequence(1,DAYS(EOMONTH(DATE(E3,MONTH(B3&0),1),0),DATE(E3,MONTH(B3&0),1))+1) But unfortunately it shown #NAME?. Could you advise in this problem and appreciated
I got the same thing. Did you ever get this resolved?
Hi Chester, Great work. I've followed your instructions to the T and have everything working like a charm. The only issue that's just come to light is once you've filled out the month and move onto the next month to record the previous months dates will cross over and not saved separately. So you'd have to wipe it all away and start again. The way it has been set up would be perfect to be able to look back historically at someones attendance whilst adding to future ones. Are we looking at VBA detail to keep hold of this data or is there a way of saving/reviewing all inputted data over the year?
Thanks in advance!
Simon, you'd have to create a separate sheet for each month using this method. Otherwise set up a database to feed through to the attendance sheet - which would be a completely different solution.
@@ChesterTugwell Thanks Chester for your response. Okay thank you, I'll have a look into that. Thank you for the video learnt a lot of new things from it! Keep up the good work!
@@ChesterTugwell could u kindly make a video to this solution ?
Is there a way of having the monthly stats recorded into a central page that allows you to see each employee’s monthly stats for the year?
Thanx
Hello, I find your video very interesting, maybe you can help me, we have a billiard club with max 20 people, what do we want to do we only play on Tuesday and Thursday, we want to monitor the presence of the players, then know how many times they have been present per month and then per year a separate sheet for the names, the question is now can you help us?
I'm having an issue saving from month to month. Whatever I save in June 2021 ends up being saved also in July 2021. If I delete or change something in July it will also change June's info. How can I separate the data from month to month?
Hi, did you find a resolution to this issue? I’m finding the same thing.
im looking for the same thing also...
I think this problem, No one solved up to now😍
Fantastic Chester. I do have a question though. I want to use this type of sheet but maintain historical records. How do I get it to maintain attendance records for only the month they were entered? When I change the month the attendance entries remain in place
Terry, create a different sheet for each month.
when I add the formula my, "True" and "False" show. How do I elimiate them from showing so that I can use the calendar to add the other stuff into each cell?
Took awhile, but I fixed it. My weekends didn't highlight as I was following the video. It's actually Holidays_list (as it was named in Name Manager. The correct one is =OR(WEEKDAY(H$6,11)=6,WEEKDAY(H$6,11)=7,COUNTIF(Holidays_list,H$6)=1,H$6="") The video shows Holiday_dates. You can use Holiday_dates but be sure to name that in the Name Manager.
every schedule tutorial or online info start with first day of the month ... i need my schedule start from the 19th till next month 18th how dus it roll over the months like that
please help
Great video! Very helpful!
The problem that I'm facing is that, whenever I change the month, the data won't change, which means that I have to delete the data in the cells every month. But in this case I can't keep track of every month.
Any helpful advice or guidance!!!
Hi. You'll need a sheet for each month.
How to mark all the blank date cell of certain 'OFF Day' of different employees across different sheet of excel and fill up with 'OFF'? Please help me.
shit ahahahh wtf is that day formula man, how were you even able think that logic .....! damn....! you are absolutely amazing....!
Can anyone help me with the sequence formula?
In June 2024 P% Coming by negative percent. What should i do?
Anyone please HELP. I am trying to add an additonal row next to "H" ( holiday). For example; MA, for meeting absence. How can i add?
How to create weekly attendance e.g week 40
Hi Chester,
Why my "today" date is off by one date from actual?
Can somebody help me?
TQ.
How to I linked Page with others pages ?
whats the formula if i wanted to just blackout mon-thursday?
The days didn't show up After I copy it across. The formula had been copy but only the days somehow weren't showing. Have anyone got the same problem? I am using office 2019.
How do I remove the blacked out days??? I am unable to insert A or P. Otherwise this is a good tutorial.
how i change the saturday to a working day?
Someone has found out how to save the attendance record for the different months?
Also looking for that?
Did you find out please?
Need that as well. He skipped right over it
F3 option doesn't work - just adjusts volume?
You probably need to use your Fn key.
I'm having an issue saving from month to month. Whatever I save in June 2021 ends up being saved also in July 2021. If I delete or change something in July it will also change June's info. How can I separate the data from month to month?
You will need to create a different sheet for each month.