l Tracked Hours Expression (not including Sunday)

AppSheet AppSheet Q&A
1

Can any one help with this expression. My staff use this app to track their hours throughout the week. I have a virtual column called “CurrentWeekTotalTime” with the following App formula:

SUM(
SELECT(
Timesheets[Total Time (Hrs)],
AND(
[Staff ID] = [_THISROW].[Staff ID],
YEAR([Date]) = YEAR(TODAY()),
EOWEEK([Date]) = EOWEEK(TODAY()),
[Date] >= (TODAY() - WEEKDAY(TODAY()) + 2)
)
)
)

The problem is, on a Sunday it says 0 hours tracked for this week.

Our working week is from Monday to Sunday, and so I need the total to include Sunday’s hours and then reset on Monday morning. I’m guessing EOWEEK is the problem, but after 2hours troubleshooting with ChatGPT, I still cant solve it.

Any help will be greatly appreciated.

Thanks.

2

Please try below expression:

SUM(
SELECT(
Timesheets[Total Time (Hrs)],
AND(
[Staff ID] = [_THISROW].[Staff ID],

IF(WEEKDAY(TODAY())=1,
AND([Date]>=TODAY()-6 ,[Date]<=TODAY()),
AND([Date]>=EOWEEK(TODAY())-5 ,[Date]<=EOWEEK(TODAY())+1)

)

)
)
)

1 Like
3

Thank you, that worked perfectly.

1 Like
4

You are welcome.

If possible, instead of giving heavy calculation to an expression in a VC, you may want to create a " Current_Week" slice with a filter expression

IF(WEEKDAY(TODAY())=1,
AND([Date]>=TODAY()-6 ,[Date]<=TODAY()),
AND([Date]>=EOWEEK(TODAY())-5 ,[Date]<=EOWEEK(TODAY())+1)

)

Then your VC expression could be simpler such as

SUM(
SELECT(
Current_Week[Total Time (Hrs)],

[Staff ID] = [_THISROW].[Staff ID]

)

)

The expression will need to evaluate a sub set of records of current week only , instead of evaluating entire table and therby being more sync expensive.