C
Cindy at PWIC
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?
ie: Apr 06 change to AL 06, can this be done?
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Pete_UK
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
returning a date which is formatted to show as Apr 06, or is it a text
value?
Pete
C
Cindy at PWIC
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.
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.
R
Rick Rothstein
Can you tell us what your customer's month codes for each month are? Also,
showing us your formula would be useful too.
showing us your formula would be useful too.
C
Cindy at PWIC
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
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
R
Rick Rothstein
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>>)
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>>)
P
Pete_UK
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
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
C
Cindy at PWIC
Excellent, it worked. Thank you very much. You have no idea how long I have
been trying to do this. Thanks again.
been trying to do this. Thanks again.
C
Cindy at PWIC
It works, thanks a lot. I should have asked for help sooner. i will know
for next time. Thanks again.
for next time. Thanks again.
P
Pete_UK
You're welcome, Cindy - thanks for feeding back.
Pete
Pete
R
Rick Rothstein
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)
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 said:It works, thanks a lot. I should have asked for help sooner. i will know
for next time. Thanks again.
- Joined
- Dec 23, 2008
- Messages
- 1
- Reaction score
- 0
similar issue
I have a similar issue with formatting but mine needs to represent something with 15-20 days of shelf life and read in a different format..."09JA20B--" (year/month/day+text = B (or A depending on production location) + text - - for example...for January 1st, 2009 being 19 days. I have attached a copy of the "calendar" as well...as you can see we currently populate each day by hand...there's got to be a better way. I would love it if we could just take the day of year + X and have it calculate automatically. Any tips? Thanks.
I have a similar issue with formatting but mine needs to represent something with 15-20 days of shelf life and read in a different format..."09JA20B--" (year/month/day+text = B (or A depending on production location) + text - - for example...for January 1st, 2009 being 19 days. I have attached a copy of the "calendar" as well...as you can see we currently populate each day by hand...there's got to be a better way. I would love it if we could just take the day of year + X and have it calculate automatically. Any tips? Thanks.
Attachments
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.