Calculate total of hours of same day

AppSheet AppSheet Q&A
1

I created a column xduration which counts the difference between start and endtime

i created also a virtual column xdate which contains the date of the starttime

i want to calculate total hours of this day so that insee how many hours i have on this day

I suppose: i have tot create in my list a virtual field with formulae (and here i don’t know) totalhours of same day (but hiw to select? ) any idea?

2

Welcome to the community!

Your question is not clear. Please post screenshots of your table columns, and explain in more details what you are trying to achieve. Thanks.

1 Like
3

Table

fields starting with x are virtual calculated field. The field xworkload summarise all rows with same date.

starttime - endtime - title - xduration -xdate. - xworkload

1/1/2001 12:00 1/1/2001 13:00 Visit1 1:00. 1/1/2001. -2:00

1/1/2001 14:00. 1/1/2001 15:00 Visit2. 1:00 1/1/2001. -2:00

4

I understand that “xworkload” is where you want to calculate the total hours of the day. Its formula would be:

SUM( SELECT(visitsTable[xduration], [xdate] = [_ThisRow].[xdate]) )

The above solution will work, however, putting this formula and your other formulas in virtual columns is not a good way to preform this calculations, since it would impact your app’s performance as the dataset grows. Better to do the following, a bit more complicated, but will benefit you on the long run:

  • “xduration” should be a normal column in your sheet
  • Have a new table for Dates, the column should be “xdate” and “xworkload”.
  • Build a Bot so that whenever a new record is added to visitsTable, a new record is created in Dates table with [xdate] if it does not exist, and add [xduration] of visitsTable to [xworkload] in Dates table for the same [xdate].

If you make “xdate” as the key column in the Dates table, you can still have an “xdate” as normal column in visitsTable and make it a Ref to Dates table. In this way, you can have a view of Dates table, and when you click on a date you will see all related visitsTable records.

Read these:

References Between Tables | AppSheet Help Center

Actions: The Essentials | AppSheet Help Center

AppSheet Automation: The Essentials | AppSheet Help Center

2 Likes