type date and year has to come.

i have a field like:-

Name of Name of Date Year Qty Rate Amt.
Party Co.

what i want is when i type date i want year has to come
automatically.
E.G.
If i type date from 01.04.1998 to 31.03.1999 i.e. (account
year Date) it should automatically type in year field 1998-
99 from 01.04.2000 to 31.03.2001 than year field should
have automatically 2000-01

is any function is there. please help me.

****al

With your date range in C2 (in the format: 01.04.1998 to
31.03.1999):

=RIGHT(LEFT(C2,FIND(" ",C2)-1),4)&"-"&RIGHT(C2,2)

HTH
Jason
Atlanta, GA

-----Original Message-----
i have a field like:-

Name of Name of Date Year Qty Rate Amt.
Party Co.

what i want is when i type date i want year has to come
automatically.
E.G.
If i type date from 01.04.1998 to 31.03.1999 i.e.
(account
year Date) it should automatically type in year field
1998-
99 from 01.04.2000 to 31.03.2001 than year field should
have automatically 2000-01

is any function is there. please help me.

****al
.

Make a vlookup table with the first accounting date of the year i.e.

01.04.1998 1998-99
01.04.1999 1999-00
01.04.2000 2000-01
01.04.2001 2001-02
and so on......

then us a formula like

=VLOOKUP(C2,F2:G10,2)

where C2 is the date and F2:G10 is the table dimension

--

Regards,

Peo Sjoblom


"****al" wrote in message
...
i have a field like:-

Name of Name of Date Year Qty Rate Amt.
Party Co.

what i want is when i type date i want year has to come
automatically.
E.G.
If i type date from 01.04.1998 to 31.03.1999 i.e. (account
year Date) it should automatically type in year field 1998-
99 from 01.04.2000 to 31.03.2001 than year field should
have automatically 2000-01

is any function is there. please help me.

****al

sorry Jason but it's not working. plz given more details.
Thanks for ur ans.
****al
-----Original Message-----
With your date range in C2 (in the format: 01.04.1998 to
31.03.1999):

=RIGHT(LEFT(C2,FIND(" ",C2)-1),4)&"-"&RIGHT(C2,2)

HTH
Jason
Atlanta, GA

-----Original Message-----
i have a field like:-

Name of Name of Date Year Qty Rate Amt.
Party Co.

what i want is when i type date i want year has to come
automatically.
E.G.
If i type date from 01.04.1998 to 31.03.1999 i.e.
(account
year Date) it should automatically type in year field
1998-
99 from 01.04.2000 to 31.03.2001 than year field should
have automatically 2000-01

is any function is there. please help me.

****al
.

.

Of course I am assuming that your regional setting are set for
dd.mm.yyyy date format..

--

Regards,

Peo Sjoblom


"Peo Sjoblom" wrote in message
...
Make a vlookup table with the first accounting date of the year i.e.

01.04.1998 1998-99
01.04.1999 1999-00
01.04.2000 2000-01
01.04.2001 2001-02
and so on......

then us a formula like

=VLOOKUP(C2,F2:G10,2)

where C2 is the date and F2:G10 is the table dimension

--

Regards,

Peo Sjoblom


"****al" wrote in message
...
i have a field like:-

Name of Name of Date Year Qty Rate Amt.
Party Co.

what i want is when i type date i want year has to come
automatically.
E.G.
If i type date from 01.04.1998 to 31.03.1999 i.e. (account
year Date) it should automatically type in year field 1998-
99 from 01.04.2000 to 31.03.2001 than year field should
have automatically 2000-01

is any function is there. please help me.

****al

thanks for ur ans.
i want if i type date between 01.04.1998 to 31.03.1999 it
should take 1998-99 like 01.04.1999 to 31.03.2000 than
1999-00

-----Original Message-----
Make a vlookup table with the first accounting date of
the year i.e.

01.04.1998 1998-99
01.04.1999 1999-00
01.04.2000 2000-01
01.04.2001 2001-02
and so on......

then us a formula like

=VLOOKUP(C2,F2:G10,2)

where C2 is the date and F2:G10 is the table dimension

--

Regards,

Peo Sjoblom


"****al" wrote in message
...
i have a field like:-

Name of Name of Date Year Qty Rate Amt.
Party Co.

what i want is when i type date i want year has to come
automatically.
E.G.
If i type date from 01.04.1998 to 31.03.1999 i.e.
(account
year Date) it should automatically type in year field
1998-
99 from 01.04.2000 to 31.03.2001 than year field should
have automatically 2000-01

is any function is there. please help me.

****al


.

That is what it does.. If you put a [date] (I am not talking about text
here,
you have to make sure you put in a date that excel recognizes as date) it
will
lookup a date like October 8th 2002 it will lookup April 1st 2002 in the
table and return
2002-03, just remember that you have to use real dates for this to work, if
your dates are
left aligned in the cell they are text, then you would need Jason's
solution.
If they are text I suggest you change them to dates (change the lookup table
dates as well
of course). Maybe your date format is 01-04-2002 or something, you find that
by
starting the control panel and looking at regional settings..

--

Regards,

Peo Sjoblom


"****al" wrote in message
...
thanks for ur ans.
i want if i type date between 01.04.1998 to 31.03.1999 it
should take 1998-99 like 01.04.1999 to 31.03.2000 than
1999-00

-----Original Message-----
Make a vlookup table with the first accounting date of
the year i.e.

01.04.1998 1998-99
01.04.1999 1999-00
01.04.2000 2000-01
01.04.2001 2001-02
and so on......

then us a formula like

=VLOOKUP(C2,F2:G10,2)

where C2 is the date and F2:G10 is the table dimension

--

Regards,

Peo Sjoblom


"****al" wrote in message
...
i have a field like:-

Name of Name of Date Year Qty Rate Amt.
Party Co.

what i want is when i type date i want year has to come
automatically.
E.G.
If i type date from 01.04.1998 to 31.03.1999 i.e.
(account
year Date) it should automatically type in year field
1998-
99 from 01.04.2000 to 31.03.2001 than year field should
have automatically 2000-01

is any function is there. please help me.

****al


.