Create your own Employee Attendance Template using Excel 365
Vložit
- čas přidán 12. 07. 2024
- In this short video I will design a simple Employee Attendance Sheet using an Excel spreadsheet.
The completed template is based on Calendar Months and caters for up to 5 separate 'LEAVE' types.
Once completed you can easily alter or adjust the Month and the descriptions to better suit your needs.
During the presentation I will use the Excel DATE and WEEKDAY commands.
I will use Conditional Formatting as well as using the VLOOKUP and COUNTIF functions.
To speed up the design work I will demonstrate how to use the REPLACE routine.
The final template is both easy to use and understand.
As usual, if you need any help with the Excel functions or routines that I have used, then please do leave me a comment.
If you enjoyed watching the video, then please do subscribe to the Mr. Spreadsheet channel.
I hope you enjoy watching ...
Here are the links to the various Mr.SpreadSheet resources available to you.
Please help support our channel by subscribing.
Many thanks
Free Employee Attendance Spreadsheet Template - www.MrSpreadsheet.com/product...
You can also visit our websites at:
USA & Canada & World - www.MrSpreadSheet.com
UK & Europe - www.MrSpreadSheet.co.uk
Or our Social channels:
FaceBook - / mrspreadsheets
Instagram - / mrspreadsheet
Twitter - / mr_spreadsheet
#MrSpreadSheet #Excel #spreadsheet #exceltutorial #msexcel #Accounting - Jak na to + styl
![How to create a Quotation System using an Excel spreadsheet - [create your own Template]](/img/n.gif)
Hello Mr. Spreadsheet,
Thank you very much this video was a great help
You are welcome! :-)
Well detailed. Good for us who are slow learners
Thanks Zettie, glad you found it useful :-)
Wow I was really struggling to get the template to automatically display the colours, you're video saved me a lot of frustration! Thanks a lot!
Glad it helped!😊
So helpful!
Glad it was helpful!
Great so interesting subject
Glad you enjoyed it :-)
New to Excel and I found this video to be the best for what I needed. How would I create a new monthly report without losing the old one? I need to go back and watch again in case I missed that. Thank you for this.
Hi Steven. Glad you found the video useful. Suggest you do a 'Save' on your current work and then start on Month 2 etc. That way you will always have a backup of your work. :-)
Thank you sir🥰
Always welcome :-)
Nice video 💝💝💝
Thanks 🤗
First off, a big shoutout for this super helpful explanation! I've checked out a bunch of others, but this one really hits the spot.
But, here's a little something I've been thinking about. I work with temporary staff quite often, and it would be super cool if we could automate the removal of employees from the schedule once their employment end date has passed.
For instance, if someone's last day is in March, it'd be awesome if they automatically vanished from the schedule starting April. This would save me some manual work and ensure the schedule stays accurate without me having to tweak it.
Any chance this auto-removal feature could be on the cards for future updates? It'd be a game-changer for folks like me who juggle schedules and temporary staff all the time. Thanks again for this fantastic resource!
Hi, Thanks for your comments and interest. Yes, you could do this in several different ways, but how about if you just HIDE the LINES of those who have left. Far easier than writing a lot of complicated code. :-)
Hello Mr. Spreadsheet,
Love your videos. Can you do a weekly time sheet for hours worked?
Hi Sophia, that's a good idea, I'll put it on the list :-)
Hello… I am also looking for an attendance tracker that tracks hourly attendance/day (ie: track time missed for late arrivals etc.). I don’t see it in your list of videos yet. Where can I find it please?
Hi, thanks for these episodes.
Can you do a students attendance sheet as well.
Hi Ismaeel, You can use this one for this :-)
Helloo Mr. Spreadsheet
I have compiled the Employee Attendance for March 2022, your instructions were excellent! How do I apply the spreadsheet that is now completed for each month of the year?
Hi, You will need to create new Monthly tabs and then use the Date Formulae with the Months you require :-)
Thank you Mr. Spreadsheet. I created our own attendance sheet but I have a problem adding some data particularly when I tried editing the leavetypes. Can I still edit "define name" in the formula?
Hi Moneth, You can check your work against the original. See the download guide video czcams.com/video/m7Zf8_UWPGQ/video.html 😊
This has helped me so much thank you. I do have a question though, how do I formulate it to include half days for annual leave as well as full days?
Hi, Great suggestion. I will incorporate this into any future templates on this subject matter :-)
I am also interested in how to formulate partial days (for when staff use vacation time or BOT for things like appointments or being late, etc.). Please tag me when you create the future template! @@MrSpreadSheet
It was useful yes
Is there a way so the each person has the total calculated per reason of absence?
Thanks in advance
Hi Dbzitb, I think it already does that ?
I just finished watching the employee attendance video, which was terrific. However, I will never remember all the steps, would there be a written step by step sheet that I could print out?
Hi Gerard, You can check your work against the original. See the download guide video czcams.com/video/m7Zf8_UWPGQ/video.html 😊
Hi,thanks for this tution, how i continue for month that follows,april,may,june etc..
Hi G m, copy the whole file onto 12 separate worksheets, then change the Month Date on each one :-)
This is a good tutorial, but you really have made it even longer than it should be, you don't need to make 'NameRanges', you can just Absolute Cells, Rows and/or Columns ... but of course everyone does it differently , I would just find this easier and quicker by locking cells rows and columns.
But saying all that, you explain things very well :)
Keep it up.
Thanks Scott, Yes, I guess we all use different methods.
How can I do this?
I need needed the tutorial for the borders took to define parameters and how to choose color scheme lol!
But thanks so much!
Well done ...
@@MrSpreadSheetwould it be possible to create 12 different tabs for each month of the year on the same spreadsheet? Or would somethings need to be changed if I do that? Thanks so much!
When I make a new sheet for another month, is there anyway I can have it separate from the original? Right now, everything from month one will carry over to the next month.
Hi, you could simply use a fresh sheet for each month, that way you start from scratch each time :-)
thanks again for the video what would be the best way to add new employees while keeping them in alphabetical order thanks
Hi Griss, I would use the SORT routine.
@@MrSpreadSheet so if I add a a new employee
Will I have too add them to each month or is there a way too add them too every sheet at once
Hi! Is there a way to do a YTD absence column that keeps a current tally of days off?
Hi, I'm sure that could be done, I suggest you extend the absence options, or maybe substitute one of the current ones. :-)
All of my total of leave types are working except for 1. My countif formula is correct, however it is not capturing that specific leave type. How can this be fixed!
You can download a copy at www.MrSpreadSheet.com and check your work against this :-)
Hello Mr. Spreadsheet,
I have a problem when I change the month it shows the data for the last month in the current month. What should I do?
Hi Raafat, You can check your work against the original. See the download guide video czcams.com/video/m7Zf8_UWPGQ/video.html 😊
@@MrSpreadSheet Hello Mr Spreadsheet, I am having the same issue. I am unable to see the download guide as it comes up as a private video.
Thank you it's very useful videos, Can you please send me a copy of the completed spreadsheet
Thank you
Hi Sherif, The file you have requested can be obtained by following the guide in this video czcams.com/video/m7Zf8_UWPGQ/video.html 😊
HI, WITH THE VLOOKUP FORMULA WHERE IT SHOWS DAY INSTEAD OF NUMBER 3. IT SHOWS WED INSTEAD OF TUE. CAN U HELP
Hi Chandhrashekar, You can check your work against the original. See the download guide video czcams.com/video/m7Zf8_UWPGQ/video.html 😊
I want to use this month but the formula doesn’t work. I changed the 3 to a 7 but something isn’t working
Hi, You can check your work against the original, a download is available at www.MrSpreadSheet.com :-)
I need to create a whole year in one sheet?
Hi, The file is designed to be in Monthly format and the date formulae are consistent with this. You could set a yearly range, but obviously this will make the file more complex :-)
Hello,
I would like a copy of the finished product.
Thank you
Hi Diana, studio.czcams.com/users/videow9xdAeYSkxg/edit
How can you add all sick days for the whole year
Hi Griss, sometimes a simple Plus Month1 + Month2 is all that is needed. :-)
Exemplary performed
Thanks Asa, glad you found it useful. :-)
can i get the final result one sir?
Hi David, please follow the instructions at the end of the video :-)
I would like a copy of the completed spreadsheet emailed to me how do I go about doing this?
Hi, downloads are available at www.MrSpreadSheet.com:-)
Can you please share the spreadsheet file, I have done the required.
Hi Deep, please message me on FB :-)
@@MrSpreadSheet nvm.. I will send dm in fb
My first two absence types are not working :(
Hi Ivette, You can check your work against the original. See the download guide video czcams.com/video/m7Zf8_UWPGQ/video.html 😊
I am getting the weekday as Wednesday and not Tuesday for the same year
Hi, you can check your work against the original. A free download is available at www.MrSpreadSheet.com
the date formula didnt work for me
Hi Olivia, You can check your work against the original. See the download guide video czcams.com/video/m7Zf8_UWPGQ/video.html 😊
Why won't from the beginning add up the days. From H to Al?
Hi Ryan, you can check your work against the original. Download is available at www.MrSpreadSheet.com 😊
How can I move everything made from this month to the next month?
The easiest way is to replicate your work :-)
Does this mean starting from scratch each month? 😮
Define name for each row is not so great as you were changing the formulas manually. 10 rows could be done easily, but what if we had more. It's not automation I believe. Thank you for the share thou.
Hi Syed, agreed, here is the link to the product download, you can check your work against the original. 😊 czcams.com/video/m7Zf8_UWPGQ/video.html
never mind sorry
:-)