TOTALHOURS()

Count of hours (Decimal) in Duration

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)
      )
    )
  )
)
  1. SELECT(Timesheets[Hours], ...) gathers the Duration-type values of the Hours column from rows in the Timesheets table that match the given criteria (... ; see (2)). See also: SELECT()

  2. AND(..., ..., ...) includes only rows that match all of the given criteria (see (3), (4), and (5)). See also: AND()

  3. ([Employee] = USEREMAIL()) includes only rows with an Employee column value that matches the current app user's email address. See also: USEREMAIL()

  4. ([Date Worked] > (TODAY() - WEEKDAY(TODAY()))) includes only rows for which the Date Worked is after the last day of the previous week. See also: TODAY(), WEEKDAY()

  5. ([Date Worked] <= (TODAY() - WEEKDAY(TODAY())) + 7) includes only rows for which the Date Worked is on or before the last day of the the current week.
  6. SUM(...) computes the total time as a Duration value from the gathered daily Hours values from (1). See also: SUM()

  7. TOTALHOURS(...) converts the Duration value from (6) to a Decimal value.

Syntax

TOTALHOURS(duration)

  • duration - A Duration value.

See also

Date and time expressions

HOUR()

MINUTE()

SECOND()

TOTALMINUTES()

TOTALSECONDS()

 

Was this helpful?

How can we improve it?

Need more help?

Try these next steps:

true
Search
Clear search
Close search
Google apps
Main menu
18015585048222032216
true
Search Help Center
true
true
true
false
false