Returns the number of hours (Decimal
value) represented by the given duration of time.
Sample usage
TOTALHOURS("000:00:18")
returns 0.005
(number of hours in 18 seconds)
TOTALHOURS("000:15:00")
returns 0.25
(number of hours in 15 minutes)
TOTALHOURS("024:00:00")
returns 24
TOTALHOURS("024:00:00" + "000:15:00" + "000:00:18")
returns 24.255
; equivalent to TOTALHOURS(SUM(LIST("024:00:00", "000:15:00", "000:00:18")))
. See also: LIST()
, SUM()
TOTALHOURS(TIMENOW() - "00:00:00")
returns the number of hours that have passed from midnight today to now. TIMENOW()
returns the current time; subtracting one Time
value ("00:00:00"
) from another gives the needed Duration
value argument. See also: TIMENOW()
, Date and time expressions
Hours worked this week
TOTALHOURS(
SUM(
SELECT(
Timesheets[Hours],
AND(
([Employee] = USEREMAIL()),
([Date Worked] > (TODAY() - WEEKDAY(TODAY()))),
([Date Worked] <= (TODAY() - WEEKDAY(TODAY())) + 7)
)
)
)
)
-
SELECT(Timesheets[Hours], ...)
gathers theDuration
-type values of theHours
column from rows in theTimesheets
table that match the given criteria (...
; see (2)). See also:SELECT()
-
AND(..., ..., ...)
includes only rows that match all of the given criteria (see (3), (4), and (5)). See also:AND()
-
([Employee] = USEREMAIL())
includes only rows with anEmployee
column value that matches the current app user's email address. See also:USEREMAIL()
-
([Date Worked] > (TODAY() - WEEKDAY(TODAY())))
includes only rows for which theDate Worked
is after the last day of the previous week. See also:TODAY()
,WEEKDAY()
([Date Worked] <= (TODAY() - WEEKDAY(TODAY())) + 7)
includes only rows for which theDate Worked
is on or before the last day of the the current week.-
SUM(...)
computes the total time as aDuration
value from the gathered dailyHours
values from (1). See also:SUM()
TOTALHOURS(...)
converts theDuration
value from (6) to aDecimal
value.
Syntax
TOTALHOURS(duration)
duration
- ADuration
value.
See also