Date & Time
Find highest & lowest value on a specific day of week (WEEKDAY, MAXIFS & MINIFS)
Use a Monday-first weekday helper to find maximum and minimum numeric readings, while distinguishing no matching dates from a genuine zero result.
Isolate peak Mondays or quietest Saturdays from a daily time series!
The example uses six complete daily observations. WEEKDAY(date,2) numbers Monday 1 through Sunday 7. All readings must be numeric; negative values are allowed for metrics where they make sense.
This exercise accepts whole-day date serials from March 1, 1900 through December 31, 9999 and rejects text dates or timestamps with a fractional day. The lower bound avoids early 1900 date-system edge cases. All inputs must use the workbook’s date system. Missing readings invalidate the complete-log calculation rather than being treated as zero.
| Date (A) | Daily Load MW (B) | ISO WEEKDAY(A, 2) (C) | Day Name & Role |
|---|---|---|---|
| 2026-10-19 | 410 | 1 (Monday) | Monday #1 |
| 2026-10-20 | 520 | 2 (Tuesday) | Overall peak (not Monday!) |
| 2026-10-23 | 380 | 5 (Friday) | Friday #1 |
| 2026-10-26 | 465 | 1 (Monday) | Peak Monday = 465 MW! |
| 2026-10-30 | 340 | 5 (Friday) | Lowest Friday = 340 MW! |
| 2026-11-02 | 430 | 1 (Monday) | Lowest Monday = 410 MW |
Maximum value occurring on target ISO weekday F2 (1 = Mon .. 7 = Sun)
Ignores Tuesday's 520 MW overall spike to return true Monday peak 465 MW.
=IF(OR(COUNT(F2)<>1,COUNT(C2:C7)<>6,COUNT(B2:B7)<>6),"Invalid inputs",IF(OR(F2<1,F2>7,F2<>INT(F2)),"Invalid inputs",IF(COUNTIF(C2:C7,F2)=0,"No records",MAXIFS(B2:B7, C2:C7, F2))))Minimum value occurring on target ISO weekday F2
Check for matching dates before computing the minimum. No matching dates returns No records, while a recorded zero remains numeric zero. This avoids interpreting the default zero from a no-match aggregation as a measurement.
=IF(OR(COUNT(F2)<>1,COUNT(C2:C7)<>6,COUNT(B2:B7)<>6),"Invalid inputs",IF(OR(F2<1,F2>7,F2<>INT(F2)),"Invalid inputs",IF(COUNTIF(C2:C7,F2)=0,"No records",MINIFS(B2:B7, C2:C7, F2))))Try the example
Use the original values or change them to test your understanding.