#82-How to Make Automated Attendance Sheet in Excel - Step by Step Tutorial
ฝัง
- เผยแพร่เมื่อ 1 ต.ค. 2024
- Are you tired of manually tracking attendance for your employees or students? Look no further! In this step-by-step tutorial, we'll show you how to create an automated attendance sheet in Excel using Saha Computer's Smart Attendance System.
First, we'll walk you through the process of setting up your Excel spreadsheet with the necessary columns and formulas. We'll cover everything from creating a new workbook to entering employee or student information and tracking attendance.
With the Smart Attendance System, you can easily monitor and analyze attendance data for multiple individuals or groups. Plus, you'll save time and reduce the risk of errors by automating the process.
Whether you're a small business owner, HR manager, or teacher, this tutorial is perfect for anyone looking to streamline their attendance tracking process. So, grab your computer and follow along with us!
Don't forget to like and subscribe to our channel for more helpful tutorials like this one!
***********************************************************
~:Related Video Links:~
Previous Video: • #81-Tally Course: Actu...
Next Video: • #83-How to create an a...
***********************************************************
Related Video Links:
***********************************************************
~:Support Our Work Financially:~
Premium Download : sahacomputer.c...
***********************************************************
~:Join us on Social Media :~
🔔Subscribe to Our Channel: bit.ly/2TTblTG
Visit Website: www.sahacompute...
Join Us on Patreon: / scec
Facebook: / scecpkt
Twitter: / manojsaha2016
***********************************************************
~:Hire Me:~
If want me to help you to build Excel-based applications, you can Hire ME!
Contact me: solutions4utb@gmail.com
***********************************************************
~:Sponsored:~
***********************************************************
Keywords: saha computer, smart attendance system, attendance in excel, how to create attendance sheet in excel, attendance, attendance sheet in excel, attendance sheet, excel attendance sheet, employee attendance tracker, attendance tracker excel, employee attendance sheet, student attendance register, attendance sheet format in excel, employee attendance sheet excel, how to make an attendance sheet, attendance sheet in excel with formula, how to make attendance sheet in excel.
#ExcelAttendanceSheet #AttendanceSheetTutorial #ExcelTutorial #StepbyStep #AttendanceTracking #Productivity #Organized #Efficiency #MicrosoftExcel #Tutorial
You r genuis my brother .. big effort maked .. i ask about attendance .. when i put ( P,A,H) in any months . I find it the same schedule i want each month to be kept separately ..
Latest Attendance Tracker Video: th-cam.com/video/1tGSpi_4B_o/w-d-xo.html
anyway nice information it would be better for me can u help me for the subject wise attendence
Give me some information, so that I can help you.
How to find out,when an employee worked for more than 24 hours continuously ...I.e yesterday' day and night and again today's day...because an employee should not continue his duty more than 12 hrs ....so please clarify
This attendance sheet is not time based so it is clearly understood that the employee works within office hours.
This is not time tracker this is attendance tracker
This is very practical. Thank you for the clear guidance. I have 2 questions: 1. how do you add Saturday onto the formula since you already have "Sun"? 2. I noticed on the "Copy Sheet" macro it said "Updateby Extendoffice 20160704. Could you please elaborate?
Watch this video given below, I think your Saturday problem will be solved. "Updateby Extendoffice 20160704" Ignore or delete this.
th-cam.com/video/NUK8PXLfggo/w-d-xo.html
For our office we have sat and sun as weekoff. How can i insert that in formula for both days. As in video it shows only sun.
Highly Appreciate your video which will be helpful for everyone. Hatsoff
I will check today
Latest Attendance Tracker Video: th-cam.com/video/1tGSpi_4B_o/w-d-xo.html
I created excell sheet for the year of 2021 , and included its all of the holidays even.but it desplayed as 2020; what is the reason? Is it still tha pc date is 2020 ? Thkz
@@SahaComputer . Thaks a lot. Very much appreciated .
Latest video on Attendance Sheet, Payroll Sheet and Pay Slip, All In One: th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
Bravo,, THIS IS AWESOME,, Great effort thannnnnnnnnnnnnnnnnnnnk u ,, u r Geniusssssssssssssssssssssssssssssssssssssssssssssssss
Latest Attendance Tracker Video: th-cam.com/video/1tGSpi_4B_o/w-d-xo.html
Great video....Can you please explain why you have given 20160704 formula in 30:39 seconds....
It is a comment ignore that. I have given there for my reference.
Do you have a sample code for time in, time out for employee/teachers using RFID card (tapping) badly needed pls. thanks in advance
Watch this video
th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
Hai,
This video very useful for me, but one dot end of the video ,how to copy of html ??. Can you please update me
The year should also have a drop-down list to choose the year and the attendance sheet changes according to the selected year and month.
Overall great VDO. Teaching is good, felt that you are in front of me...
@@SahaComputer One more thing, in the Sunday and Holiday columns should be displayed as "S" and "HL" in every cell automatically, then this will be more attractive...
Appreciate Video! Excuse me for chiming in, I am interested in your opinion. Have you heard the talk about - Weydaniel Organized Dominator (Have a quick look on google cant remember the place now)? It is a great one of a kind product for getting 7000 plus project management and business templates without the normal expense. Ive heard some amazing things about it and my buddy at very last got astronomical success with it.
I have added it by my self
@@MrAram85 how
Latest Attendance Tracker Video: th-cam.com/video/1tGSpi_4B_o/w-d-xo.html
Sir ours is multi branch multi city.how we can make attendance by branchwise and at the same time consolidated and sync with all branches
Is this only for 2019? What if year changes?
@@SahaComputer My sincere love & respect to u all
Latest Attendance Video: th-cam.com/video/1tGSpi_4B_o/w-d-xo.html
Hello
First of all, thanks a lot for your nicely explanation.
After evaluating the START DATE, comes an error, #VALUE!; I don`t know why.
would really appreciate if somewone helps.
Thanks
Jawid
Please check the formula carefully.
I am so grateful to God Almighty and to the producer of this video, this lesson had push me a steps further while on the path of Success. Like I always maintain, Success is not a destination but rather a PATH. Again I say big thank you yaaah...
You are very welcome
Please share a video in which can calculate half day as .5
If you want to get the total of total present including halfday, then create another column and type =Total Present+(Total Halfday /2)
attendance change nahi ho rahi every month same attendance show ho rahi hai
Latest video on Attendance Sheet, Payroll Sheet and Pay Slip, All In One: th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
Thank you very much Sir.
very helpful.. You explanation is very simple for learners like me.. Superb..God bless you👍
Latest Attendance Tracker Video: th-cam.com/video/1tGSpi_4B_o/w-d-xo.html
Hello, I like that excel! How can I make the year rollover. If I want January 2024 rolling over automatically , How can I make that happen? Thanks
See video 101,130,165
Masterpiece................Great effort
Latest video on Attendance Sheet, Payroll Sheet and Pay Slip, All In One: th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
How can I do this for school attendance when we only have school on Sundays? I need to make calendar with only Sundays. Is this possible to make? I don’t need Monday to Saturday, just Sunday only. But I don’t know how to do this.
You can create a single sheet with dates of all Sundays in a year. If you need my help contact solutions4utb@gmail.com.
Great video and easy to follow! How do I change the year from 2019 to 2020?
Latest Attendance Tracker Video: th-cam.com/video/1tGSpi_4B_o/w-d-xo.html
The clear sheet and copy sheet is mot working for me. I have copied the same code but facing issues mainly with copy sheet and when i save the excel file it does not allow me to save the codes. Please help much appreciated
File type should be macro enabled.
Great tutorial! I have a question please, the half days are counted as whole days in the total work days column how to fix it.
Latest video on Attendance Sheet, Payroll Sheet and Pay Slip, All In One: th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
Awesome Video. Please help!
I am facing issue with Copy Sheet, it is not taking the name of workbook even after giving the cell value where my month has drop down. Its saving as Sheet(2) and so on. Anu suggestions?
Sorry for the late reply, if you have solved the problem its fine else you can fill out the form link given in the description box and get file and check where you are making mistake or you can also send me the file for checking.
What is the formula for year???
Check out this video your problem will be solved and you can also learn new features.
th-cam.com/video/NUK8PXLfggo/w-d-xo.html
Here is serious query-
1- When we make start date- we get the number of days from 1-1-1900 & then we convert it to current month & date though date formatting. Naturally it is calculating days only till current year from 1900. Then is it will calculate date of any month BUT ONLY TILL CURRENT YEAR ONLY (Because it has calculated days from 1900 till date of current year only).
2- Now what would happen when year changes?.
3- I started my attendance from Nov-2020. But when I moved to January it shows days on January 2020 only while this should be of Jan-21! That is the biggest flaw I noticed.
4- It only works for current year.
5- If it is related to computer date & takes it from 1900 then I have to prepare it again on 1st Jan-2021 after 12.00 AM (Work in night) because it should be ready on 1/1/21 morning.
6- Please tell- If it would be automatically updated for new year after 12.00 AM on Jan 1st 2021? I am not sure. If not- then this all is useless.
7- Secondly- If we change month through drop down, days do change but data do not. That means we have to prepare separate sheet for every month!
8- Now when we are making it separate sheet for every month then what is the importance of making it automatic? Because we will never use it in any sheet as that has to remain fixed else it will show data of same month, for all months!
9- Is there any way to populate days & month of Year 2021 now?
10- Please post- else it is not of any use other then playing "Excel=Excel" for nothing. Neither automatic date & day system of any use (We will never use) nor it updates the data filled in sheets for displayed months! Learning VBA for excel learners is a curse.
Latest video on Attendance Sheet, Payroll Sheet and Pay Slip, All In One: th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
But my workdays r not coming accurately Although applying the same formula😢😢😢
Extremely sorry for the late reply, Did you solved the problem?
Hi thanks for this awesome vid.. Here in philippines we have many kinds of holiday.. trying to set regular holidays, special nonworking holidays and special working holidays.. how do i set this in the same timesheet? with conditional formatting as well.. i'm kind of stuck. trying to copy what you do.
You can type your weekends in Weekend list and Holidays in the holiday list in the setting sheet. If you have different holiday list then you cannot do that in this sheet. But still you can do this by keeping all holiday dates in single list.
Nice vdo sir...But I chose January and put p mark for one employee..then I switched to February there also the same p mark was there where I put it for january ....what is solution sir....How to feed the details in each month and differently sir
Watch this Video: th-cam.com/video/1tGSpi_4B_o/w-d-xo.html
But what if The school year starts from October 2020 and ends September 2021. I notice that the Year doesn't change in the dates. How to fix that?
Latest video on Attendance Sheet, Payroll Sheet and Pay Slip, All In One: th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
If your providing us knowledge,,so provide it deeply😡 Now after 30.33 it is bothering me,,how it has done.😟
Hi Prasant, I teach about the code because video was becoming too long but I made seperate video for that. You can watch videos, Links are given below:
th-cam.com/video/msUV24L9x4M/w-d-xo.html
th-cam.com/video/Ct7Wd3BLb0s/w-d-xo.html
It's easy to make attendance sheet for a common holiday. But if Different employee have different offday (Holiday), How can you do attendance sheet for them?
th-cam.com/video/pDGmmIhqV2Y/w-d-xo.html
Sir when I put the code updateby extendoffice 20160704 it shows complied error expected end of statement
Hi Arpit, Delete that line or put single inverted comma before that line because that is a comment.
Hi for the copy sheet and clear sheet. I already follow the instructions you given and follow the same code. But why when I press my copy sheet and clear sheet nothing’s changed. I need your help on this. Thank you
May be there is error in code. You can send me your file to check.
Thank you for the video. But i have a question. You did not explain how you wrote the code in Visual Basic
Sorry for that, all the codes are simple. What do you want to know?
Dear sir, After save the file, copy sheet and clear sheet functions are not working. Please explain.
Thanks.
May be coding has problem, you can send the file to check.
thank u so much for video
Latest video on Attendance Sheet, Payroll Sheet and Pay Slip, All In One: th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
Sirji
How do I get d code so that I can copy n clear for next month? Plez give me solution for this as m stuck on this only...
Visit our blog link given in description box.
I tried to use Macro for Copy Sheet but the new worksheet is not open with the name that I wanted. However I tried, getting only the sheet2, sheet3, .....
You must have made mistake in cell reference.
Thank you, sir, I gain a lot from this video. Sir please! How can I find the code for clear and coping?
If(C7
Im trying to add the function for holidays but not getting any luck. Its all N/A no matter the month i chose. Please help
whats the problem?
Good, I think that u should have loaded the script file here, as we have to write them word by word. Any way well done. Thanks.
Code details: sahacomputercentre.blogspot.com/2021/08/How%20to%20make%20an%20automated%20attendance%20sheet%20in%20excel%20with%20formula2019%20V2.0.html
I want the dates automatically in a single cell on which dates he absent
for example in a month a person put 3 leaves we would like to represent the dates in single cell ie 3,5,11 like that
Yes, it can be done with VBA. I will upload a video on that within this week. Join our channel to get the tutorial within this week.
How comes 23 day present if 22 full day and 1 half day present it should be 22.5
23 is total working days not total present + half day
If you want to get the total of total present including halfday, then create another column and type =Total Present+(Total Halfday /2)
thanks a lot that exactly what i need i made my form with some additional cell that show boys absents and attendance also girls for each day in the month
Great!
Dear sir, =countif(holidays,c$6) is not working in my system window 13
have you created the named range for holiday list?
Hello sir. I want to know what did you do when you change the month the data that you input will stay the same:ex Feb then you switch next month and I want to stay the data I input in the Feb month even that you switch in another month.
When you want to create your new sheet for any month, go to the Master sheet and select the month name then sheet for the new will be created. You can also see video no 96,101,1333,165
Hi I’m just curious Where did you get the code?
Hi, you can get the link of the file in the description box below the video.
hi sir. on minute 4.11 the video dindt show what you did on column R until T. what is the fomula coz on the video it suddenly missed that part.
Sorry for the late reply, I have just merged those cells, but you can also create a dropdown list for years like months.
What's the macro code?? We need that plz
Sir, I have one problem in my attendance sheet my present and absent is working on all months, how to fix it?
In this sheet, month sheet does not create automatically, You have to create with the help of Copy sheet button.
If you want to make it automatic, watch this series: th-cam.com/play/PLpsGVOobEOlDX4ug8tA9B1ZT5ZInl6dqz.html
Sir. Still I have doubt how do you get view code? and how can I get and see view code to paste in developer visual basic?
Blog link is given in the description box. Visit the the link you will get the code.
sorry unable to find the link to download the excel file. any one can help ?
Latest video on Attendance Sheet, Payroll Sheet and Pay Slip, All In One: th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
Hi good file but i want to change date from 22 jan to 21 Feb pls kindly let me know how to change
Watch this video, your problem will be solved.
Ok thanks sir😊
Welcome
I need to insert a coloum automatically before each Sunday to show the shifts of the employees. How can I do it. Please reply
how to create the 26th January to February 25th, how to selected the range in this type of sheet
Watch this video: th-cam.com/video/NUK8PXLfggo/w-d-xo.html
solution is given here.
dear Deepak how to select month cell in the formula?? through cursor or through button ??
Hi, I am not Deepak. I am Manoj, anyway
If the formula is about the start date you have select it with cursor.
@@SahaComputer with cursor its not working you have hold the cursor or cell??
Great video but my teams cycle run from 21 to 20 the next month is it possible to create something like
Watch this video carefully: th-cam.com/video/NUK8PXLfggo/w-d-xo.html
You will get the exact solution
For every month the attendance is calculated but how the casual leave, earned leave, medical leave, will be calculated for an year will be calculated
You can get idea to that from Video #167, Report Part
How do I retain data from previous month of CALENDAR
Watch the videos from link below:
th-cam.com/video/1tGSpi_4B_o/w-d-xo.html
th-cam.com/video/kJFLnviz8jc/w-d-xo.html
I have doubt what if employee worked on Sunday who I can calculate that sir
Visit our channel & watch the current video you will learn that too.
Dear Sir
Supose I want to prepare in advance the attendance of year 2021 how do I change the date from 2020 to 2021 thanks in advance
You can check out this video your all problem will be solved.
th-cam.com/video/NUK8PXLfggo/w-d-xo.html
I'm having an error with the IFERROR and IF statement together...
Type the formula.
Sir im facing problem when you data validation the formula didnt work
Do it as shown in video it will work. What problem are you facing.
Is it possible to get this sheet? Plz REPLY. Thank you
Hi, link is given in the description box.
The ms excel u r using is premium one or normal excel sheet? because I am using basic ms excel where it is in green color and it is not showing define name
can you tell me which version of excel you are using? so that I can help you.
Dear sir you make my work very easy before show this msg i am confused to fill attandance
if you to make more advanced attendance application. you can watch this videos:
th-cam.com/video/NUK8PXLfggo/w-d-xo.html
th-cam.com/video/1tGSpi_4B_o/w-d-xo.html
th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
th-cam.com/video/pa1OybTTe2s/w-d-xo.html
Nice Tutorial. But how to construct for an academic year ie between June and May
See video 96, 133,239
datevalue formula doesn't work with my worksheet. do you know why?
Download the file and check where you have error.
Great tutorial but i can't make the code work for copy sheet.
Pls help
Go to the description box and click the link under download I fill the form I will send you the file with code. Further if you have any problem, you can contact me.
Good job sir
i want to ask one thing just how can we change satuday also with sunday like a man using two days holiday how can he change this?
Here is the link, You will be master in highlighting Weekend Days
th-cam.com/play/PLpsGVOobEOlDjzMn5tsfIBaZI4nnDt8HO.html
The link for the 3rd version of the video:
th-cam.com/video/NUK8PXLfggo/w-d-xo.html
nice kindly guideme how i can set employee filter means to say if i want to see Employee "A"attandance for 12 months on sigle sheet then how can i set it
Latest video on Attendance Sheet, Payroll Sheet and Pay Slip, All In One: th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
hello,how to make a cell absolute, in the time period 7:33,i have tried F4 but it is not working
After selecting the cell press F4 (function key), You will $ symbol both letter & number of a cell reference.
Everything is good but when i Change the month the data doesn't change with it in the sheet please tell how to fix.
Check my new videos
Great stuff.wish i have means to join your channel
Welcome.
Its a great very useful work done by you.can i get this sheet for myself .
Link in the description box.
When I double click the start date and end date only then it is changing accordingly, please help
I have to see your file.
How do you do if the month start from the 26 to the next month 25
You can watch this video: th-cam.com/video/NUK8PXLfggo/w-d-xo.html
your problem will be solved. And I have started a new series of Attendance Management System You can also watch that and I am makingthe tutorial one by one.
th-cam.com/video/pDGmmIhqV2Y/w-d-xo.html
I just making 2022 attendance sheet but holidays is not shown any months after putting iferror formula...what to do
Please upgrade your office bro from 2019 your functions not working.. omg
Sir pls tell me the how to excel formula for present and off day calculation sir.
I can't understand you clearly, How you want Present & Off day calculation. Please write clearly.
I could not get the holiday formula correct. for eg : in this for march 6 & 7
How can I know where you made mistake, for check you can send me the file.
Dara fill krne k baad hum month change kar rhe hai to data same hai blank sheet ni aari
its not taking for highlighting of Sunday and holiday even after many trials
check the helper row formula and condtional formatting formula, may be there is something mistake.
Can u pls let me now the fourmal for keeping sat and Sunday are out of no of working days
Latest video on Attendance Sheet, Payroll Sheet and Pay Slip, All In One: th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
Hi sir how can we enter if there duty is Only half of the day?
You H as a legend for half-day, and you have to enter a new column for Total Present as
Total Present= AH7+(AJ7*0.5)
Watch the 3rd Version: th-cam.com/video/NUK8PXLfggo/w-d-xo.html
Next month we will upload a full-year attendance sheet in which you can change the month automatically.
For the time being, you can watch this video: th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
After merging from M to Q I did data validation but month list is not coming on sheet
Can you tell me how you done the data validation. by creating a named range for the month list or directly selecting the month list?
Sir that formula =IF(C6
How please write it clearly I'm also struggling
Latest Attendance Video: th-cam.com/video/1tGSpi_4B_o/w-d-xo.html
I have created well..but while changing february agaij same filling datas in january reflected how to solve
This is a single month attendance sheet you have watch other videos in our channel, Watch video 133 or 165 your problem will be solved.
@@SahaComputer ok sir thank u
Very useful video ..
Please share .. clear and copy Code ..
Latest video on Attendance Sheet, Payroll Sheet and Pay Slip, All In One: th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
hi sir thanks a lot the only problem is the clear sheet and copy sheet how to create the formula?
That is not a formula, it is VBA code. You can get the file, link is given in the description box.
Do you have any Excel sheet regarding vehicle sale management
No, I don't have. I will try to create one.
Attendance remain the same if you write present on any date of any month it remains the same of another month
In this template you have copy the master sheet with Copy sheet button then, change the month and do you your attendance. But if you wish to change the month and its attendance automatically, watch the video given below:
th-cam.com/video/pDGmmIhqV2Y/w-d-xo.html
th-cam.com/video/1tGSpi_4B_o/w-d-xo.html
th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
how to make exe file of my Excel Project without registration
Which registration, I didn't understand.
31:36 the copy sheet option didn’t work 😢 only the clear sheet
You can watch this video:
th-cam.com/video/msUV24L9x4M/w-d-xo.html
th-cam.com/video/Ct7Wd3BLb0s/w-d-xo.html
or download the file and work on it to learn. Request link is given in the description box.
Qhwn you put data for any employee in a month..those cell will show in whwn you change the month !! Can you please explain
I have replied this question several time please read the comment. If you need more automated attendances sheet please check our channel there several videos on it.
th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
th-cam.com/video/1tGSpi_4B_o/w-d-xo.html
th-cam.com/video/Do-coAkxMRQ/w-d-xo.html
th-cam.com/video/pDGmmIhqV2Y/w-d-xo.html
th-cam.com/video/6QvJ0t0L4_A/w-d-xo.html
th-cam.com/video/DiZJcdeEctM/w-d-xo.html
Could you please answer? When you change the month. previous months' employee details are still showing. ideally, I should get a blank sheet when I change month
At least the formulas should be zoomed. It cannot be seen clearly.
Latest video on Attendance Sheet, Payroll Sheet and Pay Slip, All In One: th-cam.com/video/QHr-4WsQo-E/w-d-xo.html
excellent but i have one doubt about how to create the Xcel sheet code for copying the sheet
Pause the video and type it.