I'm in the process of developing a spreadsheet as a work log. It now has about 40 columns. Is that too many columns for a spreadsheet? All are needed, but I just never saw a spreadsheet with that many columns. I'll be using this to create pivot table reports.



Thank you,
Karen

Karen
It is difficult to answer if that is too many columns. Have you considered multiple tables that are normalized and then bringing each into the Data Model and creating pivot tables in Power Pivot? If you go this route, then make sure that you have common fields in the tables that need to be linked.

__________________
Alan עַם יִשְׂרָאֵל חַ Using O365 v2505

For simplest app, you need a table like:
Date, Employee, StartTime, EndTime, EntryType

To register the worktime for any employee, you add for this employee a row for every entry type you are registering for employee on this date. Like:
01.12.2024 Employee1 08:00 11:30 worktime
01.12.2024 Employee1 11:30 12:00 lunchtime
01.12.2024 Employee1 12:00 16:30 worktime
The worktime for Employee1 at 01.12.2024 is calculated as sum of worktime entries. The lunchtime is not included into worktime.

You need a separate table, where you define all possible EntryTypes, like:
worktime, lunch breake, state holiday, annual leave, sick leave, etc. In this table, you also determine for every entry type, does it add to worktime calculation, and when yes, does it add as positive or negative value (and whatever other additional info you need)

Now you can create any number of report sheets, like daily, weekly, monthly or yearly reports. At top of report sheet, you select a period for report (using Data Validation list), and any other filter conditions in case there is a need for them, and below conditions the report table is calculated based on your data entry and entry types tables, according the conditions you set for this report.

NB! In case the worktime can start and end on different dates (over midnight), you have to enter start and end times as datetimes (like 01.12.2024 08:00)!

NB! In report sheets, you have to format the cells with worktime totals (which surely may be >24 hours) like "[h]:mm"!

Please post a small sample sheet ( no pics please) so we can help without having to guess. Thx

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Has this thread been solved?

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Here is an example how i'd do this.

Attached Files

WorktimeExample.xlsx (52.5 KB, 3 views)