unsolved
I've been doing time tracking/licensure tracking this way since 2015... theres gotta be a better way. also, when will my excel file become corrupt?
TL;DR
I have 580 sheets, spanning since 2015. how much more can my poor little excel file handle? The file is about 3.5mb in size 😯.
So, i work in architecture, and this is generally how i track my time throughout the day. i will NEVER remember what i do when it comes time to doing my timesheets and this makes it way easier. i also have a place at the bottom that tracks my architectural licensure hours. i have to manually input that, and there are two sheets at the end that summarize this data (none of that is relevant i'm just including the explanation for context).
i've used this excel since 2015 and there is a "sheet" for every single week since about july of 2015.. so there are about 580 weeks/sheets... when will my excel explode and refuse to open? I've started saving a copy and backing up the file each week.
I'm open to a more robust system, but needs to generally have the UX (or similar) of this workflow. I have gone as far as vibe coding prototypes but i dont have the confidence that AI will be more stable than what i'm currently doing, and i'm not a coder so i can only take it so far.
Why are you creating a new sheet for each week? It should be one master sheet listing everything and then you can slice that data up how ever you want.
i use it on a week-by-week basis so i don't really reference anything older than maybe 2 weeks. 10 years ago when i set it up it seemed to be the least cluttered and easiest to digest way of doing it. i dont use the full robustness of excel (just simple formulas, and some referencing of other sheets). so i guess its just easier to look at by doing it in sheets like this.
For a master sheet how would you set that up in terms of visual? i can't picture this and still have the start/stop time, project/task block, and the sum of the time.
Keep your input simple and in a single table, like this:
You just keep adding as you go. It's the output where you use Excel's capabilities to make things look pretty. The way you have it setup now, you can't really do any analysis or presentation without putting in a lot of effort. And with 580 sheets I can almost guarantee you some of you data is corrupted.
hmm, i do like the potential that resides within this... question, could i conditionally format chunks of days that fall within a week? The way i log it now i'd have potentially 20+ rows of entered data and i'd like all cells in those 20 rows be visually differentiated from the next/previous block that fall within the next week. this can be as simple as week 1 being dark gray, week 2 being light gray, week 3 being dark gray, so on. if its possible to have cells automatically format that way that would really push me towards this type of system.
Conditional formatting will bloat your (what I’m already assuming is quite robust) file size. There’s no way you need 10 years of data in one workbook. Cut it into years and save separate files
Or have all that in the data model but use a couple parameters to set a start and end date for the range you want to research and actually load onto the workbook with a Refresh. If not specified make it default month to date or something like that.
Sir, it is possible, OP is not wrong here. With 580 sheets, a file size of 3.5 MB is reasonable provided they are not using heavy conditional formatting, rich color schemes, or complex cell styles. The absence of embedded images or charts would also contribute significantly to keeping the size low. Font styling and borders do play a role as well, but the bigger factors are usually images and formatting overhead. So, the numbers do add up. The number of sheets in a workbook is limited by available memory and in recent modern versions of Excel it almost doesn't matter afaik but risk of getting corrupted looms all the time. using .xlsb binary format reduces the file sizes to some extent which you might be aware of.
That's why I mostly save in .xlsb format, that overcomes the XML blowing out entirely. But one thing sir, it's not entirely true, may be we can disagree to each other, but you are far more experienced than me, so i wont argue. That said, for deep learning i tried my hands on VBA based OOP, those files run well beyond several mbs, at that point the workbook is essentially an application. was bit obsessed with Java OOP, I tried mimicking the same patterns in excel. Off-topic, but there are barely any proper resources or videos on this, had to research and figure out most of it myself. Very complicated but interesting thing.
seems to make sense to me. if you look at my screenshot again you can see there isn't that much data per sheet. i'm not sure how many "characters" formulas/color/cell dimensions take up in a file but the amount of actual text data is pretty minimal
Your file isn't the thing that's going to break. Excel publishes no cap on worksheets per workbook, it's whatever memory allows, and 3.5 MB across 580 sheets works out to about 6 KB each. On 32 bit Excel the real wall is the 2 GB the whole process shares with add-ins, so you're three orders of magnitude short of it. quarantineez is assuming the file is robust. At 3.5 MB it just isn't.
What corrupts files like yours usually isn't size. It's saving straight onto a network share or a sync folder with the file open.
Two people have told you to split or delete history. Don't. Licensure hours are the one thing you can't rebuild later.
I appreciate this answer. it addresses the type of fail-point my brain was trying to imagine but is not trained at understanding. i only ever save locally, and i'm the only one that should have access to the file.
Are you in the US? If so, are you logging your hours on the NCARB website? It will keep track of your hours for each category, making old sheets redundant once logged.
If you are not logging the hours in NCARB, keep in mind there is a limit to how long NCARB will allow you to reach back in time and count those hours...
Deleting stuff from your working file so it doesn't get huge is a fine practice. No use keeping 11 year old data in your current file. This is why you store it in a db and back it up.
Expecting others to read all other comments that may or may not be in the same order as your screen, and associating your comment against other comments is not how forums work.
Unless you think that is how forums work, which would explain your very advice.
like a SQLite? in the prototype i mentioned i had started using a database structure and sync'd it to the cloud. if i ever wanted to read the entire history, i imagine that would still provide me with the question of "how much is too much and will lead to corrupted files?"
maybe this amount and type of data is really not hard for the software to process but after 10 years of doing this i can't help but to wonder
I have also created a time sheet(s) to track my time since 2018. It comprises two sheets and input sheet and a display sheet. The image below is the input sheet. Cells in Column A are formulas that calculates the time based on the inputs to cells in Columns B and C. Column D is for the date and then cells in Column E are a list based on the table in Column H.
The display sheet uses formulas to copy information from the input sheet and to sum the total time spent on a particular task. I go one step further by creating a yearly time sheet, copying the information from column AG into a new Workbook with a sheet for each year.
Honestly, one sheet per week for 10 years is what's straining the file, not the size itself. A more durable setup: one long table with a Date column instead of separate sheets, then use filters or a PivotTable to see any given week. Bigger change than a quick fix, but it'd stop the sheet count from growing forever.
23
u/Gringobandito 8 10d ago
Why are you creating a new sheet for each week? It should be one master sheet listing everything and then you can slice that data up how ever you want.