Splitting a Date Range

[Glasgowsmile]

Good day,

I'm having issues separating out a date range so I can display them. It works for August but September isn't pulling correctly and I'm not sure what's wrong with it.

It appears to not work for February, November, and December as well.

Book1
	A	B	C	D	E	F	G	H	I
1
2
3		August 29, 2021 - September 04, 2021			August 29, 2021	ptember 04, 2021	44437	#VALUE!
4
5
6
Sheet1

Cell Formulas
Range	Formula
E3	E3	=LEFT($B3, FIND("-", $B3)-1)
F3	F3	=RIGHT($B3,FIND("-",$B3)-1)
G3:H3	G3	=DATEVALUE(E3)

 

[Joe4]

Try this formula in F3:

=TRIM(RIGHT($B3,FIND("-",$B3)+1))

Note that the other formulas are fine. You just need to choose a date format for cells G3 and H3.

 

[Fluff]

Try

=MID($B3,FIND("-",$B3)+1,100)

 

[Glasgowsmile]

Try this formula in F3:

=TRIM(RIGHT($B3,FIND("-",$B3)+1))

Note that the other formulas are fine. You just need to choose a date format for cells G3 and H3.

I had tried the +1 approach (without TRIM) and it worked for those months I was having issues with but then creates a new problem for other months. See below:

Book1
	A	B	C	D	E	F	G	H	I
1
2
3		August 29, 2021 - October, 21 2021			August 29, 2021	- October, 21 2021	44437	#VALUE!
4
5
Sheet1

Cell Formulas
Range	Formula
E3	E3	=LEFT($B3, FIND("-", $B3)-1)
F3	F3	=TRIM(RIGHT($B3,FIND("-",$B3)+1))
G3:H3	G3	=DATEVALUE(E3)

 

[Glasgowsmile]

Try

=MID($B3,FIND("-",$B3)+1,100)

This appears to work great, if you have time can you explain what's happening here?

 

[Joe4]

Go with the MID option that Fluff presented.
I should have chose that instead of continuing on with the RIGHT logic you tried to incorporate (my brain is not firing on all cylinders this morning).

 

[Fluff]

When using Right like you need to take into consideration the length of the string. if you had
May 29, 2021 - September 04, 2021

The hyphen is the 14 character, so you are taking the last 14 characters, not the 14th character onwards.

 

[Joe4]

When using Right like you need to take into consideration the length of the string. if you had
May 29, 2021 - September 04, 2021

The hyphen is the 14 character, so you are taking the last 14 characters, not the 14th character onwards.

Yeah, as soon as I saw your post, I had that Home Simpson "DOH!" moment of recognition.
It is one of the those things if someone asked me to do it from scratch, I probably would have done it right (instead of continuing on with the original method tried).

 

[Joe4]

This appears to work great, if you have time can you explain what's happening here?

The "FIND" function finds where the "-" is located in the string, and then you want to start at the characters AFTER that.
And then you just choose some large number of characters to make sure you get to the end (like 100 - it does not matter if you go over).

I would probably just add a TRIM to remove any spaces between the "-" and the first letter of the month, i.e.

=TRIM(MID($B3,FIND("-",$B3)+1,100))

The DATEVALUE function will work on it, with or without the space, I just think it look a little better (just my own personal preference).

 

[jtakw]

Hi,

I'm just curious, do you really need separate formulas for G:H ?
Why not just convert the results to Dates in 1 shot ?

Note: You Only need either E3:F3 Or E4:F4, Not Both, just showing 2 ways:

Book3.xlsx
	B	C	D	E	F
3	August 29, 2021 - September 04, 2021			August 29, 2021	September 04, 2021
4				August 29, 2021	September 04, 2021
Sheet978

Cell Formulas
Range	Formula
E3	E3	=LEFT($B3, FIND("-", $B3)-1)+0
F3	F3	=TRIM(MID($B3,FIND("-",$B3)+1,100))+0
E4	E4	=DATEVALUE(LEFT($B3,FIND("-",$B3)-1))
F4	F4	=DATEVALUE(MID($B3,FIND("-",$B3)+1,100))