Formula to Count the Number of Dates Entered

Dave33

New Member
Joined
Nov 8, 2022
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDE
1Stable Name
2Address
3Address 2
4
5Date: October 2022
6
7
8Horse Owner Name
9Address
10Address 2
11
12Horse Name:
13
14Previous Balance:
15Payments Received:
16Balance carried forward:$ -
17
18Board$ 100.00
19NYS 7% Tax $ 7.00
20
21Turnout, Lunge & Walks
22Date/s12, 13, 14, 15$ 6.004$ 24.00
23$ -
24Staff Rides
25Date/s$ 20.001$ 20.00
26
27Lessons
28Date/s (Semi Private)$ 40.001$ 40.00
29
30Date/s (Private)$ 45.001$ 45.00
31
32Special Care
33
34Current Statement Charges$ 236.00
35
36Total Due$ 236.00
Board Lease Template
Cell Formulas
RangeFormula
E16E16=E14+E15
E19E19=18:18*0.07
D22,D30,D28,D25D22=LEN(B22)-LEN(SUBSTITUTE(B22,",",""))+LEN(B23)-LEN(SUBSTITUTE(B23,",",""))+1
E30,E28,E25,E22:E23E22=C22*D22
E34E34=SUM(E18:E33)
E36E36=E16+E34
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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!
 
Upvote 0
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.

Rich (BB code):
=IF(B22&B23="",0,LEN(B22)-LEN(SUBSTITUTE(B22,",",""))+LEN(B23)-LEN(SUBSTITUTE(B23,",",""))+1)
 
Upvote 0
Solution
Yes B23 is a contiuation cell. I forgot to mention that.
 
Upvote 0
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.

Rich (BB code):
=IF(B22&B23="",0,LEN(B22)-LEN(SUBSTITUTE(B22,",",""))+LEN(B23)-LEN(SUBSTITUTE(B23,",",""))+1)
Your code works perfectly. Thank you very much.
 
Upvote 0
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...
 
Upvote 0
Could the number come from the source information?
or try

I show two examples.

T202211a.xlsm
BCD
2212, 13, 14, 157
2316, 17, 20
24
25121
26
5b
Cell Formulas
RangeFormula
D22,D25D22=(LEN(B22)>0)*((LEN(B22)-LEN(SUBSTITUTE(B22,",","")))+1)+(LEN(B23)>0)*((LEN(B23)-LEN(SUBSTITUTE(B23,",","")))+1)
 
Upvote 0
Could the number come from the source information?

T202211a.xlsm
BCD
2212, 13, 14, 157
2316, 17, 20
24
25121
5b
Cell Formulas
RangeFormula
D22,D25D22=SUM(LEN(B22:B23)-LEN(SUBSTITUTE(B22:B23,",","")),--(B22:B23>0))
 
Last edited:
Upvote 0
I'm all set. 6Stringjazz had the correct code.

Thank you all for help and input.
 
Upvote 0
How many numbers in the following?
T202211a.xlsm
B
2212, 13, 14, 15
2316, 17, 20
5b
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top