Text, dates and pivot tables

Clean data, work with dates and summarize with a pivot table.

Text functions

=LEFT(A2, 3)   =RIGHT(A2, 4)   =MID(A2, 2, 5)
=LEN(A2)       =UPPER(A2)      =PROPER(A2)
=A2&" "&B2                     # join text
=SUBSTITUTE(A2, "-", "")       # replace text
=TEXTJOIN(", ", TRUE, A2:A6)   # join a range

Dates

=TODAY()                       # current date
=B2-A2                         # days between two dates
=EDATE(A2, 3)                  # three months later
=EOMONTH(A2, 0)                # last day of that month
=NETWORKDAYS(A2, B2)           # working days between dates
=TEXT(A2, "yyyy-mm-dd")        # format as text

Dates are stored as numbers (days since a start date), which is why subtracting works. If a date looks like a number, change the cell format.

Pivot tables

1. Make sure the data has one header row and no blank rows or columns.
2. Select the data and insert a PivotTable.
3. Drag a field to Rows (what to group by), another to Values (what to total).
4. Add Filters or Columns to slice further.

NoteKeep raw data on one sheet and analysis on another. A pivot table updates when you refresh it, not instantly.