Recording Working Time Template - Weekly Hours instead of Daily hours

[HeleneRousseau]

Hi,

I would like to use the “Recording Working Time” template but would like to calculate employee working hours by week rather than day. I would also like to add up the full amount of time required for that month based on the amount of working days in the month and for employees to see how much time they still need to work for the month based on this. Is there an easy way to adjust this template for this purpose?

Thanks,
Helene

[cdb]

There is no central setting to switch from day to hour measurement.
But it should be not so difficult to update the template accordingly. I would even recommend to it by yourself:

• Reason 1: it is a template, we expect, that the users adapt it to their needs
• Reason 2: you should be familiar with your bases. If you don’t know how they work, you are completely dependent from it or somebody.

I am sure, you will achieve this change by yourself.

[HeleneRousseau]

Okay so this is what I have managed. It doesn’t seem like the most efficient way to get to it so would still be interested in knowing if there is.

I have used the link formula RollUp function to add up all the worked hours calculated in the time tracking table for a specific month. E.g February Rollup, March Rollup and so on

I then created an “Hours Remaining for February” column with a formula: **(20 * {Mandatory working hours (per day)}) - {February Rollup}** and then do it for every month Feb, Mar, April and so on.

It works and is effective, but I am curious nonetheless if there was another way.

[bennhatton]

Hi @HeleneRousseau,
Great job! Here are few thoughts I’d like to share with you:

• In order to limit the number of columns to create, you can eventually integrate the rollup calculation directly inside your Hours Remaining columns (see bellow). However, it is a very readable indicator, so it may be worth keeping it.
• Depending on how long you want to use your tool, you may be confronted with the hard-coded number of working days in your formulas (each month of February does not necessarily have 20 working days). That’s why I wrote the following function (with the rollup mentioned above). It automatically calculates the number of working days between the 1st of February of the current year and the last day of the same month. You’ll notice that the three 2s in the formula correspond to the month of February. The formula can therefore easily be duplicated for the other months. If you want to keep the Rollup function, you can replace the second part rollup("Events","Worked time","sum","{Month}=2") by {February Rollup}

{Mandatory working hours (per day)}*networkdays(date(year(today()),2,1),eomonth(date(year(today()),2,1),0))-rollup("Events","Worked time","sum","{Month}=2")

• depending on the particular cases you’ll have to deal with, you might want to use something more complex like a python script to calculate the theoretical mandatory working hours per month. I did this once to deal with both particular weekmasks due to part-time work and public holidays (it can become quite complex to determine which public holidays should be taken into account for which employees depending on their working days). It also helped me to easily handle the public holidays with variable dates that we have in France (Easter Monday, etc.)

Bests,
Benjamin

---

I love spending time gathering information and explaining solutions to help you solve your problems. I spend… quite a bit of time on it .If you feel grateful for the help I’ve given you, please don’t hesitate to support me. I am also available for paid services (scripts, database architecture, custom development, etc.).

[HeleneRousseau]

Hi Ben,

Thank you so very much for your help and great suggestions as always!

Two things that I’m wondering

1. If I use this formula and lets say it is now 2026. Will this then change all my data for 2025 because the formula is looking up what is current for this year? This might be problematic for historical data?
2. I was thinking it might be best to do a new base for each year as to keep everything fairly clean and not too clunky. I think the only thing that would need to be transferred to the new year would be leave that is carried over. Does that make sense to you?

Yes those complexities can be quite something to figure out! Our workplace works on flexi-time so it also adds other levels to think about. One way we thought to work around the public holidays is to allocate x amount of hours to a public holiday that falls in a work week depending on how many hours the staff member works for the month. I haven’t thought how to possibly automatically add this in but at the moment I think we will give the information to staff and when logging hours they will claim X amount for public day hours.

[bennhatton]

Hi Helene, and thank you for the compliment

1. You’re completely right: I used year(today()) in my formula so on 31 December the formula will still return 2025, whereas on the following day (1 January) it will return 2026. This could really be a problem… It seemed like a good idea when I wrote it (it was a bit too late ), but it’s probably not, and that’s not how I implemented the system in my former company neither: I created on column by year (we only used an annual working hours count).

2. About your idea of creating a new base for each year, it really depends on what you created this base for and how do you want to use it. In my former company, we used this base both for time tracking and for assessing the time on each project. If a project runs on several years, it’s really easier to keep all the declared hours in the same base. Depending on how the staff members submit their data (directly inside SeaTable or using a form or an app), creating a new base can create some difficulties as the URLs will change.

   You could also create only a new table in the same base for each year (this could even be done with a script launched automatically with n8n or a scheduled automation: duplicating the current year table and modifying the formula for each month column) but your time tracking table will continue to grow, so you’ll have to clean it from time to time.

About your last paragraph, I’m afraid I didn’t completely understand your point but I can at least share my experience of giving information to staff: my colleagues forgot pretty often if/what they should enter for each case (paid leave, unpaid leave, public holidays, sick leave, etc.) so I ended up adding a single select column for leaves/holidays and a formula column to calculate the proper amout…

Bests,
Benjamin

---

I love spending time gathering information and explaining solutions to help you solve your problems. I spend… quite a bit of time on it .If you feel grateful for the help I’ve given you, please don’t hesitate to support me. I am also available for paid services (scripts, database architecture, custom development, etc.).