Ok Thanks that worked (sort of)

The sheet goes on for 32 lines header and 31 days.
the formula will error if =IF(D20="","",A19+1) is placed in Column A
Column A can be blank
Column D is Data field
If you fill in the date fields (column A) to 31 days then it averages the data by 31. It comes out ok at the end of the month.

=SUMPRODUCT(--(WEEKDAY($A$2:$A$32,2)<6),$D$2:$D$32)/SUMPRODUCT(N(WEEKDAY($A$2:$A$32,2)<6))

The formula works fine except if the date column contains a formula
Date Usage
29-Jul-18 39.69
30-Jul-18 43.58
31-Jul-18 44.90
1-Aug-18 47.69
2-Aug-18 50.69
3-Aug-18 42.15
4-Aug-18 48.08
5-Aug-18 51.65
6-Aug-18 46.63
7-Aug-18 46.00
8-Aug-18 46.00
9-Aug-18 50.48
10-Aug-18 50.11
11-Aug-18 51.79
12-Aug-18 52.00
13-Aug-18 48.42
14-Aug-18 42.68
15-Aug-18 48.15
16-Aug-18