Excel Magic Trick 496: Attendance Sheet with Freeze Pane, IF & SUM functions, Custom Date Formatting
ฝัง
- เผยแพร่เมื่อ 4 ก.พ. 2010
- Download Files:
people.highline.edu/mgirvin/Y...
See how to create a basic Attendance Sheet for a classroom in Excel. See THESE TRICKS:
1)See how to Freeze Panes for Large Spreadsheet
2)Ctrl + 1 to open Format Cells Dialog box
3)Create A Custom Date Number format: ddd, m/d/y
4)Copy Dates and use Smart Tag to fill weekdays only
5)Ctrl + Shift + Arrow selection trick
6)IF function to show blank when no student name is showing
7)SUM function to add attendance score.
Vhmrz18 from TH-cam - วิทยาศาสตร์และเทคโนโลยี
After watching this video, I realize that preparing attendance sheet is no longer difficult. Thank you for uploading this video.
You are welcome!
@TheTeacherJulio , you are welcome!
Thank you sir for unwinding the secret of weekdays. I had no idea that we could use such kind of dates.
I am glad that this helps!
You are welcome!
@msashikin85 , I do not know how to do that because if you highlight all sheets many features such as Freeze Pane, Conditional Formatting, etc. do not work. I would just do it on each sheet but use the keyboard (Excel 2007 and 2010): Alt + W + F + F
thank u so much bless u and your not using big words so no complication btw im subscribing
Glad the video helps!
Hi thanks.
I did as you said for the blanks instead of a zero. But the the zero keeps coming up. Why is that?
Awesome! clear, simple and educational.
Thank you.
This really helped my staff create an attendance sheet. It's great, thanks for the help!
@Facebookawya , You are welcome!
Thank you! what do you do if the class is only on monday's? just enter the dates in manually? or is there an easier way?
You are a great teacher. How can I create an automated attendance sheet for a shift that is on 4 on 4 off for a period of 1 month.
I am glad that you lik eit!
you're a legend, very helpful. Thanks from Iraq!
Thank you. But can these work without the key shortcuts? I prefer to key in manually.
2010
But everything should work in almost any version.
Extremely helpful. Thank you!
yep its really help full. but how to pass the message when after get the different of two times based on it, using if condition.?
Thanks for making it simple and clear...
You are welcome!
I was searching online found your videos after spending a lot time searching, found your videos very helpful and easy to follow. I do have a question though; I wanted to make a weekly schedule for my workers to go to work at a different location, they will be split into teams of 2 each with a task. How do I make a schedule where I can use all my workers and not overlap workers? Does Excel do this? I do not need to keep record of hours worked.
Thanks, its very useful for me
excellent tutorial ......... specially freeze the columns, formula, auto formula..... this tutorial is verrrrrrrrrrrrrrrrrry helpful. thanks
DEAR SAN ,
THIS IS VERY HELP TO NEW PARSON TO LEARN COMPUTER SHORTCUTS THANKS
If I want to print out a sign-in sheet with many rows, which means it will require multiple pages to be printed on, is there a way I can format the spreadsheet so that it adds the column descriptions (e.g. name, signature, time-in etc.) to each new page? Thanks!
Thanks for you tube its is useful to me
Excellent!
Your videos have been very helpful and I have learnt a lot. Thank you.
I have created an attendance register and report template. I have posted that video as a response. It will be great to get your feedback. Thanks again for all the knowledge shared.
So helpful, cheers!
Thank you so much it was very insightful, But i have a question. What if i have to use X for Absent and O for present and at the total they want to have for example 14/14 and one kid is absent so he will be 13/14 thirteen out of fourteen?
Hey ExcellsFun, How do I do an attendance sheet where I can add up the row with "Late" as my column, but I have the symbol "L15" or "L20" and add the cells in the row with the total number. L15 means 15 minutes late. I am having a hard time figuring this out. Would you be able to tell what function or formula to use if not a video. Thanks.
send me the spread sheet that you have made so far, to look at it.
Hi. Quick Question. What version of Excel are you using in this video?
hello! i have a problem, where i live the weekdays are from sunday to thursday, what should i do?
When I drag the date, the year changes instead of the days?! And when I custom changed the 'ddd' Monday did not appear?! Help!
I will teach you,my email is rkkayemkhani1@gmail.com
Were the control key and enter?
Thanks u lot sir for this videos.
Sir I have a question is there any logical formula that move the Total column automatically if total day are 29(Feb),30(April),31 so that no column left blank.
thanks to make me perfect
its a big help for me,
how can i download the video tutorials?
micrasoft excel
hey i cant find how to download the attendance sheet
can u send me the link
hello, i want to create a table that only covers wed, fri, sat and sunday. how to i do this?
My question
i have made the sheet now i also want it to multiply per day salary with total no. of days present?
Thanks so much it helped me alot
when i used angry rabbit , student 1 does not changed into student 2 . it will remain the same. plz help
harshita pant 👀
thank u for this.. it is very helpful
I am only learning now how to use Excel coz i've never needed it before (up until now) so I've been using an auto-fill Excel attendance sheet that I found online... I was wondering how can I setup the days to be for every other day? The attendance sheet that i've been using is for an entire month, and there is a drop down menu to change the month and the year and it will auto fill the days and dates by itself (I hope im not complicating things :D )
Now i was trying to put two month on the same sheet and failed (dont know if its possible) and been trying to specify the days for example only Mondays and Wednesdays... and I cant seem to figure out how :(
Help!
I will help you,my email is. rkkayemkhani1@gmail.com
NICE but how to "transpose" dates and numbers from horizontal to vertical and keep student names vertical too..????????
very useful, thank you so much..
hye, I love your video, relly helpful and appreciate all your hardwork.. while, actually i have some problem to asking you, will be good if you can solve my problem..
I'm working as Human Resource executive and i always do and calculate employees annual leave and attendance by using excel, but i have a problem which when I do a calculation by using a simple formula I what it come out at summary sheet for all employees total of their (leave entitlement, leave balance, leave taken and etc..) I create 1 sheet for 1 employee (for a year attandance record) and 1 sheet for total summary for all employess..
my question is How do the calculation that i update at other sheet (employees record, example leave taken) will come out at my summary sheet for all difference employee as a total .. really need your help..
Awesome Video! I am having a problem with my attendance sheet, and have looked everywhere for a simple solution, no luck, my sheet is as follows a2-LastName b2-FirstName .....f2 (dates) -2/13/2017 g2- 2/14/2017 and so forth for years into the future, f3- would be blank if no attendance occurrence, .5 for tardy and 1 for absent and so forth g3 and on for continuing dates. I would like to have a column for auto fill/sum of 365 rolling calendar, today 1/9/2018, if there was a .5 or 1 for 1/9/2017 it would fall off the calculation. Hope that makes sense.
Is there a way to find missing student sequence (contains text and number) in this video? I watched the duo video of MrExcel and you but couldnt make it work because in that video, the example is number format that is missing. For example, student7 and studen14 are missing in column A, can you please help find those 2? I follow the two methods of yours and mrexcel but no luck because the format contains both text and number. Thank you!
Clear and helpful!
My year changes when i copy the date
I need to knowing how to create a time sheet. in my work I have to report twice a month. I want something to copy every month of the year. I use to type all the week's days for every payroll, twice a week. I want something just to copy. If you have something please let me know it. Thanks.
nice information
thank you
I know this is really old, but.... I need a Roster that only contains Wednesdays, Sunday AM, and Sunday PM.... is that even doable?
i dont know what EMT494-501.xls i dont know where is it !! pls help me!
Sir could you please do the same in excel 2007- Attendance Sheet with Freeze Pane, IF & SUM functions, Custom Date Formatting
Question: lets say that for every absent i want to add an "a" and at the end of each row i want to have the total of "a"s for each student
Thank you so much.
EASY TO USE.......THANKS
How make to do staft absent
thank mate!
can we do this with phpmyadmin ??
hi i am jack walker from lincolnshire fire and rescue and have just been apointed the new store manager and have the basic excell spred sheat and would ask or see if you have done something be for os this like fit sizes and equipment
+Jack walker talk to me . i can help you
This is very use full.
use the countif() formula. =countif(range,"a")
NICE +helpful
how tow remove this blu lines
This guy sounds like David Schwimmer!
put it the correct date for the first class and use the autofill, but adjust it so it increments by 7 and use date format
sir good afternoon yeh Jo attendence ms excel me dikha raha hai yeh jara full 100%me format kr dijiye
people.highline.edu/mgirvin/excelisfun.htm
How to Use the Digital Attendance Device in Excel Sheet ?
it is awesome video
hi, i'm ziela i would like to ask you few question regarding the excel sheet that i have overight, yet i don't know how to edit from the excel sheet. can i have your email?
Hello, if you need help, please email me.
Plz upload the excel file
tnx :))
Excellent mate. But do you know how to make color zones, I mean if the student hasn't absent will have a green background, or if the student has a lot of absence days, the background of that student will be painted on red and so on. I need that to be automatically how?Thanks in advance + like + subscribe
lenovo price in ksa
Trying to download and it says the file is corrupt. Help
It is still there and working, it is just the older file format ".xls".
nice
00:40 studnet
but it was clearly expained
Student* not Studnet
You are welcome!
You are welcome!