Google Sheet Weekly Report Troubleshooting - Subtask 6 (Bonus)

Description

Bonus They really want to use periods to show the dates…

6. Alter the formulas in the Weekly section table to support dates with periods.

Solution

Change:

=SUMIFS('Raw Data Combined'!D:D,'Raw Data Combined'!$A:$A,">="&$B14-6,'Raw Data Combined'!$A:$A,"<="&$B14,'Raw Data Combined'!$I:$I,"<>#N/A")

To:

=SUMIFS('Raw Data Combined'!D:D,'Raw Data Combined'!$A:$A,">="&SUBSTITUTE($B14,".","/")-6,'Raw Data Combined'!$A:$A,"<="&SUBSTITUTE($B14,".","/"),'Raw Data Combined'!$I:$I,"<>#N/A")

Explanation

As mentioned in Subtask 3, valid date formats include - or /. Dates using . aren’t interpreted by Sheets as a valid date. Attempting to pass malformed dates to our SUMIFS() formula causes the function to fail.

To ensure the dates used in the formula are valid, we can insert into SUMIFS() a SUBSTITUTE function to replace . with / before the formula uses the value as a date.

Alternatively, we can accomplish the same thing using theREGEXREPLACE function:

# replaces all . with /
=REGEXREPLACE(TO_TEXT($B16), "\.+", "/")

For a more robust solution, we can replace all non-digit values with /:

# replaces all non-digit [0-9] characters with /
=REGEXREPLACE(TO_TEXT($B14), "\D+", "/")
# Input
11-29.2020 or 11x29y2020 or 11*29&2020, etc.
# Output
11/29/2020

Inserting the code above, the final formula in C14 looks like this:

=SUMIFS('Raw Data Combined'!D:D,'Raw Data Combined'!$A:$A,">="&REGEXREPLACE(TO_TEXT($B14), "\D+", "/")-6,'Raw Data Combined'!$A:$A,"<="&REGEXREPLACE(TO_TEXT($B14), "\D+", "/"), 'Raw Data Combined'!$I:$I,"<>#N/A")

At this point, updating each cell of the table can be arduous. The formula for each cell in a given row contains a unique reference to that row’s date. i.e. ">="&$B14-6, and "<="&$B14. Manually editing every cell would be too time consuming. Slightly faster would be using Find and replace one row at a time.

Using Find and replace with a regular expression and capture group makes updating the formulas even faster. From the Google Sheets Documentation:

You can replace parts of a regular expression with capture groups. You reference these capture groups in the “Replace” string using the format “$.” Note: Capture groups only work with Google Sheets.

For each row, the date column is always B and the row number changes. Using a capture group we can extract the row number and re-insert it into our replace string.

The regular expression "&(\$B\d*) matches "&$B followed by any number of digits and stores $BXX as capture group $1.

We can use the "®EXREPLACE(TO_TEXT($1), "\D+", "/") as the replacement string, using $1 as the cell address we captured in the find string.

Here you can see the Executive Overview sheet working well even with . in dates: