Date Breakdown into Columns

[Justinian]

I run a report every morning for moving dates. For example, today's report is 6/2-6/5. I want Excel to recognize each unique date and place those dates starting in E11. So for today, I want Excel to put "2nd" in E11, "3rd" in E12, "4th" in E13, and "15th" in E14. Tomorrow's report will be 6/3-6/9 so there will not always be just four (4) dates.

How do I get Excel to do this?

 

[jasonb75]

Doesn't need an AppData folder, it can go in any folder.

I don't know how far back you will need to go to fix this now, moving the file after installing it was the issue. Normal practice would be to extract the zip file directly to its intended location rather than dumping it on the desktop and moving it later.

I suspect that you might need to go back into add-ins and remove it from the list, then browse for it again, add it, then repeat the steps XL2BB Disappears when Excel is Reopened

 

[Justinian]

I unzipped to a folder, right-clicked, and selected "unblock" under general. When I open my Excel file, nothing appears. How is this supposed to work?

 

[Justinian]

Do I open my file then open the add-in?

 

[jasonb75]

You add it in the add-in menu as per the instructions, then it will be locked into the ribbon until you do something to remove it.

You've already done that once but because you moved the file afterwards, excel has lost sight of it so you need to repeat the process.

 

[Justinian]

My goodness, I finally got it working. Thank you for your patience.

Here is the code. I have to (2) options for counting dates (6/3, 6/4, 6/5) versus 3rd, 4th, 5th, etc.

Move-In Report.xlsx
	A		C	D	E	F	G
11	Building/Unit		Scheduled Move-In Date		Option 1
12	1564		06/03/20		6/3	-	13
13	2105		06/03/20		6/4	-	6
14	3310B		06/03/20		6/5	-	4
15	3400D		06/03/20		6/6	-	7
16	3403M		06/03/20		6/7	-	1
17	3409H		06/03/20		6/8	-	1
18	3411J		06/03/20		6/9	-	6
19	3609E		06/03/20
20	4111C		06/03/20		Option 2
21	4702D		06/03/20		3rd	-	0
22	4705C		06/03/20		4th	-	0
23	4904M		06/03/20		5th	-	0
24	5009A		06/03/20		6th	-	0
25	1556		06/04/20		7th	-	0
26	3240		06/04/20		8th	-	0
27	3505M		06/04/20		9th	-	0
28	3612K		06/04/20
29	3909H		06/04/20
30	4308E		06/04/20
31	919		06/05/20
32	1412		06/05/20
33	3909D		06/05/20
34	4409J		06/05/20
35	961		06/06/20
36	2238		06/06/20
37	3231		06/06/20
38	3704C		06/06/20
39	3804A		06/06/20
40	4506C		06/06/20
41	4604G		06/06/20
42	3807C		06/07/20
43	1953		06/08/20
44	950		06/09/20
45	1217		06/09/20
Sheet1

Cell Formulas
Range	Formula
G12:G18,G21:G27	G12	=COUNTIF(C:C,E12)
F12:F19,F21:F27	F12	=IF(E12=FALSE,"","-")

 

[jasonb75]

Ok, now that I see it, how does the date range that you asked for in post 1 related to the example above?

How do we know if it should cover the date range 6/2-6/5 or 6/3-6/9?

Do you want to use all dates in column C, or is the range specified somewhere?

 

[Justinian]

What I wanted to do is have Excel count the dates so I do not have to do it by hand. Every morning when I run this report, there will be varied number of rows with units and dates. If I run the report on Monday, the dates will be for that Monday through Thursday; if I run the report on Tuesday, the dates will be for that Tuesday through Friday; if i run the report on Wednesday, Thursday, or Friday, the dates will be for that day through the following Tuesday.

So, today when I ran the report, it was for 6/3-6/9/20 and I pasted the codes/dates as you see above. What I want is to have Excel look at the dates in column C and list them in column E, with a count of those dates in column G.

 

[jasonb75]

Personally, I would use a pivot table to count the dates.

It could be done with formulas, but in my opinion that would be making a simple task more complicated than needed.

 

[Toadstool]

Justinian2.xlsx
	A	B	C	D	E	F	G
11	Building/Unit		Scheduled Move-In Date		Result
12	1564		6/3/2020		6/3	-	13
13	2105		6/3/2020		6/4	-	6
14	3310B		6/3/2020		6/5	-	4
15	3400D		6/3/2020		6/6	-	7
16	3403M		6/3/2020		6/7	-	1
17	3409H		6/3/2020		6/8	-	1
18	3411J		6/3/2020		6/9	-	2
19	3609E		6/3/2020
20	4111C		6/3/2020
21	4702D		6/3/2020
22	4705C		6/3/2020
23	4904M		6/3/2020
24	5009A		6/3/2020
25	1556		6/4/2020
26	3240		6/4/2020
27	3505M		6/4/2020
28	3612K		6/4/2020
29	3909H		6/4/2020
30	4308E		6/4/2020
31	919		6/5/2020
32	1412		6/5/2020
33	3909D		6/5/2020
34	4409J		6/5/2020
35	961		6/6/2020
36	2238		6/6/2020
37	3231		6/6/2020
38	3704C		6/6/2020
39	3804A		6/6/2020
40	4506C		6/6/2020
41	4604G		6/6/2020
42	3807C		6/7/2020
43	1953		6/8/2020
44	950		6/9/2020
45	1217		6/9/2020
Sheet2 (2)

Cell Formulas
Range	Formula
F12:F21	F12	=IF(E12="","","-")
G12:G21	G12	=IF(E12="","",COUNTIF(C:C,E12))
E12:E25	E12	=IFERROR(IF(ROWS($E$11:E11)<2,MIN($C$12:$C$99),AGGREGATE(15,6,$C$12:$C$99/($C$12:$C$99>E11),1)),"")

 

[jasonb75]

Shorter formula for use with excel 2019 or office 365, but I still prefer the pivot.

Book2
	E	F	G
1	Result
2	03/06/2020	-	13
3	04/06/2020	-	6
4	05/06/2020	-	4
5	06/06/2020	-	7
6	07/06/2020	-	1
7	08/06/2020	-	1
8	09/06/2020	-	2
9
Sheet4

Cell Formulas
Range	Formula
E2:E9	E2	=IF(OR(E1="",E1=MAX(C:C)),"",MINIFS(C:C,C:C,">"&N(E1)))
F2:F9	F2	=IF(E2="","","-")
G2:G9	G2	=IF(E2="","",COUNTIF(C:C,E2))