Converting selected section of text to numbers

R

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
 
K

Kassie

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
 
R

Rob

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

Rob
 
R

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

Rob

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

Rob
 
Top