Help with a formula

Copper Contributor

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. 

2 Replies

@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)))

@DDWHV 

Or

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

Blank "date" is always Saturday.