+ Reply to Thread
Results 1 to 18 of 18

Counting Dates in a Single Cell

  1. #1
    Registered User
    Join Date
    10-08-2016
    Location
    Munich, Germany
    MS-Off Ver
    16.0.7167.2060
    Posts
    6

    Counting Dates in a Single Cell

    I want to count dates in a single cell. The dates will be written in the form of: 05 09 11 21. I could use commas to separate the dates or even both commas and spaces. Searching on the internet, I have found two ways I can to do this – it’s just sad that none of them work.
    Here’s what I have found:

    1. =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",","")+1 (using commas to separate the text) (The brackets around TRIM(A1) are red and, so is the last bracket)

    2. =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1 (using a space to separate) (The brackets around (A1," ","") are red)

    I have written the formulas as they appear in the fx field, so it appears that the brackets are wrong. I think my version of Excel is the latest
    Ideally I would like to allow for an either commas or spaces for input, but as I haven’t managed to get past elementary school Excel level, I’ll hold.

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Counting Dates in a Single Cell

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-08-2016
    Location
    Munich, Germany
    MS-Off Ver
    16.0.7167.2060
    Posts
    6

    Re: Counting Dates in a Single Cell

    It didn't work.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting Dates in a Single Cell

    Can you post a SMALL sample file and show us what result you expect?

    A SMALL file will have about 20 rows worth of data.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    10-08-2016
    Location
    Munich, Germany
    MS-Off Ver
    16.0.7167.2060
    Posts
    6

    Re: Counting Dates in a Single Cell

    Please, don’t take offence that the question is too simple. I’ve only been using excel for two weeks and this is only the third spreadsheet that I’ve created.

    The answer is clearly: =LEN(A2)/3. But this will only work if an extra space is typed in, either at the beginning or end.

    I’ve tried =(LEN(A2)+1)/3, but anytime I put secondary brackets around (A2), it won’t accept them. In fact, every time I’ve typed any formula with multiple brackets, the brackets around the cell number change to red. I’ve tried a find function; and I’m trying to work out if an array will work. It should be very simple to create a function that can recognize numbers and count them and divide them, so the data is easy to input i.e., extra spaces won’t change the result, and the data can be input with spaces or commas.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting Dates in a Single Cell

    Quote Originally Posted by Rol.S View Post
    Ideally I would like to allow for an either commas or spaces for input.
    It would be easier if you maintain some consistency by using one or the other but not both.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Counting Dates in a Single Cell

    As Tony said: will you please attach a sample Excel workbook? Please don't attach a picture of one.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  8. #8
    Registered User
    Join Date
    10-08-2016
    Location
    Munich, Germany
    MS-Off Ver
    16.0.7167.2060
    Posts
    6

    Re: Counting Dates in a Single Cell

    Excel example sheet attached.
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Counting Dates in a Single Cell

    Please Login or Register  to view this content.
    Look for commas and replace with "", else if not found replace " " with ""
    Last edited by protonLeah; 10-08-2016 at 06:56 PM.
    Ben Van Johnson

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting Dates in a Single Cell

    This formula works in your sample file.

    Entered in C2 and copied down:

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

  11. #11
    Registered User
    Join Date
    10-08-2016
    Location
    Munich, Germany
    MS-Off Ver
    16.0.7167.2060
    Posts
    6

    Re: Counting Dates in a Single Cell

    Neither of the two options suggested have worked on my computer. My computer doesn't like the brackets marked in the formula =LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1, And it doesn't like the brackets marked in the other formula, =1+(len(b2)-len(substitute(b2,if(iserror(find(",",b2))," ",","),""))).

    I'm guessing that red marked brackets are wrong.
    Last edited by Rol.S; 10-08-2016 at 07:27 PM.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting Dates in a Single Cell

    Quote Originally Posted by Rol.S View Post
    My computer doesn't like the brackets marked in the formula
    What exactly does that mean?

    Here's your file with the formula implemented.
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Counting Dates in a Single Cell

    Tested with commas and spaces:
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    08-19-2014
    Location
    Dallas, TX
    MS-Off Ver
    Office2013
    Posts
    44

    Re: Counting Dates in a Single Cell

    This works for the sample provided:

    =(LEN(B2)+1)/3

  15. #15
    Registered User
    Join Date
    10-08-2016
    Location
    Munich, Germany
    MS-Off Ver
    16.0.7167.2060
    Posts
    6

    Re: Counting Dates in a Single Cell

    Just before I declare that this problem has been solved, please look very closely at the formulas.

    This is Ben’s formula: =1+(LEN(B2)-LEN(SUBSTITUTE(B2;IF(ISERROR(FIND(",";B2));" ";",");""))).

    Please note the semi-colons that are in the formula, but not as written in the posting.

    And it is the same with Tony’s posting: =LEN(D15)-LEN(SUBSTITUTE(D15;" ";""))+1.

    Both of the formulas work with the semi-colons, so this is a resounding solved.

    Can someone tick solved for me; I'm not sure how to do it?

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Counting Dates in a Single Cell

    Mainland Europe is different from the rest of the world. You use ; as argument separators. Everyone else uses ,

  17. #17
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting Dates in a Single Cell

    I based my formula on the ones in the first post which used commas as separators.

    Glad we finally got this solved!

    Thanks for the feedback!

  18. #18
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting Dates in a Single Cell

    Quote Originally Posted by Rol.S View Post
    Can someone tick solved for me; I'm not sure how to do it?
    We can't do that. Only the person that started the thread can do that.

    Immediately above post #1 there is a menu bar.

    From that menu bar select Thread Tools then select Mark this thread as solved...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Counting Unique Characters Within a Single Cell
    By camsbigblue in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-15-2015, 07:26 PM
  2. dates from many cells to single cell
    By puttu in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 10-18-2013, 01:19 PM
  3. [SOLVED] Counting Numbers within a Single Cell
    By VT_2000 in forum Excel General
    Replies: 5
    Last Post: 05-02-2012, 06:23 PM
  4. Counting Consecutive Dates As A Single Occasion
    By SamuelT in forum Excel General
    Replies: 4
    Last Post: 02-16-2011, 06:23 AM
  5. Counting multiple values in a single cell
    By ac8038 in forum Excel General
    Replies: 8
    Last Post: 06-20-2006, 09:30 AM
  6. [SOLVED] dates in single cell
    By vshammer in forum Excel General
    Replies: 1
    Last Post: 09-05-2005, 09:05 PM
  7. [SOLVED] Counting values and comparing them to a single cell
    By Tbentsen in forum Excel General
    Replies: 3
    Last Post: 08-04-2005, 06:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1