Excel Magic Trick 496: Attendance Sheet with Freeze Pane, IF & SUM functions, Custom Date Formatting
Vložit
- čas přidán 4. 02. 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 CZcams - Věda a technologie
people.highline.edu/mgirvin/excelisfun.htm
I am glad that this helps!
Thank you sir for unwinding the secret of weekdays. I had no idea that we could use such kind of dates.
This really helped my staff create an attendance sheet. It's great, thanks for the help!
Awesome! clear, simple and educational.
Thank you.
excellent tutorial ......... specially freeze the columns, formula, auto formula..... this tutorial is verrrrrrrrrrrrrrrrrry helpful. thanks
you're a legend, very helpful. Thanks from Iraq!
Extremely helpful. Thank you!
thank u so much bless u and your not using big words so no complication btw im subscribing
Glad the video helps!
DEAR SAN ,
THIS IS VERY HELP TO NEW PARSON TO LEARN COMPUTER SHORTCUTS THANKS
I am glad that you lik eit!
@TheTeacherJulio , you are welcome!
After watching this video, I realize that preparing attendance sheet is no longer difficult. Thank you for uploading this video.
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
So helpful, cheers!
You are welcome!
Thanks for you tube its is useful to me
Excellent!
Thanks, its very useful for me
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.
Thanks for making it simple and clear...
You are welcome!
very useful, thank you so much..
Hi thanks.
I did as you said for the blanks instead of a zero. But the the zero keeps coming up. Why is that?
Thanks so much it helped me alot
EASY TO USE.......THANKS
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.
thanks to make me perfect
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.
Thank you. But can these work without the key shortcuts? I prefer to key in manually.
Thank you so much.
nice information
Clear and helpful!
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!
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?
NICE +helpful
it is awesome video
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.?
thank you
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.
thank mate!
This is very use full.
2010
But everything should work in almost any version.
@Facebookawya , You are welcome!
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..
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.
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?
My question
i have made the sheet now i also want it to multiply per day salary with total no. of days present?
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?
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
hey i cant find how to download the attendance sheet
can u send me the link
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.
hello, i want to create a table that only covers wed, fri, sat and sunday. how to i do this?
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!
its a big help for me,
how can i download the video tutorials?
micrasoft excel
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.
Were the control key and enter?
I know this is really old, but.... I need a Roster that only contains Wednesdays, Sunday AM, and Sunday PM.... is that even doable?
NICE but how to "transpose" dates and numbers from horizontal to vertical and keep student names vertical too..????????
tnx :))
when i used angry rabbit , student 1 does not changed into student 2 . it will remain the same. plz help
harshita pant 👀
nice
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
This guy sounds like David Schwimmer!
can we do this with phpmyadmin ??
Sir could you please do the same in excel 2007- Attendance Sheet with Freeze Pane, IF & SUM functions, Custom Date Formatting
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
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
i dont know what EMT494-501.xls i dont know where is it !! pls help me!
How make to do staft absent
use the countif() formula. =countif(range,"a")
My year changes when i copy the date
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
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
how tow remove this blu lines
Plz upload the excel file
sir good afternoon yeh Jo attendence ms excel me dikha raha hai yeh jara full 100%me format kr dijiye
lenovo price in ksa
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.
How to Use the Digital Attendance Device in Excel Sheet ?
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".
but it was clearly expained
00:40 studnet
Student* not Studnet
You are welcome!
You are welcome!