# Timesheet Table: Calculating Hours Worked Over the Past Two Weeks, Displayed Weekly in Bullet Points

I’d like to share an effective method I developed using the GPTs model I created (new OpenAI service), which assisted me in creating formulas and API codes.

In my project, there’s a table named ‘Timesheet’ that includes typical fields you’d expect, such as Worker, Job, Start, End, Time Spent, etc. My goal was to devise a formula that, based on a selected worker, displays in bullet points the total hours worked in the past two weeks. The desired output resembles the image below:

With the GPT created and with all the Coda Formula page inserted as a knowledge (and after a few interactions), it gave me a formula that works. I don’t know if it is the best one, but it worked. See the result:

``````WithName(
Today() - Days(IsoWeekday(Today()) - 1),
startOfWeekCurrent,
WithName(
startOfWeekCurrent - Days(7),
startOfWeek1,
WithName(
startOfWeek1 - Days(7),
startOfWeek2,
List(
// Week 1
WithName(Round(
ToHours(
Timesheet
.Filter(
[Start time] >= startOfWeek2 AND
[Start time] < startOfWeek1 AND
[User WR].Contains(User())
)
.[Time Spent]
.Sum()
),2),
totalHoursWeek1,
"Two Weeks ago: " + totalHoursWeek1 + " hours" +
If(
totalHoursWeek1 > ToHours(Hours(40)),
"Two weeks ago: " + Round((totalHoursWeek1 - ToHours(Hours(40))),2) +
" hours overtime",
Character(10) + "Two Weeks ago: 0h overtime"
)
),
// Week 2
WithName(
Round(
ToHours(
Timesheet
.Filter(
[User WR].Contains(User()) AND
[Start time] >= startOfWeek1 AND
[Start time] < startOfWeekCurrent
)
.[Time Spent]
.Sum()
),2),
totalHoursWeek2,
"Last Week: " + totalHoursWeek2 + " hours" +
If(
totalHoursWeek2 > ToHours(Hours(40)),
"Last Week: " + Round((totalHoursWeek2 - ToHours(Hours(40))),2) +
"hours overtime",
Character(10) + "Last Week: 0 hours overtime"
)
),
// Current Week
WithName(
Round(
ToHours(
Timesheet
.Filter(
[User WR].Contains(User()) AND
[Start time] >= startOfWeekCurrent
)
.[Time Spent]
.Sum()
),2),
totalHoursWeekCurrent,
"Current Week: " + totalHoursWeekCurrent + " hours"
)
)
.BulletedList()
)
)
)
``````

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.