Forum Discussion
Using Excel Spreadsheet to log calls by month, where each tab is a day of the month (M-F)
Is it possible to auto fill the tabs to be the days (ex: Feb 1, Feb 2, Feb 3), and each tab to have a cell with the same date formatted (ex: 1-Feb-24, 2-Feb-24, 3-Feb-24)? Is it possible to further define to M-F only?
Thank you!
- To automate insert sheets, better to use VBA, here I have given code for inserting March 2024,
Sub InsertMarch2024Tabs()
' Set the desired month and year
Const DesiredMonth As Integer = 3
Const DesiredYear As Integer = 2024
' Get the start date of the desired month
Dim StartDate As Date
StartDate = DateSerial(DesiredYear, DesiredMonth, 1)
' Determine the number of days in the desired month
Dim DaysInMonth As Integer
DaysInMonth = Day(DateSerial(DesiredYear, DesiredMonth + 1, 0))
' Loop through each day of the month
Dim i As Integer
For i = 1 To DaysInMonth
' Add a new worksheet
Sheets.Add After:=Sheets(Sheets.Count)
' Name the worksheet with the current date in "dd-mmm-yyyy" format
ActiveSheet.Name = Format(StartDate + i - 1, "dd-mmm-yyyy")
Next i
End Sub
Before that, please make sure that proper changes for specific months to insert sheets in worksheet.
Thanks Let's back up a bit.
Having a workbook with separate tabs for every day sounds like a bad design right off the bat. It works for the human being in us--we like to see each day on its own fresh sheet, and that's how we'd do it if we were keeping a log of calls on paper. But you're wanting to use the computer in this case, and what you describe actually interferes with some of what Excel might be able to do (e.g., consolidating entries by category, summarizing numbers of calls by category). If you're planning on doing that, or anything like it, it would make more sense to have all calls logged on a single sheet, with the date noted in the first column. From that single database it would be very easy to extract a given day's calls, but also to do some of those summary reports I allude to with my question.
If you're not planning on summary reports along those lines, if this is "just a log," why are you using Excel in the first place? Because it has neat rows and columns? You might find Word (the table format in Word) to be more flexible, and you could start a new day with a new page or a new document.....
mathetes thank you for your response. We don't create reports. The calls can be up to 100 in a day and that's why we use a tab for each day. It wasn't my design, I am just trying to improve upon it for ease of use. I will look into both of your suggestions, thank you!
We don't create reports.
You may not create summary reports, but surely there is SOME use of the logs...some need to search for "How did we handle that call?" Why do you log them at all, if there's no output?
The calls can be up to 100 in a day and that's why we use a tab for each day.
100 a day would mean 3,000 in a month, 36,000 for a year. Those are not unmanageable numbers in a single spreadsheet, and it would be far easier to search for a single call using the filter capability (or the FILTER function), either one of which could limit the search to a given day if you know what the day is, and incorporate other criteria to facilitate finding the relevant logged entry.
It wasn't my design, I am just trying to improve upon it for ease of use.
It's precisely here--at the "use"--that my questions are aimed: what is that "use"? There's the input end of use, logging the calls. But what's the nature of the use at the output end of things? How can it be designed to facilitate that? And to take optimum advantage of Excel's abilities to search and find, by date, topic, person who handled the call, etc. etc.
