I am dealing with an attendance sheet. And I'm having a problem with how to change the total number of students attended. And since I'm dealing with dates, I've tried extracting the DAYS where a certain student left school. Now the problem is the total number of students attended does not conform with the supposed actual students present since a student left.
To further explain the scenario, I have attached a sample sheet below.
Thank you
sample attendance sheet.xlsx
If you can change your table as in the attached workbook, the formulas for total attendance give you the right answer.
Pierre Leclerc
_______________________________________________________
If you like the help you got,
Click on the STAR "Add reputation" icon at the bottom.
In the attached workbook, I'm using your table but I changed the formula
to get the date a student left the school from column G
and I set the formula for the total attendance.
Hope this helps
Hello p24leclerc!!!
Thanks for your reply!
he formula you used to extract the day works only if the Remarks section used the format YYYY/MM/DD.
how do we revise it if the format was DD/MM/YYYY
Please Login or Register to view this content.
Last edited by wedzmer; 03-16-2015 at 10:15 PM.
I understand that changing the Remarks section was the best idea to extract it.
But since I can't do so in the original sheet, I have to work with the Text "Left School On: DD/MM/YYYY".
Try this formula:
The DateValue function is dependent of your system date configuration. This formula does not work on my computer but my system is set up for a french date format. It would certainly work on yours.Please Login or Register to view this content.
Regards
THANKS P24LECLERC!
That really works! Can I ask one more thing? considering that the sample sheet was originally a question regarding a case where a student left school, what if the case would be the opposite? I mean, the formula was based on a scenario where the student was originally part of the class and now it dropped out or left school. What if I have a student who was a late enrollee, probably a case where he might have enrolled on that month (june) or a month after (july) and so on. How do I add this student on the list for that month starting on that day he was enrolled. The second consideration would be since for example he was a late enrollee on June, so by July, on his "REMARKS" row, there would be a part there that states "LATE ENROLLEE: 2014/07/05". So he should be counted as part of the class then on.
I was thinking about it since the formula was only extracting the "day" value without considering the month. So probably if I use the same formula altering it a bit, it would then deduct still from July 04-05 or vice versa.
I also noticed that the formula doesn't work on the row "total" when the month moves further for example when it's checking attendance for July. Since the remarks says "LEFT ON JUNE...." the deduction should start from the first day from the total number of cases.Originally Posted by p24leclerc
Try this formula:
The DateValue function is dependent of your system date configuration. This formula does not work on my computer but my system is set up for a french date format. It would certainly work on yours.Please Login or Register to view this content.
Regards
could you attach a workbook with more than a month worth of data so I can take a look at the formula?
thanks a lot!
Here's a revised sample template of the attendance sheet I'm working on.
Basically, the June was the same template we used above. And then I made another sheet for July which is a bit complicated because it's the REMARKS SECTION that makes it so difficult to handle. Anyway, I really hope and pray you could help me out.
sample attendance sheet REVISED.xlsx
see formulas in the attached workbook.
Results differ from you supposedly right numbers but I don't agree with those numbers.
Thanks p24leclerc!
That worked! Haha! My mistake..
But I noticed that the formula you used for extracting the date
does not work if G5's text is written as: "T/I on 2014/06/05" because there are sheets given to me where the phrase "transferred in" has been abbreviated to "T/I".Please Login or Register to view this content.
how do we change the formula for that?
In order for this kind of workbook to work well you ought to ask for STRICT input in this field.
Remember that the Total formulas also use the words "Transferred" and/or "Left" and/or "Enrollee" to count students.
For those formulas to work, all users have to use those exact terms or it won't work.
I would suggest to change your sheet so that you have 2 columns instead of one for the comments.
One would be the date and the other would use DATA VALIDATION to restrict entries made by users. But it seems you can't change it, can you?
If your date in remarks column is alway the last 10 strings
1) I use this to get the date array from column G, where blank cells are considered as a very far date
DATEVALUE(IF($G$4:$G$12="","2020/12/31",RIGHT($G$4:$G$12,10)))
2) And I use this to get the date array in B column (Jul-7), where blank cells are considered as current date, not blank (i.e contains "A") as very far date (10^10: big number)
IF(B$4:B$12="",B$2,10^10)
3) Then, if the current date is smaller than date from G column, count 1
IF(B$4:B$12="",B$2,10^10)<DATEVALUE(IF($G$4:$G$12="","2020/12/31",RIGHT($G$4:$G$12,10)))
(gives TRUE/FALSE equals 1/0)
4) SUM all 1's:
Place in B14:
=SUMPRODUCT(--(IF(B$4:B$12="",B$2,10^10)<DATEVALUE(IF($G$4:$G$12="","2020/12/31",RIGHT($G$4:$G$12,10)))))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Drag accross.
That is my idea of processing. In case the date in G column is not 10 last strings, We will use MID() with 10-string to get it.
Hope it helps.
Quang PT
That would be a lot more convenient on my part if the remarks section was divided into two columns but unfortunately i can't because these sheets are part of a template teachers follow and some lazy ones only abbreviate the phrase "transferred in" with "T/I". The cells and templates are fix, the data entries vary depending on the class. That's why I could only work with the specific rows and columns as was presented on the sample attendance sheets. In fact, there's also "T/O" for transferred-out, "LE" for late enrollee, "NLS" for No Longer in School and that one I mentioned, "T/I" for transferred-in.
I'm really having a hard time since there are around 64 different classes where I should make a seperate copy which tabulates all the attendance of all students in school along with a statistical report for each grade level. Being a teacher in a public school here in our country really is hard.
I'm also happy that a member here in the forum who was the pioneer for this project helped me, big thanks to Winon and to you as well for your help.
I hope we could find a fix for this.
Much gratitude!
Thanks for that bebo 021999...
But I'm having a hard time understanding your codes.
Sorry, I'm really a noob with these functions you were using.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks