Working with Time, Dates, and Totals in AppSheet: A Practical Guide

Tracking and calculating time-based data can feel intimidating in a no-code platform like AppSheet. But with the right expressions and a practical understanding of how AppSheet processes rows, you can build powerful tools without writing a single line of code. In this guide, I’ll walk you through how I used AppSheet to manage time logs, calculate durations, and generate running totals — all with real-world examples from a Diesel Generator (DG) monitoring system.

1. Calculating Time Durations: On Period and Off Period

Use Case: Calculate how long a DG remains active in each session.

Problem Statement:
Tracking how long a diesel generator remains active is crucial for operational efficiency and maintenance. Manual logs often lead to errors, especially when calculating durations from start and stop times. We needed a solution to automate this calculation. The challenge was combining separate Date and Time fields into a usable duration in AppSheet.

Approach: Subtract the combined DG On Date and Time from DG Off Date and Time.

DATETIME([DG Off Date] & " " & TEXT([DG Off Time])) - DATETIME([DG On Date] & " " & TEXT([DG On Time]))

Explanation:

• DATETIME(...) converts the combined Date and Time values into a single DateTime object.
• TEXT(...) is used to ensure time formatting is retained.
• Subtracting two DateTime values in AppSheet automatically returns a Duration.

Column Type: Duration

2. Validating Time Entries

Problem Statement:
When logging generator events, it’s easy to mistakenly enter an Off time that is earlier than the On time. Without validation, this can distort calculations and reports. We needed to enforce logical consistency between On and Off DateTime values without blocking blank fields during entry.

Purpose: Prevent invalid entries where DG Off DateTime is earlier than DG On DateTime.

OR(
  ISBLANK([DG Off Date]),
  ISBLANK([DG Off Time]),
  DATETIME([DG Off Date] & " " & TEXT([DG Off Time])) >= DATETIME([DG On Date] & " " & TEXT([DG On Time]))
)

Explanation:

• Allows blank values during entry.
• Compares full DateTime values to ensure logical correctness.
• Returns TRUE only when the off datetime is on or after the on datetime.

3. Cumulative Duration: Todate On Period

Problem Statement:
Cumulative calculations are not natively row-aware in AppSheet. We needed a way to keep a running total of ‘On Period’ from the top of the dataset to the current row. This is essential for tracking progressive generator usage over time.

Goal: Calculate a running total of the On Period from the beginning to the current row.

[On Period] + 
IF(
  ISNOTBLANK(
    MAXROW("Grid", "Timestamp", [Timestamp] < [_THISROW].[Timestamp])
  ),
  LOOKUP(
    MAXROW("Grid", "Timestamp", [Timestamp] < [_THISROW].[Timestamp]),
    "Grid",
    "ID",
    "Todate On Period"
  ),
  0
)

Explanation:

• MAXROW(...) finds the previous row with the latest timestamp before the current one.
• LOOKUP(...) fetches the Todate On Period from that row.
• Adds current On Period to the previously calculated total.
• Falls back to 0 if it is the first row.

4. Daily Totals (Same-Date Aggregation)

Use Case: Sum all On Periods that occur on the same date.

Problem Statement:
In many reporting systems, there’s a need to display the total activity duration for each calendar date. Users need to see how long the DG ran each day, even if there are multiple entries. Without automation, these totals have to be calculated manually, leading to inefficiency and errors.

SUM(
  SELECT(Grid[On Period], [Date] = [_THISROW].[Date])
)

Explanation:

• Filters all rows where the Date matches the current row’s Date.
• Sums their On Period values.
• Shows the same total in all rows with the same date.

5. Running Totals Within the Same Date

Use Case: Create a line-by-line running total (top to bottom) within each date.

Problem Statement:
Managers often want to monitor how daily generation accumulates over time for a given date. Instead of a static total, we needed a dynamic way to build a running sum for each entry. This helps in understanding energy distribution and identifying spikes.

SUM(
  SELECT(
    DG[Daily Units Generated],
    AND(
      [Date] = [_THISROW].[Date],
      [_ROWNUMBER] <= [_THISROW].[_ROWNUMBER]
    )
  )
)

Explanation:

• _ROWNUMBER identifies the order of rows.
• Sums all previous and current Daily Units Generated where the date matches.
• Useful for dashboards and progressive metrics.

6. Overall Totals Till Current Row

Example: Units Generated Todate across all records.

Problem Statement:
We needed to track total energy generated from the first log entry up to the current one. This is useful for cumulative reporting and helps determine long-term energy consumption. AppSheet doesn’t support row-by-row memory like traditional code, so we had to simulate it.

SUM(
  SELECT(DG[Daily Units Generated], [_ROWNUMBER] <= [_THISROW].[_ROWNUMBER])
)

Alternate (using Timestamp):

SUM(
  SELECT(DG[Daily Units Generated], [Timestamp] <= [_THISROW].[Timestamp])
)

Explanation:

• Captures all values above the current row in terms of entry time.
• Timestamp-based tracking avoids row shifting errors when rows are inserted/deleted.

7. Calculated Metrics: Units Based on Duration

Use Case: Multiply energy reading (kWh) with DG runtime in hours.

Problem Statement:
DG efficiency is often calculated based on how much energy is produced per hour of operation. With separate readings for Kwh and time, we needed a way to accurately convert duration into decimal hours and multiply it with the Kwh difference to get true energy output.

[Difference Kwh] * (
  HOUR([Todate On Period]) + 
  (MINUTE([Todate On Period]) / 60) + 
  (SECOND([Todate On Period]) / 3600)
)

Explanation:

• HOUR(...), MINUTE(...), and SECOND(...) extract time components.
• Convert total duration to decimal hours.
• Multiply by Kwh difference to get actual units generated.

Conclusion

Using AppSheet’s expressions for working with Time, Dates, and Totals opens the door to building highly functional internal apps. These formulas, when stacked smartly, allow for running logs, validations, real-time calculations, and meaningful reports — all without writing backend code.

If you’re looking to digitize logbooks, energy reports, or operational dashboards, AppSheet gives you just enough power to do it the no-code way.