Sum total of a duration for a given period

Hello,

i am working on a timesheet app. I got everything to work but my employees are asking me if they can have the sum of their working hours for the last three weeks displayed. I am really clueless of how i can pull this one.

Solved Solved
0 16 3,120
1 ACCEPTED SOLUTION

For last week, try:

SUM(
  SELECT(
    table[Duration],
    AND(
      (USEREMAIL() = [email]),
      ([date] >= (TODAY() - WEEKDAY(TODAY()) - 7)),
      ([date] < (TODAY() - WEEKDAY(TODAY())))
    )
  )
)

For the week before that:

SUM(
  SELECT(
    table[Duration],
    AND(
      (USEREMAIL() = [email]),
      ([date] >= (TODAY() - WEEKDAY(TODAY()) - 14)),
      ([date] < (TODAY() - WEEKDAY(TODAY())) - 7)
    )
  )
)

For this week so far:

SUM(
  SELECT(
    table[Duration],
    AND(
      (USEREMAIL() = [email]),
      ([date] >= (TODAY() - WEEKDAY(TODAY())))
    )
  )
)

View solution in original post

16 REPLIES 16

Steve
Participant V

Can you be more specific about what you need help with?



currently i am using a google docs for their timesheet. as you can see they put the begging and the end of each day giving me the duration. I did all that in my appsheet app and it is working perfectly. What i do not have and do not know what to do is the total of the week as you can see in the picture (32.5). So it would have to be something like sum duration of last week (Sunday to Saturday). and one for the week before.

i could do sum of duration for the past 7 days but it would not be useful since the working week is from Sunday to Saturday. I donโ€™t even know if what i am trying to do can be done. it always have to sum last weeks duration so that even if my employees checks how much hours he did this week and last week on Monday or Wednesday it is the same number (well obviously for the ongoing week the number would have changed)

is it still not clear enough?

Sorry, I missed your update.

It appears you have a column per date. How do you handle new dates?

the image shown above is the current time sheet (they access it directly via google sheets). I have to create a new sheet every two weeks. but the main reason i uploaded that picture is to show you what i mean by the total duration of hours worked by week number by technician. here is the new time sheet done by appsheet. It is my prototype app that i tried with few employรฉes. image|690x201

Iโ€™m afraid Iโ€™m going to be unable to help with this. Your approach is fundamentally contrary to the expectations AppSheet has for data structure, and therefore contrary to how I approach solving problems with AppSheet. I strongly encourage you to reconsider your data structure.

we got out of topic. The first picture is the old way not the appsheet way. The second picture it is how my app is structured and as i said it is working really well. All I need to know is can appsheet sum the total of duration for last week (from Sunday to Saturday) and the week before that (also from Sunday to Saturday) As time goes on and on I always get the summed total for the last two weeks displayed for my employees.

I think that what i want is actually impossible. I should do a scheduled report that send it by user of their total hours done for the week.

They told me that for them it is re comforting know how many hours they have worked until now and what they can expect as a pay.

Whoa! I completely missed that image! I apologize! Let me consider a solution in light of this revelation!

For last week, try:

SUM(
  SELECT(
    table[Duration],
    AND(
      (USEREMAIL() = [email]),
      ([date] >= (TODAY() - WEEKDAY(TODAY()) - 7)),
      ([date] < (TODAY() - WEEKDAY(TODAY())))
    )
  )
)

For the week before that:

SUM(
  SELECT(
    table[Duration],
    AND(
      (USEREMAIL() = [email]),
      ([date] >= (TODAY() - WEEKDAY(TODAY()) - 14)),
      ([date] < (TODAY() - WEEKDAY(TODAY())) - 7)
    )
  )
)

For this week so far:

SUM(
  SELECT(
    table[Duration],
    AND(
      (USEREMAIL() = [email]),
      ([date] >= (TODAY() - WEEKDAY(TODAY())))
    )
  )
)

wow Steve you are a genius with this app!! It is working really well except that sometimes the result disappears i used virtual columns to get the result.

Also it is the first time that i see this note: Note, this expression could significantly impact sync time. should I be worried?

Is it possible to explain the formula. I cant figure out how the dates and weekdays work.

What do mean by this? Can you provide a screenshot?

Virtual columns are recomputed every time the app syncs. The more data you have, the longer the computation and thus the sync will take. Any virtual column that makes use of SELECT() or LOOKUP() (as well as a few others) will cause the process to take even longer. So itโ€™s best to use as few virtual columns as possible, and to keep virtual column app formulas as simple as possible.

In your case, because last-weekโ€™s-hours and two-weeks-ago-hours donโ€™t require frequent updating, you would benefit from converting the from virtual columns and using a weekly report to update them only once a week. This would be a more advanced undertaking, so you may prefer to wait until app performance becomes noticeably slow.

For the current weekโ€™s hours, should you choose to use it, you could probably leave it as a virtual column.

Looking at:

SUM(
  SELECT(
    table[Duration],
    AND(
      (USEREMAIL() = [email]),
      ([date] >= (TODAY() - WEEKDAY(TODAY()) - 7)),
      ([date] < (TODAY() - WEEKDAY(TODAY())))
    )
  )
)
  1. SELECT(table[Duration], ...) gathers a list of Duration column values from rows of the table table that match the given criteria (...; see (2)).

  2. AND(..., ..., ...) matches rows of the table table (per (1)) that match all three of the given criteria (..., ..., ...; see (3), (7), and (8)).

  3. (USEREMAIL() = [email]) matches only those rows with an email column value ([email]) that matches the current app userโ€™s email address (USEREMAIL()).

  4. WEEKDAY(TODAY()) gives the day of the week for today as a number, where Sunday is 1 and Saturday is 7.

  5. TODAY() - ... subtracts the day-of-the-week number for today (...; from (4)) from todayโ€™s date, giving the date of last Saturday.

  6. (... - 7)) subtracts 7 from the date of last Saturday (...; from (5)) to give the date of the Saturday before last.

  7. ([date] >= ...) matches only those rows with a date column value ([date]) that is on or after the date of the Saturday before last (...; from (6)).

  8. ([date] < (...)) matches only those rows with a date column value ([date]) that is strictly before the date of last Saturday (...; see (5)).

  9. SUM(...) adds all of the Duration column values from the matched rows in the table table (...; from (1)).

See also:







If i change the column name it just disapears in the app. I have to delete and redo them in order to get them back.

Hi @Steve ,
I am hoping you can help me get a similar result..? but the difference is my table does not have [email] column, instead it has [name] column which matches [full name] column from the "staff' table, which has the [email] column to match USEREMAIL(). I have tried:

SUM(
  SELECT(
    timesheet[Hours],
    AND(
      IN(LOOKUP(USEREMAIL(), Staff, Email, Full name)= [Name]),
      ([date] >= (TODAY() - WEEKDAY(TODAY())))
    )
  )
)

But it doesn't seem to work. I was also hoping to subtract from these hours  any durations where [task]=break...
Thankyou ๐Ÿ˜

the column structure goes like this:
unique id, week number (based on the date of work), tech name, tech email, client, comment, car, start, end, lunch time, and total duration for that given day.

hello i just updated the formula Thanks to a friend who helped me out a lot. The formulas you gave me Steve work but i never had the correct hours because my weeks starts on Sundays. so here are the formulas if you need to sum the duration but from Sunday to Saturday:

current week:

SUM(
SELECT(
Feuille de temps[Duration],
AND(
(USEREMAIL() = [email]),
([date] <= (TODAY() - WEEKDAY(TODAY()))+7),
([date] >= (TODAY() - WEEKDAY(TODAY()))+1)
)
)
)

previous week:

SUM(
SELECT(
Feuille de temps[Duration],
AND(
(USEREMAIL() = [email]),
([date] <= (TODAY() - WEEKDAY(TODAY()))),
([date] >= (TODAY() - WEEKDAY(TODAY()))-6)
)
)
)

the week before the previous:

SUM(
SELECT(
Feuille de temps[Duration],
AND(
(USEREMAIL() = [email]),
([date] <= (TODAY() - WEEKDAY(TODAY()))-7),
([date] >= (TODAY() - WEEKDAY(TODAY()))-13)
)
)
)

Top Labels in this Space