Date Range

J

Jing

Active Member
Joined
Feb 11, 2011
Messages
289
I have some dates that need to be separated...

example:
<TABLE style="WIDTH: 211pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=281 border=0><COLGROUP><COL style="WIDTH: 211pt; mso-width-source: userset; mso-width-alt: 10276" width=281><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 211pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=281 height=20>01/03/2011 - 31/03/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>01/04/2011 - 30/04/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>01/05/2011 - 31/05/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>01/06/2011 - 30/06/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>01/07/2011 - 31/07/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>01/08/2011 - 31/08/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>01/09/2011 - 30/09/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>01/10/2011 - 02/10/2011</TD></TR></TBODY></TABLE>

All the dates are in A2 - A9. The formating is always the same.
i was wondering if there was a formula or somthing that can take the first date and put it into say G2 and the second date into H2.

that way i can take the two dates and get the # of days for that range.

thank you in advance.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
G2: =--LEFT(A2,SEARCH(" - ",A2)-1)
H2: =--MID(A2,SEARCH(" - ",A2)+3,10)

Reformat the columns as dates.

Denis
 
Upvote 0
this almost worked :)
all of column G works well but this is what i am getting now...

<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=205 border=0><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 67pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=89 height=20>3/1/2011</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 87pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle width=116>#VALUE!</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=205 border=0><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 67pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=89 height=20>4/1/2011</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 87pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle width=116>#VALUE!</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=205 border=0><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 67pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=89 height=20>5/1/2011</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 87pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle width=116>#VALUE!</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>6/1/2011</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#VALUE!</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7/1/2011</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#VALUE!</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>8/1/2011</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#VALUE!</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>9/1/2011</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#VALUE!</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>10/1/2011</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>10/2/2011</TD></TR></TBODY></TABLE>
 
Upvote 0
Try Data ---- > Text To Column -----> delimited ----> check other (Uncheck rest) and mention " - " in textbox and click finish
 
Upvote 0
removing the -- from the formula fixed it... and it works great to parse the dates into the new columns. but for some reason i can't use any simple formula to deturmine the #'s of days in the range it gives me a Value error.

*********************************************************


I have thought of the text to column option but that doesn't auto update once the dates change i would have to keep doing that each time...


*********************************************************

so just need to figured out a way to find the # of days in each date rate and add them to I2 - I9

I would like to thank each of you for your help on this so far...
 
Upvote 0
Check to make sure your dates are all numbers and not text. If they are text, you will get errors.
 
Upvote 0
Jing said:
removing the -- from the formula fixed it... and it works great to parse the dates into the new columns. but for some reason i can't use any simple formula to deturmine the #'s of days in the range it gives me a Value error.
Click to expand...
Check to see if there are any spaces around the second date. If there are, you can adjust the second formula to get rid of them. Where you currently have
=(Formula without the --)
Change that to
=--TRIM((Formula without the --))

eg:

H2: =--TRIM(MID(A2,SEARCH(" - ",A2)+3,10))


Denis
 
Upvote 0
i have tried modifying the formula's to work with the date ranges.
it wasn't working correctly. i found that since excel likes things to be mm/dd/yyyy and not the other way around as it is labled in the original range it was not allowing me to calculate the # of days in the range.

******************************************************************************************************************************

Here was the solution:

original range A2 - A9
<TABLE style="WIDTH: 211pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=281 border=0><COLGROUP><COL style="WIDTH: 211pt; mso-width-source: userset; mso-width-alt: 10276" width=281><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 211pt; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=281 height=20>01/03/2011 - 31/03/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" height=20>01/04/2011 - 30/04/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" height=20>01/05/2011 - 31/05/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" height=20>01/06/2011 - 30/06/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" height=20>01/07/2011 - 31/07/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" height=20>01/08/2011 - 31/08/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" height=20>01/09/2011 - 30/09/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" height=20>01/10/2011 - 02/10/2011</TD></TR></TBODY></TABLE>

as labled it is Day, Month Year...

******************************************************************************************************************************

in G2 i have entered the following formula to counter act this issue
=VALUE(MID(A2,4,3)&LEFT(A2,2)&MID(A2,6,5))

this eliminated the value error and allows me to change the order of the date.

******************************************************************************************************************************

in H2 i have the following formula to do the same with the second date
=VALUE(MID(A2,17,3)&MID(A2,14,3)&MID(A2,20,4))

******************************************************************************************************************************

i can now use a simple h2-g2 formula to get the # of days...

thanks again for all the help :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
L
  • Solved
Help Please
2 3
Replies
21
Views
513
Asbestos_Jen
Asbestos_Jen
B
  • Solved
Remove Duplicates
Replies
3
Views
187
hagia_sofia
hagia_sofia
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
68
Bernie Galve
B
J
  • Question Question
Plot x axis with dates with non standard interval
Replies
1
Views
179
saboh12617
S

Forum statistics

Threads
1,224,522
Messages
6,179,299
Members
452,904
Latest member
CodeMasterX

Share this page

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
Back
Top