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]

The simple version, original date range in E9, note that I've had to change it slightly to work with my UK regional settings.

Note that excel does not have capability to show 1st, 2nd, 3rd, etc. A custom format of d"th" has been used on the formula cells, with conditional formatting to correct days 1,2,3,21,22,23 and 31 as shown below. The first rule uses a format of d"rd" the second uses d"nd" and the third d"st"

Book5
	E
11	2nd
12	3rd
13	4th
14	5th
15
Sheet11

Cell Formulas
Range	Formula
E11	E11	=DATEVALUE(LEFT(E9,FIND("-",E9)-1))
E12:E15	E12	=IF(MAX(E$11:E11)=DATEVALUE(MID($E$9,FIND("-",$E$9)+1,5)),"",E11+1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E11:E15	Expression	=OR(DAY(E11)=3,DAY(E11)=23)	text	NO
E11:E15	Expression	=OR(DAY(E11)=2,DAY(E11)=22)	text	NO
E11:E15	Expression	=OR(DAY(E11)=1,DAY(E11)=21,DAY(E11)=31)	text	NO

 

[Toadstool]

Hi Justinian,

I don't think I'm understanding the rules.
Q1: Why is E14 the 15th? What decides that?
Q2: What denotes there will be 4 days or 7 days?

Here's today for 3 days:

Justinian.xlsx
	E
11	2nd
12	3rd
13	4th
Sheet1

Cell Formulas
Range	Formula
E11:E13	E11	=DAY(TODAY()+ROWS($E$11:$E11)-1)&MID("xxstndrdthththththththththththththththththstndrdthththththththst",(DAY(TODAY()+ROWS($E$11:$E11)-1)*2)+1,2)

 

[jasonb75]

@Toadstool I think that 15th was a typo. I just notice that I chopped the top off of the table in my reply earlier, I think that I'm following what the OP requires, can you improve?

Not sure of your regional settings, note that the date in E9 is June 2nd to June 5th (4 days), if you're using US settings then you would need to use 6/2-6/5 for the formula to recognise the dates correctly.

Book5
	E
9	2/6-5/6
10
11	2nd
12	3rd
13	4th
14	5th
15
Sheet11

Cell Formulas
Range	Formula
E11	E11	=DATEVALUE(LEFT(E9,FIND("-",E9)-1))
E12:E15	E12	=IF(MAX(E$11:E11)=DATEVALUE(MID($E$9,FIND("-",$E$9)+1,5)),"",E11+1)

 

[Justinian]

@Toadstool Yes, the 15th was a typo.

If the report is run on Monday or Tuesday, the dates have to run through Friday. If I run the report on Wednesday, Thursday, or Friday, the dates have to run through the following Tuesday.

@jasonb75 I forgot to mention that my dates are in column C. For example, C11:C:17 has the date 6/2/20; C18:C26 has the date 6/3/20.

 

[Justinian]

Of course, I have to COUNTIF the dates so instead of "2nd" or "3rd," column E could say 6/2/20, 6/3/20.

 

[jasonb75]

I forgot to mention that my dates are in column C. For example, C11:C:17 has the date 6/2/20; C18:C26 has the date 6/3/20

I'm not following, is that in place of E11, E12, E13 and E14 in your earlier post, or something else?

Please install the XL2BB add in (link below) and post a clear example showing what you already have in your sheet and what you want added with the formulas.
Please make sure that there is nothing else that you may have forgotten to mention so that we don't waste any more time on answers that are not what you want!

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Justinian]

The link says the add-in is native in Excel but not in mine.

 

[jasonb75]

Don't know where you've seen that, it's not native, the link gives you a guide of how to install and use the add in.

 

[Justinian]

It indicates this in the instructions you sent. The 3rd bullet says "Find XL2BB add-in in the Add-ins list and get the installation location in the location column" and I do not have XL2BB in my add-ins. When i click on the Download link, the file opens and when I click on the file but nothing happens and it is not visible in the add-ins either.