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

[Elber_Domingos1]

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()
    )
  )
)