To compare monthly sales when months have different numbers of trading days (the days you’re open), report total monthly sales and sales per open day side by side. Count open days with NETWORKDAYS for a Monday–Friday schedule, or NETWORKDAYS.INTL for other schedules, and list your actual closures. Then divide sales by that count. The total shows revenue scale; the daily figure shows whether an average open day sold more or less.
For instance, suppose a supply distributor open Monday–Friday sold $95,000 in February 2026 and $103,400 in March. March is up $8,400, or 8.84%. But March had 22 open days, and February had 19, so sales per open day fell from $5,000 to $4,700, a 6.00% decline. Reporting only the first number hides the second.
Why You Need Both Numbers
March had 10.71% more calendar days but 15.79% more open days, because February also lost a weekday to a company closure on February 16. Total sales rose 8.84%, while sales per open day fell 6.00%.
Each metric answers a different question:
- Total monthly sales measures revenue scale. It isn’t profit, and it isn’t cash. The SEC’s beginner’s guide to financial statements treats revenue, expenses, profit, and cash flows as separate things. To see whether costs are covered, compare gross profit with expenses. To see whether cash is available for bills, track customer collections and payments.
- Sales per open day removes the difference in month length. It can still move with weekday mix, operating hours, pricing, customer and product mix, and seasonality. It describes the change without explaining it.
Set Up the Calculation in Excel or Google Sheets
Here’s the finished sheet you’ll build. Set up a sheet named Trading_Comparison with the headers below in row 1, February in row 2, March in row 3, and the change in row 4. Column A is a text label, so type '2026-02 with a leading apostrophe to stop the sheet from turning it into a date.
Open in Google Sheets to view the full sheet and its formulas.
These functions are documented for both Excel and Google Sheets.
1. Enter real dates. Put =DATE(2026,2,1) in B2 and =EOMONTH(B2,0) in C2, then =DATE(2026,3,1) in B3 and =EOMONTH(B3,0) in C3. Changing the number format doesn’t turn text into a date. For imported text dates, confirm the source’s order (month/day or day/month), then convert them in a helper column with =DATEVALUE(TRIM(X2)), where X2 is the imported text, and paste the results as values. =ISNUMBER(B2) should return TRUE.
2. List your actual closures. On a sheet named Holidays, put a header in A1 and enter =DATE(2026,2,16) in A2. List only full days you closed on a normal operating day. A Tuesday weather shutdown counts; a public holiday when you stayed open doesn’t. Leave A3:A10 blank until you need them, and point every formula at the same range, Holidays!$A$2:$A$10. To catch closures typed as text, put this in Holidays!C2:
=IF(COUNTA($A$2:$A$10)=COUNT($A$2:$A$10),"OK","CHECK CLOSURES")
This check flags nonnumeric entries; it does not confirm that a date belongs on your closure calendar. Correct flagged entries even if the calculation appears to work.
3. Count open days. In G2, enter this formula and copy it to G3:
=IF(OR(NOT(ISNUMBER(B2)),NOT(ISNUMBER(C2)),C2<B2),"CHECK DATES",NETWORKDAYS(B2,C2,Holidays!$A$2:$A$10))
It returns 19 for February and 22 for March. NETWORKDAYS treats Saturday and Sunday as the weekend and has no built-in holiday calendar. It subtracts only the dates you list. The “CHECK DATES” guard checks the period dates in B and C for missing or text values and reversed order.
Complete the supporting columns in this step: enter =C2-B2+1 in D2, =NETWORKDAYS(B2,C2) in E2, and =E2-G2 in F2, then copy all three to row 3. Column F, Closure_Days, shows how many scheduled days were removed by the closure list.
Monday–Saturday alternative: Use weekend code 11, which excludes Sundays only in Excel and Google Sheets. Change E2 to =NETWORKDAYS.INTL(B2,C2,11), rename E1 to Scheduled_Days_Before_Closures, and replace G2 with:
=IF(OR(NOT(ISNUMBER(B2)),NOT(ISNUMBER(C2)),C2<B2),"CHECK DATES",NETWORKDAYS.INTL(B2,C2,11,Holidays!$A$2:$A$10))
Copy both E2 and G2 to row 3. E now shows 24 and 26 scheduled days, G shows 23 and 26 open days, and F remains 1 and 0 closure days.
4. Divide sales by open days. Enter each month’s sales as numbers in H2:H3. In I2, enter this formula and copy it to I3:
=IF(OR(NOT(ISNUMBER(G2)),NOT(ISNUMBER(H2))),"CHECK INPUTS",IF(G2<=0,"NO OPEN DAYS",H2/G2))
It returns $5,000 and $4,700. A month with no open days shows “NO OPEN DAYS” instead of #DIV/0!. If G2 shows “CHECK DATES” or an error, I2 shows “CHECK INPUTS,” so fix the source dates first. ISNUMBER confirms that H2 holds a number, not that it covers the whole month, so reconcile each total to your sales report.
For row 4, type Mar vs Feb in A4, enter =D3-D2 in D4, and copy it across to I4. To calculate a percentage change separately, use =G3/G2-1 for open days and format the result as a percent; it returns 15.79%.
Split the $8,400 Change Between Days and Daily Sales
The monthly change breaks into two parts that add up exactly. Put labels in K2:K4 and these formulas in L2:L4:
| Cell | Formula | Result | Meaning |
|---|---|---|---|
| L2 | =(G3-G2)*I2 |
$15,000.00 | 3 extra open days valued at February’s $5,000 |
| L3 | =(I3-I2)*G3 |
-$6,600.00 | $300 lower average across March’s 22 open days |
| L4 | =L2+L3 |
$8,400.00 | Should match H4 |
=ROUND(L4-H4,2)=0 returns TRUE when the split reconciles.
This split is one choice. If you value the extra days at March’s $4,700 and apply the rate change to February’s 19 days, you get +$14,100 and -$5,700, which also total $8,400. Pick one order and use it every month. Either way, the split is arithmetic and doesn’t explain why the average fell. For that, check order count, average order size, March’s weekday mix, pricing, and product mix.
When the Numbers Look Wrong
| Symptom | Cause | Fix |
|---|---|---|
| February shows 20 open days instead of 19, or a closure added in A3 changes nothing | The holiday range was left out, or it points to $A$2:$A$2 |
Use Holidays!$A$2:$A$10 in every row |
| Too few open days for a Monday–Saturday business | NETWORKDAYS always removes Saturday and Sunday | Apply the step 3 alternative to both E and G, using NETWORKDAYS.INTL with weekend code 11, and rename E1 to Scheduled_Days_Before_Closures |
#VALUE!, #NUM!, "CHECK DATES," or a negative day count |
A period date is invalid or reversed, or a closure entry is malformed or outside the supported date range; text-date handling differs by application | Rebuild dates with =DATE(...), or convert text dates and paste as values; verify each closure against your calendar |
| "CHECK CLOSURES" on the Holidays sheet | A cell in Holidays!$A$2:$A$10 holds text, an error, or "" (plain numbers still pass COUNTA=COUNT) |
Re-enter each closure with =DATE(...) and verify the dates against your closure calendar |
#DIV/0! in column I |
Sales were divided by open days without the guard | Use the guarded formula in step 4 |
Leaving out the closure list in the example divides February by 20 days, which gives $4,750 per day. March’s decline then looks like 1.05% instead of 6.00%.
Frequently Asked Questions
What if my business sells online seven days a week?
Use calendar days (=C2-B2+1, minus any full-day closures) as the denominator. Assuming the online store stayed open on February 16 and had no other full-day closures, dividing this example’s totals by 28 and 31 calendar days gives $3,392.86 per day in February and $3,335.48 in March, a 1.69% decline. Weekday and weekend volume often differ, so the mix of days can still move the average.
How do I handle closures that differ from public holidays, or half-days?
The functions subtract only the dates you list, so list your real full-day closures. For scheduled half-days, track open hours in a separate column and divide sales by hours.
Why doesn’t AVERAGE over my daily February log return $5,000?
If the log has 28 rows and the 9 closed days are entered as $0, AVERAGE counts those zeros and returns $95,000 / 28 ≈ $3,392.86. To get $5,000, average only the 19 open days, keeping any genuine $0 open days and confirming every open day has a recorded value. Don’t fill a missing day with zero. Blank or Zero? Why Missing Values Change Your Average covers this in detail.
What to Do Next
Report both metrics monthly, alongside margin, expenses, and cash. How to build a monthly KPI scorecard covers pairing totals with rates, and What to Include in a Monthly Business Report covers the full monthly pack.