Connect dates and count

[JonnyMan]

Hello,
I'm new to excel formulas and VBA as well as a first time poster. I looked in the forum for an answer to my question and couldn't find out. It seems a bit complicated so hopefully I can describe it simply enough. Please see attached sample as well.

I am trying to find the number of contacts from the time an application was created. Column A has the 'app created date'. Column B has all the dates a contact was made for the created date. Each date constitutes 1 contact. I wasn't sure if it was possible to do this since all the dates in B are within one cell. Will I need to separate the dates into difference cells in order to do this?

Please let me know if this needs further explanation.

 

[David Evans]

Does each App Created Date reflect a separate App? That would appear to be the case, but it's not stated.
The guys around here can come up with some truly amazing formulas, but I honestly believe you'd be better off having at least an App Name, App Created Date, Contact Date (one for each record) and derive the Number of Contacts from the dataset.
You could achieve it by counting the number of delimiters ("-") in the Date of Contacts field +1, but it's not exactly an elegant solution, and is likely to be less than robust. It's in the attached file for you ...

What else do you want to achieve with this data?

 

[Logit]

Highlight B1 to B5

Click DATA tab, TEXT TO COLUMNS, NEXT, OTHER

Insert a hyphen  -  in the small block

Click NEXT, FINISH

Highlight Columns B to O, then drag one of the columns until all dates are showing and there are no hash  #  marks

In P2, enter the following formula :  =COUNTA(B2:O2)

If the number 13 does not show in P2, right click P2, Select FORMAT CELLS, NUMBER, NUMBER (change to no decimal)

Close the FORMAT form

Click P2 again, click and hold the small dot, lower right corner and drag down to P5

 

[JonnyMan]

@Logit , @David Evans Thank you both for you help in this! Both of your answers provide the total number of dates in the Dates of Contacts cell. However, I would like to find the number of contacts that start at the date the application was created. B2:B4 have the first contact as the application contact but B5 has two contacts that occur prior to the app created date. I do not want to include these in the Number of Contacts.
Each App Created Date represents a different application.
Ultimately, this will allow me to know how many points of contact were made since the creation of the app and no more than that.

 

[David Evans]

In that case, you will need to break out the dates to one per record. It's possible that someone will come up with a formula, but it would be more complex than just breaking the date out.
The mantra with stuff like this is simplify, simplify, simplify - then you end up with worksheets that are capable of being understood - not everyone who comes in contact with your worksheet is a Ninja!

 

[bosco_yip]

Hi,

You have duplicated "app created date" in Column B, could you write down the desired result in Column C.

Regards

 

[NARAYANK991]

Hi,

You have duplicated "app created date" in Column B, could you write down the desired result in Column C.

Regards

Hi Bosco ,

From what I have understood , the results should be as follows :

C2 ....... 13

C3 ....... 11

C4 ....... 11

C5 ....... 12

C5 is 12 because two of the dates 03/15/2017 and 02/01/2017 are prior to the App Created Date of 03/19/2017 , and hence need to be excluded from the count.

I may be wrong.

Narayan

 

[bosco_yip]

Hi Bosco ,
From what I have understood , the results should be as follows :
C2 ....... 13
C3 ....... 11
C4 ....... 11
C5 ....... 12
C5 is 12 because two of the dates 03/15/2017 and 02/01/2017 are prior to the App Created Date of 03/19/2017 , and hence need to be excluded from the count.
Narayan

Thanks Narayan, then the formula maybe.....



In C2, formula copied down :

=LEN(LEFT(B2,LOOKUP(9^9,FIND(A2,B2,ROW($1:$250)))+9))-LEN(SUBSTITUTE(LEFT(B2,LOOKUP(9^9,FIND(A2,B2,ROW($1:$250)))+9),"-",""))+1

Regards
Bosco

 

[r2c2]

@JonnyMan

Yay! I found a data set for my next data from hell episode
I think this data structure is too complex. But that is the reality too. Fortunately, there is Power Query. You should install and use it as it can quickly give the answer you are after. If you have PQ (or Excel 2016), follow below steps.

1. Select and load your data to Query Editor. If prompted, create a table.
2. If PQ adds "Changed type" step remove it. Repeat this everytime PQ does it.
3. Add a custom column with the formula =Text.Split([Dates of Contacts],"-")
4. Expand this new column
5. Select the newly expanded column and first column (App created Date) and change them to date type using locale en-US
6. Add a conditional column to check if App Created Date <= custom using below settings
   
   
   
7. change this new column type to number
8. Go to Transform > Group By and group by App Created Date with sum of custom.1 column
   
   
   
9. Close and load this data to workbook.

 

[David Evans]

Great use of Power Query @r2c2 - elegant solution!

 

[JonnyMan]

Thank you, Everyone!
I'm going to look more into Power Query. That looks like a great tool.
@bosco_yip your formula looks like its what i need. I appreciate your help on this.

J

 

[JonnyMan]

@bosco_yip I actually have additional question to this. As I'm running your formula for the remaining date sets I'm getting #N/A for many of them. I've added some of them to this updated file.
Also, I have 2154 rows of data. Do I update the $1:$250 section to $1:$2154 ?
Thanks again!

 

[bosco_yip]

@bosco_yip I actually have additional question to this. As I'm running your formula for the remaining date sets I'm getting #N/A for many of them. I've added some of them to this updated file.
Also, I have 2154 rows of data. Do I update the $1:$250 section to $1:$2154 ?
Thanks again!

1] The #N/A in the attached file due to doesn't find matching "app created date".

2] ROW($1:$250) does not related to your no. of row used, it related to the no. of characters used in the cell.

The maximum no is ROW($1:$1024) and it become >>

=LEN(LEFT(B2,LOOKUP(9^9,FIND(A2,B2,ROW($1:$1024)))+9))-LEN(SUBSTITUTE(LEFT(B2,LOOKUP(9^9,FIND(A2,B2,ROW($1:$1024)))+9),"-",""))+1

Regards
Bosco

 

[JonnyMan]

1] The #N/A in the attached file due to doesn't find matching "app created date".

2] ROW($1:$250) does not related to your no. of row used, it related to the no. of characters used in the cell.

The maximum no is ROW($1:$1024) and it become >>

=LEN(LEFT(B2,LOOKUP(9^9,FIND(A2,B2,ROW($1:$1024)))+9))-LEN(SUBSTITUTE(LEFT(B2,LOOKUP(9^9,FIND(A2,B2,ROW($1:$1024)))+9),"-",""))+1

Regards
Bosco

I see, so the Dates of Contact needs to contain the App Created Date in order to match the data? I guess this formula won't work for many rows since the Dates of Contacts did not occur on the App Created Date.
Not sure if there is a workaround for this.

Thanks again for your help.
J

 

[David Evans]

@bosco_yip I actually have additional question to this. As I'm running your formula for the remaining date sets I'm getting #N/A for many of them. I've added some of them to this updated file.
Also, I have 2154 rows of data. Do I update the $1:$250 section to $1:$2154 ?
Thanks again!

@bosco_yip has created an innovative formula, however it is complex and requires a thorough knowledge of Excel to maintain it. It has lots of moving parts!

It comes down to this, would you ask your surgeon to show you how to perform an appendectomy and then go home and try it out on a friend? These guys have an encyclopedic knowledge of Excel and it can only be gained through diligent practice, trial & error.

There's a subtle difference between getting an answer and learning. Lots of people just want an answer (some even want their homework done for them!) That's OK; but I guess they should come with an implied caveat - you probably won't be able to fix it when it goes wrong.

If the purpose of a request is to learn, you learn best by doing - yes by all means ask for help when you run into a serious wall, but it's best to beat your head against it for a while.

In this case, I'd load Power Query and learn how to use it. I'm not denigrating bosco's formula, but there's the Chandoo paradox - you have to be a Ninja to understand it, and most of us are not Ninjas, so in many ways it doesn't help us.

BTW, some Greek fellow was far more succint than I.

“For the things we have to learn before we can do them, we learn by doing them.”
― Aristotle

 

[bosco_yip]

I see, so the Dates of Contact needs to contain the App Created Date in order to match the data? I guess this formula won't work for many rows since the Dates of Contacts did not occur on the App Created Date.
Not sure if there is a workaround for this.

Thanks again for your help.
J

1] Check this revised formula if it can work with you.

2] See attached revised file.

Regards
Bosco

 

[JonnyMan]

1] Check this revised formula if it can work with you.

2] See attached revised file.

Regards
Bosco

Thank you for all your work on this @bosco_yip It looks like your new formula works for the rows where the App Created Date happens before the earliest date in the Dates of Contacts cell. If the Dates of Contacts string starts earlier than the App Created Date, then the formula still counts those and unfortunately, those should not be counted.
For Instance:
A7....................C7
9/28/2016.........16 - should be 14 since there are two dates that occurred before the App Created Date.

 

[bosco_yip]

........
A7....................C7
9/28/2016.........16 - should be 14 since there are two dates that occurred before the App Created Date.

1] Please refer to above picture. ( from post#.16 attached copy)

2] A7 is 9/28/2016, B7 doesn't content A7 (the last date is 9/25/2016).

Regards
Bosco