Converting selected section of text to numbers

[Rob]

Could someone please help me to extract some details from "1/10/2004
through 31/01/2005", which has been pasted into one cell.
I need a formula to extract the month, 10 and another formula to extract the
number 01
I then need to convert those calendar months to fiscal units (or fiscal
months), so 10 becomes 4 and 01 becomes 7.
Please note that these dates change and may not always have the same number
of characters. ie 1/10/2004 could be 11/10/2004, etc.
I need to be able to then do a calculation on those 2 fiscal units (eg
7-4=3).
I hope that's clear.

Rob

 

[R.VENKATARAMAN]

presume your dates have been entered as excel entries for e.g.

1/10/2004 which is 1 october 2004 is entred as 10/1/04.
you can format in any format you like e.g. 1-oct-04
suppose this date is in A1
in some other cell if you type the formula
=month(A1) you will get 10
then
=day(A1) will get 1
now you get the hang of it and can manipulate data as you like

 

[Guest]

The best would be to splti the entry into columns <Data, Text to Columns>,
select delimited, select space as the delimiter, set the format as date, and
then click on <Finish> You then have two columns containing dates, and a
centre column you may as well discard. You can then do the normal
calculations on dates

 

[Rob]

Thanks Kassie, I was hoping for a formula but I think I may be able use that
suggestion.

Rob

 

[Rob]

Sorry to be a nuisance, but the data in the ONE cell is:
"1/10/2004 through 1/01/2005" (without the quotes)
That is, it's not just one date but 2 dates with the word through in
between.
I need a formula to extract the 10 (October) and show the result as 4 (as
October is the 4th month in the fiscal year.)
Then I need another formula to extract the 01 (January) and show the result
as 07 (as January is the 7th month in the fiscal year.)

Rob

 

[R.VENKATARAMAN]

thedata and formulae are as follows

to make it general I have made dates also as two digits--------important

A B
C D E F
G
01/10/2004 through 01/01/2005 =MID(B5,4,2) =C5-6 =IF(D5<0,D5-6,D5)
=MID(B5,23,2) =F5-6 =IF(G5<0,G5+12,G5)

the result will be

A B C D
E F G
1/10/2004 through 1/01/2005 10 4 4 01 -5
7


Even if you enter some other text in A but in the same form (there is only
one space beteen the firstdate and <trhough> and between <through> and the
second date and dates are two digits) then also the fomula will work.

check whetehr this is ok.

 

[Rob]

Thankyou for that. I was able to use your idea and modify it to get what I
neeeded.

Rob