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

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


--
remove $$$ from email addresss to send email
===========

Rob <NA wrote in message ...
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

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" wrote:

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

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

Rob

"Kassie" wrote in message
...
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" wrote:

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

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" wrote in message
...
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


--
remove $$$ from email addresss to send email
===========

Rob <NA wrote in message ...
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

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.


--
remove $$$ from email addresss to send email
=============

Rob <NA wrote in message ...
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" wrote in message
...
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


--
remove $$$ from email addresss to send email
===========

Rob <NA wrote in message ...
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

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

Rob

"R.VENKATARAMAN" wrote in message
...
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.


--
remove $$$ from email addresss to send email
=============

Rob <NA wrote in message ...
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" wrote in message
...
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


--
remove $$$ from email addresss to send email
===========

Rob <NA wrote in message ...
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