Level 8 · Boss: the monthly returns sheet

One export, three columns

Daniel’s review runs on February 2025’s returns: 39 lines in A1:F40, one per returned item, with the order id, the category, the order timestamp, the return date, the reason and the refund. You add three columns that every returns review needs: when the order was placed (as a month), how long the return took, and a line a person can read.

Everything here is from this chapter: dates are numbers, TEXT turns a number into formatted text, DATEDIF counts whole days, and & joins pieces into a label.

Step 1: the order month

G2   =TEXT(C2,"yyyy-mm")

The first return in row 2 was ordered on 2024-12-23 at 08:00, so G2 shows 2024-12. Fill it down to row 40. February’s returns come from orders placed in three different months: 1 in December, 31 in January and 7 in February. That spread is normal: a return arrives days or weeks after its order, so “returns in February” and “returns of February orders” are different questions. Daniel’s review wants both, and the month key is what lets him group by order month later.

Step 2: days to return

H2   =DATEDIF(C2,D2,"d")

Order dates carry a time, return dates don’t, so plain subtraction gives fractions: =D2-C2 is 39.67 in row 2. DATEDIF counts whole calendar days, 40. Scan the column for sanity: the shortest return takes 5 days and the longest 40, and nothing is negative. A negative or #NUM! would mean a return dated before its order, which is a data problem to report, not to hide.

Step 3: the label

I2   =G2&" | "&UPPER(B2)&" | "&TEXT(F2,"$#,##0.00")&" | "&H2&" days"

Row 2 reads 2024-12 | TEXTILES | $298.00 | 40 days. Two things in this formula are easy to get wrong:

• The refund needs TEXT. F2 shows 298.00 only because of its format; the value is 298, and &F2 prints 298. The format "$#,##0.00" adds the dollar sign, the comma for thousands and two decimals, so row 29’s refund reads $1,352.00.
• The days don’t. H2 is a whole number, so &H2 prints 40 as it is. If you had built step 2 with =D2-C2, the label would read 39.666… days, with the digits running on.

Thinking like an analyst

A label is for reading; the columns are for working. Keep G and H next to the label, because the next questions Daniel asks will group by G and average H, and a label can’t be summed.

Before you hand the sheet over, check it against the raw data:

• Pick one row and redo it by hand: ordered January 26, returned February 2 (row 5) is 7 days.
• Count the month keys: 1 + 31 + 7 = 39, the number of rows.
• Look at the extremes: 40 days in row 2 is a holiday order returned after the new year; that belongs in the review, not in a list of errors.

Common mistakes

• MONTH(C2) as the month key: 12 for December of every year, and no leading zero.
• =D2-C2 for the days: fractional days, which leak into the label.
• &C2 or &F2 without TEXT: a serial number or a refund without its dollar sign.
• "|" without spaces, or "days" without the leading space: the label must match exactly.