Formula to Count the Number of Dates Entered

[Dave33]

My wife has a part time job at a riding stable and one of her duties is sending out monthly invoices to customers. The stable uses Excel for billing and there are over 50 invoices for various services she has to create. I'm trying to help her streamline this process as it takes her a long time to get them done. She's an Excel novice. I did help by setting up some.


One of the services the stable provides is horse exercising and turn outs. She has to list all the dates this service was provided for the month on each invoice then count the number of dates and insert this number in cell D22. In the example sheet I attached I inserted 4 dates in B22. 12, 13, 14, 15 which totals 4 in D22. But when you remove the dates, it still populates a 1 and places a charge in cell E22. What I need is cell B22 when blank, not to populate a number and corresponding charge.

Thanks in advance.

Book10.xlsx
	A	B	C	D	E
1	Stable Name
2	Address
3	Address 2
4
5	Date: October 2022
6
7
8	Horse Owner Name
9	Address
10	Address 2
11
12	Horse Name:
13
14	Previous Balance:
15	Payments Received:
16	Balance carried forward:				$ -
17
18	Board				$ 100.00
19	NYS 7% Tax				$ 7.00
20
21	Turnout, Lunge & Walks
22	Date/s	12, 13, 14, 15	$ 6.00	4	$ 24.00
23					$ -
24	Staff Rides
25	Date/s		$ 20.00	1	$ 20.00
26
27	Lessons
28	Date/s (Semi Private)		$ 40.00	1	$ 40.00
29
30	Date/s (Private)		$ 45.00	1	$ 45.00
31
32	Special Care
33
34	Current Statement Charges				$ 236.00
35
36	Total Due				$ 236.00
Board Lease Template

Cell Formulas
Range	Formula
E16	E16	=E14+E15
E19	E19	=18:18*0.07
D22,D30,D28,D25	D22	=LEN(B22)-LEN(SUBSTITUTE(B22,",",""))+LEN(B23)-LEN(SUBSTITUTE(B23,",",""))+1
E30,E28,E25,E22:E23	E22	=C22*D22
E34	E34	=SUM(E18:E33)
E36	E36	=E16+E34

 

[ZMyrrh]

Change D22 to =IF(LEN(B22)-LEN(SUBSTITUTE(B22,",",""))+LEN(B23)-LEN(SUBSTITUTE(B23,",",""))+1 = 1,"",LEN(B22)-LEN(SUBSTITUTE(B22,",",""))+LEN(B23)-LEN(SUBSTITUTE(B23,",",""))+1) and E22 to =IFERROR(C22*D22,"")

That should be all but if I missed something just let me know!

 

[6StringJazzer]

Is B23 a continuation cell that could have more dates?

This is the simplest way to change your existing formula. There is probably a cleaner way to do this in Excel 365.

=IF(B22&B23="",0,LEN(B22)-LEN(SUBSTITUTE(B22,",",""))+LEN(B23)-LEN(SUBSTITUTE(B23,",",""))+1)

 

[Dave33]

Yes B23 is a contiuation cell. I forgot to mention that.

 

[Dave33]

Is B23 a continuation cell that could have more dates?

This is the simplest way to change your existing formula. There is probably a cleaner way to do this in Excel 365.

=IF(B22&B23="",0,LEN(B22)-LEN(SUBSTITUTE(B22,",",""))+LEN(B23)-LEN(SUBSTITUTE(B23,",",""))+1)

Your code works perfectly. Thank you very much.

 

[Dave33]

Change D22 to =IF(LEN(B22)-LEN(SUBSTITUTE(B22,",",""))+LEN(B23)-LEN(SUBSTITUTE(B23,",",""))+1 = 1,"",LEN(B22)-LEN(SUBSTITUTE(B22,",",""))+LEN(B23)-LEN(SUBSTITUTE(B23,",",""))+1) and E22 to =IFERROR(C22*D22,"")

That should be all but if I missed something just let me know!

Thank you. I'll give it a try...

 

[Dave Patton]

Could the number come from the source information?
or try

I show two examples.

T202211a.xlsm
	B	C	D
22	12, 13, 14, 15		7
23	16, 17, 20
24
25	12		1
26
5b

Cell Formulas
Range	Formula
D22,D25	D22	=(LEN(B22)>0)*((LEN(B22)-LEN(SUBSTITUTE(B22,",","")))+1)+(LEN(B23)>0)*((LEN(B23)-LEN(SUBSTITUTE(B23,",","")))+1)

 

[Dave Patton]

Could the number come from the source information?

T202211a.xlsm
	B	C	D
22	12, 13, 14, 15		7
23	16, 17, 20
24
25	12		1
5b

Cell Formulas
Range	Formula
D22,D25	D22	=SUM(LEN(B22:B23)-LEN(SUBSTITUTE(B22:B23,",","")),--(B22:B23>0))

 

[Dave33]

I'm all set. 6Stringjazz had the correct code.

Thank you all for help and input.

 

[Dave Patton]

How many numbers in the following?

T202211a.xlsm
	B
22	12, 13, 14, 15
23	16, 17, 20
5b