Need some help with a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1565655%22%20slang%3D%22en-US%22%3ENeed%20some%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565655%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20column%20to%20allow%20me%20to%20Add%20multiple%20dates%20in%201%20cell%2C%20then%20total%20the%20amount%20of%20dates%20in%20another%20cell.%20See%20the%20attached%20file%20for%20reference%20on%20what%20I%20need.%3C%2FP%3E%3CP%3EExample%20Dates%3A%3C%2FP%3E%3CP%3Ein%20cell%20B%3A%201%2C%205%2C%2012%2C%2015%2C%2017%3C%2FP%3E%3CP%3ETotal%20of%20dates%20in%20cell%20C%3A%205%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1565655%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565740%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565740%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F749305%22%20target%3D%22_blank%22%3E%40stephbuch11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20C8%20as%20General%20instead%20of%20as%20Text%2C%20and%20enter%20the%20following%20formula%20in%20C8%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DLEN(B8)-LEN(SUBSTITUTE(B8%2C%22%2C%22%2C%22%22))%2B1%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565754%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565754%22%20slang%3D%22en-US%22%3ESweet!%20That%20totally%20worked%20thank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565768%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565768%22%20slang%3D%22en-US%22%3EDoes%20it%20always%20have%20to%20have%20the%201%3F%20sometimes%20the%20final%20will%20be%20zero%2C%20if%20they%20didn't%20use%20that%20particular%20service.%20Or%20can%20I%20just%20put%200%20as%20needed%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565779%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565779%22%20slang%3D%22en-US%22%3Eokay%20wait%20it's%20not%20working%20now%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565794%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565794%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%20this%20is%20what%20happened%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%20Sometimes%20the%20cell%20will%20be%20zero%20also.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565816%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565816%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F749305%22%20target%3D%22_blank%22%3E%40stephbuch11%3C%2FA%3E%26nbsp%3BWhen%20you%20copied%20down%20you%20somehow%20kept%20the%20first%20cell%20reference%20as%20C8.%26nbsp%3B%20Did%20you%20copy%20down%20or%20paste%20the%20text%20in%20each%20cell%20and%20manually%20update%3F%26nbsp%3B%20I%20corrected%20the%20problem%20in%20the%20attached%20and%20added%20the%20empty%20condition%20to%20%3D0.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565925%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565925%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BI%20thought%20I%20did%20the%20change-and%20I%20did%20it%20manually%2C%20but%20maybe%20did%20it%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565933%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565933%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3Bahhhh%20perfect%2C%20and%20I%20also%20see%20the%200%2C%20I%20tried%20to%20do%20that%20myself%2C%20but%20put%20it%20in%20the%20wrong%20place.%20Thanks%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565956%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20some%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F749305%22%20target%3D%22_blank%22%3E%40stephbuch11%3C%2FA%3E%26nbsp%3B%20You're%20very%20welcome.%26nbsp%3B%20Glad%20it%20works.%26nbsp%3B%20Make%20sure%20to%20give%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20some%20Likes%20for%20getting%20you%20the%20original%20formula.%3C%2FP%3E%3CP%3EFYI%20for%20future.%26nbsp%3B%20You%20can%20copy%20a%20formula%20down%20(either%3A%20a)%20copy%2C%20highlight%20the%20whole%20range%20and%20then%20paste%20or%20b)%20put%20the%20cursor%20on%20the%20dot%20in%20the%20bottom%20right%20corner%20and%20either%20double-click%20or%20drag%20it%20over%20the%20range%20or%20c)%20highlight%20the%20starting%20cell%20and%20the%20rest%20of%20the%20range%20and%20'Fill%20Down'%20(or%20Right%20if%20it%20is%20a%20row)%3C%2FP%3E%3CP%3Ethen%20you%20won't%20have%20manual%20typing%20errors%20as%20long%20as%20you%20set%20up%20the%20formula%20correctly%20to%20start.%26nbsp%3B%20If%20you%20do%20more%20formulas%20you%20will%20want%20to%20learn%20about%20how%20to%20use%20a%20'%24'%20to%20prevent%20excel%20from%20incrementing%20those%20references%20when%20you%20copy%2Ffill%20down%20(it%20makes%20the%20reference%20absolute%20instead%20of%20relative).%26nbsp%3B%20Excel%20can%20do%20great%20things%2C%20but%20I%20don't%20want%20to%20overwhelm%20you%20either.%3C%2FP%3E%3CP%3EBest%20of%20luck%20and%20love%20the%20company%20name%2Flogo%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I need a column to allow me to Add multiple dates in 1 cell, then total the amount of dates in another cell. See the attached file for reference on what I need.

Example Dates:

in cell B: 1, 5, 12, 15, 17

Total of dates in cell C: 5

 

 

9 Replies
Highlighted

@stephbuch11 

Format C8 as General instead of as Text, and enter the following formula in C8:

 

=LEN(B8)-LEN(SUBSTITUTE(B8,",",""))+1

Highlighted
Sweet! That totally worked thank you!
Highlighted
Does it always have to have the 1? sometimes the final will be zero, if they didn't use that particular service. Or can I just put 0 as needed?
Highlighted
okay wait it's not working now?
Highlighted

@Hans Vogelaar this is what happened   Sometimes the cell will be zero also.

Highlighted

@stephbuch11 When you copied down you somehow kept the first cell reference as C8.  Did you copy down or paste the text in each cell and manually update?  I corrected the problem in the attached and added the empty condition to =0.

Highlighted

@mtarler I thought I did the change-and I did it manually, but maybe did it wrong?

Highlighted

@mtarler ahhhh perfect, and I also see the 0, I tried to do that myself, but put it in the wrong place. Thanks so much!

Highlighted

@stephbuch11  You're very welcome.  Glad it works.  Make sure to give @Hans Vogelaar  some Likes for getting you the original formula.

FYI for future.  You can copy a formula down (either: a) copy, highlight the whole range and then paste or b) put the cursor on the dot in the bottom right corner and either double-click or drag it over the range or c) highlight the starting cell and the rest of the range and 'Fill Down' (or Right if it is a row)

then you won't have manual typing errors as long as you set up the formula correctly to start.  If you do more formulas you will want to learn about how to use a '$' to prevent excel from incrementing those references when you copy/fill down (it makes the reference absolute instead of relative).  Excel can do great things, but I don't want to overwhelm you either.

Best of luck and love the company name/logo