Splitting a Date Range

Glasgowsmile

Active Member
Joined
Apr 14, 2018
Messages
269
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHI
1
2
3August 29, 2021 - September 04, 2021August 29, 2021 ptember 04, 202144437#VALUE!
4
5
6
Sheet1
Cell Formulas
RangeFormula
E3E3=LEFT($B3, FIND("-", $B3)-1)
F3F3=RIGHT($B3,FIND("-",$B3)-1)
G3:H3G3=DATEVALUE(E3)
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,491
Office Version
  1. 365
Platform
  1. Windows
Try this formula in F3:
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,681
Office Version
  1. 365
Platform
  1. Windows
Try
Excel Formula:
=MID($B3,FIND("-",$B3)+1,100)
 
Solution

Glasgowsmile

Active Member
Joined
Apr 14, 2018
Messages
269
Office Version
  1. 365
Platform
  1. Windows
Try this formula in F3:
Excel Formula:
=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
ABCDEFGHI
1
2
3August 29, 2021 - October, 21 2021August 29, 2021 - October, 21 202144437#VALUE!
4
5
Sheet1
Cell Formulas
RangeFormula
E3E3=LEFT($B3, FIND("-", $B3)-1)
F3F3=TRIM(RIGHT($B3,FIND("-",$B3)+1))
G3:H3G3=DATEVALUE(E3)
 

Glasgowsmile

Active Member
Joined
Apr 14, 2018
Messages
269
Office Version
  1. 365
Platform
  1. Windows
Try
Excel Formula:
=MID($B3,FIND("-",$B3)+1,100)
This appears to work great, if you have time can you explain what's happening here?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,491
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,681
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,491
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,491
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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
BCDEF
3August 29, 2021 - September 04, 2021August 29, 2021September 04, 2021
4August 29, 2021September 04, 2021
Sheet978
Cell Formulas
RangeFormula
E3E3=LEFT($B3, FIND("-", $B3)-1)+0
F3F3=TRIM(MID($B3,FIND("-",$B3)+1,100))+0
E4E4=DATEVALUE(LEFT($B3,FIND("-",$B3)-1))
F4F4=DATEVALUE(MID($B3,FIND("-",$B3)+1,100))
 

Forum statistics

Threads
1,175,955
Messages
5,900,528
Members
434,835
Latest member
cmenconi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top