Forum Discussion

DDWHV's avatar
DDWHV
Copper Contributor
Feb 01, 2024

Help with a formula

I am setting up a spreadsheet and I am trying to count the days of the month as either weekdays or weekends.  In the column, I have the running dates, but I will be erasing some of them as we are not covering those days.  When I use the formulas - =SUMPRODUCT(--(WEEKDAY(A3:A33,2)<=5)) for weekdays and =SUMPRODUCT(--(WEEKDAY(A3:A33,2)>=6)) for weekends, it continues to count the erased boxes even though there is not a date there.  It seems to count them as weekend days.  What can I do to only count weekend/weekday IF there is an actual date in the cell?  Appreciate your time. 

Formulas and Functions

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 02, 2024

    DDWHV 

    Or

    =SUM(--(WEEKDAY(date,2)>=6) ) - COUNTBLANK(date)

    Blank "date" is always Saturday.

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 01, 2024

    DDWHV 

    In Excel day 1 is Sun 1st Jan 1900 so day 0 is a Saturday.  To remove the blanks from your weekend formula you could use

    =SUMPRODUCT((date<>"")*(WEEKDAY(date,2)>=6))

or with dynamic arrays

= SUM(IF(date<>"", SIGN(WEEKDAY(date,2)>=6)))

Resources