I need to change date abbreviations in a formula to match customer's dates
ie: Apr 06 change to AL 06, can this be done?
--
Cindy T

Can you explain how the Apr 06 is arrived at? Is this a formula
returning a date which is formatted to show as Apr 06, or is it a text
value?

Pete

On Nov 25, 6:29*pm, Cindy at PWIC
wrote:
I need to change date abbreviations in a formula to match customer's dates
ie: *Apr 06 change to AL 06, can this be done?
--
Cindy T

I am putting in the date as a formula and adding 180 days to calculate a best
before date but my customer has changed their date codes from Apr to AR and I
don't know how to change my dates as excell will not recognize AR as a date.
I thought I could change it by saying replace any Apr to AR but I want it to
do it automatically as I this changes every month and I use this form every
day. Thanks. Not sure if this answers your question.
--
Cindy T


"Cindy at PWIC" wrote:

I need to change date abbreviations in a formula to match customer's dates
ie: Apr 06 change to AL 06, can this be done?
--
Cindy T

Can you tell us what your customer's month codes for each month are? Also,
showing us your formula would be useful too.

--
Rick (MVP - Excel)


"Cindy at PWIC" wrote in message
...
I am putting in the date as a formula and adding 180 days to calculate a
best
before date but my customer has changed their date codes from Apr to AR
and I
don't know how to change my dates as excell will not recognize AR as a
date.
I thought I could change it by saying replace any Apr to AR but I want it
to
do it automatically as I this changes every month and I use this form
every
day. Thanks. Not sure if this answers your question.
--
Cindy T


"Cindy at PWIC" wrote:

I need to change date abbreviations in a formula to match customer's
dates
ie: Apr 06 change to AL 06, can this be done?
--
Cindy T

January JA
February FE
March MR
April AL
May MA
June JN
July JL
August AU
September SE
October OC
November NO
December DE



In cell A1 is today's date plus 180 days (2009 May 25)which is like this:
=A2=180

In cell A2 is today's date in julian code (330) which is like this:
=TEXT((TODAY()-DATEVALUE("1/1/"&TEXT(TODAY(),"YY"))+1),"000")

I need the A1 cell to read 2009 MA 25

Hope this helps
Thanks,
Cindy
--
Cindy T


"Rick Rothstein" wrote:

Can you tell us what your customer's month codes for each month are? Also,
showing us your formula would be useful too.

--
Rick (MVP - Excel)


"Cindy at PWIC" wrote in message
...
I am putting in the date as a formula and adding 180 days to calculate a
best
before date but my customer has changed their date codes from Apr to AR
and I
don't know how to change my dates as excell will not recognize AR as a
date.
I thought I could change it by saying replace any Apr to AR but I want it
to
do it automatically as I this changes every month and I use this form
every
day. Thanks. Not sure if this answers your question.
--
Cindy T


"Cindy at PWIC" wrote:

I need to change date abbreviations in a formula to match customer's
dates
ie: Apr 06 change to AL 06, can this be done?
--
Cindy T

I should have asked you for your formula, but no matter. Just put your
formula (minus the leading equal sign) in where I have indicated in the
formulas below.

You will not be able to produce a "real" date using those codes; rather, you
will only be able to produce a text string that looks like a date. Here is a
general function call that will produce the month code for the specified
date...

=CHOOSE(MONTH(<<date),"JA","FE","MR","AL","MA"," JN","JL","AU","SE","OC","NO","DE")

where you would put your formula that returns a date in for my <<date
place holder. To create a date-looking string from this, just concatenate on
(in whatever order you want) the day and year parts. For example, to produce
a date in this format, ddmmyyyy, such as 25NO2008, use a construction like
this...

=TEXT(<<date,"dd")&CHOOSE(MONTH(<<date),"JA"," FE","MR","AL","MA","JN","JL","AU","SE","OC","NO"," DE")&YEAR(<<date)

--
Rick (MVP - Excel)



"Cindy at PWIC" wrote in message
...
January JA
February FE
March MR
April AL
May MA
June JN
July JL
August AU
September SE
October OC
November NO
December DE



In cell A1 is today's date plus 180 days (2009 May 25)which is like this:
=A2=180

In cell A2 is today's date in julian code (330) which is like this:
=TEXT((TODAY()-DATEVALUE("1/1/"&TEXT(TODAY(),"YY"))+1),"000")

I need the A1 cell to read 2009 MA 25

Hope this helps
Thanks,
Cindy
--
Cindy T


"Rick Rothstein" wrote:

Can you tell us what your customer's month codes for each month are?
Also,
showing us your formula would be useful too.

--
Rick (MVP - Excel)


"Cindy at PWIC" wrote in message
...
I am putting in the date as a formula and adding 180 days to calculate a
best
before date but my customer has changed their date codes from Apr to AR
and I
don't know how to change my dates as excell will not recognize AR as a
date.
I thought I could change it by saying replace any Apr to AR but I want
it
to
do it automatically as I this changes every month and I use this form
every
day. Thanks. Not sure if this answers your question.
--
Cindy T


"Cindy at PWIC" wrote:

I need to change date abbreviations in a formula to match customer's
dates
ie: Apr 06 change to AL 06, can this be done?
--
Cindy T

Suppose you put that table in cells D1:E12. Then in A1 you could have
this formula:

=TEXT(TODAY()+180,"yyyy ") & VLOOKUP(TEXT(TODAY()+180,"mmmm"),D$1:E
$12,2,0) & TEXT(TODAY()+180," dd")

which will give you:

2009 MA 24

If you want it to show 25th May 2009 you will need to use 181 instead
of 180 in the formula.

Hope this helps.

Pete

On Nov 25, 10:47*pm, Cindy at PWIC
wrote:
January * * * *JA
February * * *FE
March * * * * *MR
April * * * * * *AL
May * * * * * * MA
June * * * * * *JN
July * * * * * * JL
August * * * * AU
September * SE
October * * * *OC
November * * NO
December * * DE

In cell A1 is today's date plus 180 days (2009 May 25)which is like this:
=A2=180

In cell A2 is today's date in julian code (330) which is like this:
=TEXT((TODAY()-DATEVALUE("1/1/"&TEXT(TODAY(),"YY"))+1),"000")

I need the A1 cell to read 2009 MA 25

Hope this helps
Thanks,
Cindy
--
Cindy T



"Rick Rothstein" wrote:
Can you tell us what your customer's month codes for each month are? Also,
showing us your formula would be useful too.

--
Rick (MVP - Excel)

"Cindy at PWIC" wrote in message
...
I am putting in the date as a formula and adding 180 days to calculate a
best
before date but my customer has changed their date codes from Apr to AR
and I
don't know how to change my dates as excell will not recognize AR as a
date.
I thought I could change it by saying replace any Apr to AR but I want it
to
do it automatically as I this changes every month and I use this form
every
day. *Thanks. *Not sure if this answers your question.
--
Cindy T

"Cindy at PWIC" wrote:

I need to change date abbreviations in a formula to match customer's
dates
ie: *Apr 06 change to AL 06, can this be done?
--
Cindy T- Hide quoted text -

- Show quoted text -

Excellent, it worked. Thank you very much. You have no idea how long I have
been trying to do this. Thanks again.
--
Cindy T


"Pete_UK" wrote:

Suppose you put that table in cells D1:E12. Then in A1 you could have
this formula:

=TEXT(TODAY()+180,"yyyy ") & VLOOKUP(TEXT(TODAY()+180,"mmmm"),D$1:E
$12,2,0) & TEXT(TODAY()+180," dd")

which will give you:

2009 MA 24

If you want it to show 25th May 2009 you will need to use 181 instead
of 180 in the formula.

Hope this helps.

Pete

On Nov 25, 10:47 pm, Cindy at PWIC
wrote:
January JA
February FE
March MR
April AL
May MA
June JN
July JL
August AU
September SE
October OC
November NO
December DE

In cell A1 is today's date plus 180 days (2009 May 25)which is like this:
=A2=180

In cell A2 is today's date in julian code (330) which is like this:
=TEXT((TODAY()-DATEVALUE("1/1/"&TEXT(TODAY(),"YY"))+1),"000")

I need the A1 cell to read 2009 MA 25

Hope this helps
Thanks,
Cindy
--
Cindy T



"Rick Rothstein" wrote:
Can you tell us what your customer's month codes for each month are? Also,
showing us your formula would be useful too.

--
Rick (MVP - Excel)

"Cindy at PWIC" wrote in message
...
I am putting in the date as a formula and adding 180 days to calculate a
best
before date but my customer has changed their date codes from Apr to AR
and I
don't know how to change my dates as excell will not recognize AR as a
date.
I thought I could change it by saying replace any Apr to AR but I want it
to
do it automatically as I this changes every month and I use this form
every
day. Thanks. Not sure if this answers your question.
--
Cindy T

"Cindy at PWIC" wrote:

I need to change date abbreviations in a formula to match customer's
dates
ie: Apr 06 change to AL 06, can this be done?
--
Cindy T- Hide quoted text -

- Show quoted text -

It works, thanks a lot. I should have asked for help sooner. i will know
for next time. Thanks again.
--
Cindy T


"Rick Rothstein" wrote:

I should have asked you for your formula, but no matter. Just put your
formula (minus the leading equal sign) in where I have indicated in the
formulas below.

You will not be able to produce a "real" date using those codes; rather, you
will only be able to produce a text string that looks like a date. Here is a
general function call that will produce the month code for the specified
date...

=CHOOSE(MONTH(<<date),"JA","FE","MR","AL","MA"," JN","JL","AU","SE","OC","NO","DE")

where you would put your formula that returns a date in for my <<date
place holder. To create a date-looking string from this, just concatenate on
(in whatever order you want) the day and year parts. For example, to produce
a date in this format, ddmmyyyy, such as 25NO2008, use a construction like
this...

=TEXT(<<date,"dd")&CHOOSE(MONTH(<<date),"JA"," FE","MR","AL","MA","JN","JL","AU","SE","OC","NO"," DE")&YEAR(<<date)

--
Rick (MVP - Excel)



"Cindy at PWIC" wrote in message
...
January JA
February FE
March MR
April AL
May MA
June JN
July JL
August AU
September SE
October OC
November NO
December DE



In cell A1 is today's date plus 180 days (2009 May 25)which is like this:
=A2=180

In cell A2 is today's date in julian code (330) which is like this:
=TEXT((TODAY()-DATEVALUE("1/1/"&TEXT(TODAY(),"YY"))+1),"000")

I need the A1 cell to read 2009 MA 25

Hope this helps
Thanks,
Cindy
--
Cindy T


"Rick Rothstein" wrote:

Can you tell us what your customer's month codes for each month are?
Also,
showing us your formula would be useful too.

--
Rick (MVP - Excel)


"Cindy at PWIC" wrote in message
...
I am putting in the date as a formula and adding 180 days to calculate a
best
before date but my customer has changed their date codes from Apr to AR
and I
don't know how to change my dates as excell will not recognize AR as a
date.
I thought I could change it by saying replace any Apr to AR but I want
it
to
do it automatically as I this changes every month and I use this form
every
day. Thanks. Not sure if this answers your question.
--
Cindy T


"Cindy at PWIC" wrote:

I need to change date abbreviations in a formula to match customer's
dates
ie: Apr 06 change to AL 06, can this be done?
--
Cindy T

You're welcome, Cindy - thanks for feeding back.

Pete

On Nov 26, 4:23*pm, Cindy at PWIC
wrote:
Excellent, it worked. *Thank you very much. *You have no idea how long I have
been trying to do this. *Thanks again.
--
Cindy T

You are most welcome. Yes, if you try to solve a problem and have trouble
doing so, feel free to post your problem/question on one of these Excel
newsgroups and I am sure someone here will be more than happy to try and
help you out.

--
Rick (MVP - Excel)


"Cindy at PWIC" wrote in message
...
It works, thanks a lot. I should have asked for help sooner. i will know
for next time. Thanks again.
--
Cindy T


"Rick Rothstein" wrote:

I should have asked you for your formula, but no matter. Just put your
formula (minus the leading equal sign) in where I have indicated in the
formulas below.

You will not be able to produce a "real" date using those codes; rather,
you
will only be able to produce a text string that looks like a date. Here
is a
general function call that will produce the month code for the specified
date...

=CHOOSE(MONTH(<<date),"JA","FE","MR","AL","MA"," JN","JL","AU","SE","OC","NO","DE")

where you would put your formula that returns a date in for my <<date
place holder. To create a date-looking string from this, just concatenate
on
(in whatever order you want) the day and year parts. For example, to
produce
a date in this format, ddmmyyyy, such as 25NO2008, use a construction
like
this...

=TEXT(<<date,"dd")&CHOOSE(MONTH(<<date),"JA"," FE","MR","AL","MA","JN","JL","AU","SE","OC","NO"," DE")&YEAR(<<date)

--
Rick (MVP - Excel)



"Cindy at PWIC" wrote in message
...
January JA
February FE
March MR
April AL
May MA
June JN
July JL
August AU
September SE
October OC
November NO
December DE



In cell A1 is today's date plus 180 days (2009 May 25)which is like
this:
=A2=180

In cell A2 is today's date in julian code (330) which is like this:
=TEXT((TODAY()-DATEVALUE("1/1/"&TEXT(TODAY(),"YY"))+1),"000")

I need the A1 cell to read 2009 MA 25

Hope this helps
Thanks,
Cindy
--
Cindy T


"Rick Rothstein" wrote:

Can you tell us what your customer's month codes for each month are?
Also,
showing us your formula would be useful too.

--
Rick (MVP - Excel)


"Cindy at PWIC" wrote in
message
...
I am putting in the date as a formula and adding 180 days to
calculate a
best
before date but my customer has changed their date codes from Apr to
AR
and I
don't know how to change my dates as excell will not recognize AR as
a
date.
I thought I could change it by saying replace any Apr to AR but I
want
it
to
do it automatically as I this changes every month and I use this
form
every
day. Thanks. Not sure if this answers your question.
--
Cindy T


"Cindy at PWIC" wrote:

I need to change date abbreviations in a formula to match
customer's
dates
ie: Apr 06 change to AL 06, can this be done?
--
Cindy T