Forum Discussion
Excel Help - Student Attendance
And here you go.
Note the comments on the raw data sheet first, then the summary sheet. By the way, do you have a subscription to Microsoft 365, or the Excel 2021 version or newer. Some of the functionality of this workbook does depend on having one of those current versions.
Two suggestions:
- Keep separate workbooks for each of your several classes.
- Eliminate from the workbook's raw data sheet all but columns A through I (those aren't used in the summary calculations).
See the attached to see how the latter operates, and try copying and pasting in just those first 9 columns from your incoming data sheets,
I have tried a couple of times now and have done a speed test, and have a great connection & it's not wanting to import more data (the longest I've waited is about 10 minutes). I still don't have a ton imported - only about 1/8 of what I need at this point in the year, and this has to last me through January, as far as data imported. It may be just too much data to handle.
- We could limit the columns imported to just the ones used in the summary, but that’s its own complication. Let’s see when you’ve got a good connection, etc.
Or do a different workbook for each class. - Awesome, it works if I upload the csv file into 365 and convert. However I am only on class 2 of five and have only imported 4 days of data and it's starting to get reeeeeally slow - which was my only fear, that this much data may be too much! I will try again in the morning just in case it's my internet...but it has been getting gradually slower with each data set - this new one has been "working on it" pasting for about 5 minutes.
I've attached a three week version, 5-day weeks only, and you should always make sure to enter a Monday date. I skip over the Saturday and Sunday in the two weekends in the middle of this span of days.
Have you tested adding a full data set? Even a full weeks worth of data?
The only odd thing I notice in your video is what I've captured in this screen grab
It's odd that the date and time are appearing as strings of hashmarks for that one column, but not the adjacent one. Making me wonder if somehow the data are not in consistent formats. Are you copying this from a CSV file? What you sent me, I believe, were always XLS or XLSX files. If it's a CSV file, try importing it to the Excel format first before copying.
- I LOVE this version! But - the same thing with the dashes is happening when I add new data - here is a video showing it, I am on 365 and the school week is typically 5 days; a three week version is probably better, if it's not a pain to add. If it is, the two week version is fine! https://somup.com/c0QiF5AEwj
I've slightly modified the attached. On the "front page" you are asked to enter a date. That date then generates two weeks (a Monday to the Friday of the following week) of data assuming you have entered all the raw data for those two weeks. This "trial data base" is of course incomplete. But you can see how simply changing the start date will change the columns to correspond.
A couple questions for you: I asked whether the "school week" in your case is five days or something else. As this stands, it assumes you'll always want to start with a Monday and go for two weeks. If you want three weeks, let me know, along with whether by week you mean 5 or 7 days; I'll also modify the layout so it's clear where weeks begin and end.
If you still have the raw data for some of the dates not currently in the database, add it in to test the functionality.
JUST BE SURE YOU'RE WORKING WITH EXCEL 365
- Yes - 5 periods, all with different students. Each period has the same kids each day unless we get new ones added or disappear if kids transfer back to a regular school (or if they are absent their name won't show). So 5 attendances each day (was just going to put them all on one sheet). I need to see participation in my class for the school year in case a parents asks if they were in class on certain days and for special ed support (they need to keep track of how many days students are coming and if they are actually participating or if they just logged in and walked away - our new system does not have a way of taking attendance other than the reports generated each period). I will need to have every day of school on there so I can see trends in participating/not participating day to day.
I do like the original set up, as the data was all on the same page, with the rest hiding in the back. I'd need to see 2-3 weeks of classes at a time at least. Sorry, I was hoping this was somewhat simple - you are patient man & I appreciate you. You are causing my head to spin a bit. 5 periods for classes each day? Different cohorts of students, or different subject matters or continuation of subject matter with the same cohort?
Get Microsoft 365 and stay with it, first of all. On your home computer too.
The dashes will appear when you add new datasets, until the formulas get copied down. I can't explain why you say you can't copy the formulas down.
A suggestion. Do you need to be showing on the summary page all dates forever and ever, amen?
Could a weekly display work? Such that we could have any set of seven days (or five days, if that's a school week)? You could keep ALL the data for all the days as a single database, but it would then be possible to have that front page, summary page, set up so that you just change the first day to the start date you want, and all the rest happens.
Or it could be monthly.
If something like that would work, such that all you had to do was copy the new datasets and paste them into the data table, I could set up that "front page" so that all you needed to do would be to set the first date of the period to report.
- Yes, it is school provided. I have an even older version on my personal computer from college. I did find out we have 365 and was playing around with it just now. It did seem to work the first time, I pasted something in, but as soon as I added another data set - an old one (date wise - the date was already in the file functioning properly) everything turned to dashes again. I'm not sure if this will be an issue or not. I have 5 periods of classes for this, so will add 5 different sets of data for each day, and usually only work on attendance once a week because it takes so much time out of my day. I am not sure why it is turning to dashes after another set of data, with the first set working multiple times. I did try to re-copy the formulas down and they don't repopulate.
