date format question

[Jessica]

Hello All,


I have a report that has a field that looks up from an expiration table
=DateAdd("m",[ExpirationDate],Now()) the thing is that I need the format
to look like this 06MR9 (YYMMD). Also I would like to have the month
be shown as abbreviations for example January=JA February=
FE March=MR it wouldn't always be the first 2 letters of the month.


Thanks so much,
Jess

 

[Ofer]

Try this

left(format(Mydate,"YYMMMd"),4) & Mid(format(Mydate,"YYMMMd"),6)

 

[Rick Brandt]

Hello All,


I have a report that has a field that looks up from an expiration
table =DateAdd("m",[ExpirationDate],Now()) the thing is that I need
the format to look like this 06MR9 (YYMMD). Also I would like to
have the month be shown as abbreviations for example January=JA February=
FE March=MR it wouldn't always be the first 2 letters of the month.


Thanks so much,
Jess

You'd have to write a custom function for that. Wouldn't be too difficult
though. Basically split the date into it's three numeric pieces, convert the
year and day to strings, use a Case statement to determine the desired month
string and then push all three pieces together for the return value.

 

[Jessica]

Ofer,


That works beautifully but, I have records that have different
expirations. I pretty much know and have it set up to show the date when
the product expires from when the report is made I just need it in a
format like this YYMMD and with the month being abbreviated a certain
way. I have a simillar report that I got to work with abbreviated months
but it dosen't lookup a table to see how many onths a product expires.
To do what I want with this report I figured I would have to combine
these two formulas to get the results I want.

=DateAdd("d",[ExpirationDate],Now())
=Choose(Month(Date()),"JA","FE","MR","AL","MA","JN","JL","AU","SE","OC","NO","DE")

Try this

left(format(Mydate,"YYMMMd"),4) & Mid(format(Mydate,"YYMMMd"),6)

:

Hello All,


I have a report that has a field that looks up from an expiration table
=DateAdd("m",[ExpirationDate],Now()) the thing is that I need the format
to look like this 06MR9 (YYMMD). Also I would like to have the month
be shown as abbreviations for example January=JA February=
FE March=MR it wouldn't always be the first 2 letters of the month.


Thanks so much,
Jess

 

[James A. Fortune]

Hello All,


I have a report that has a field that looks up from an expiration
table =DateAdd("m",[ExpirationDate],Now()) the thing is that I need
the format to look like this 06MR9 (YYMMD). Also I would like to
have the month be shown as abbreviations for example January=JA February=
FE March=MR it wouldn't always be the first 2 letters of the month.


Thanks so much,
Jess

You'd have to write a custom function for that. Wouldn't be too difficult
though. Basically split the date into it's three numeric pieces, convert the
year and day to strings, use a Case statement to determine the desired month
string and then push all three pieces together for the return value.

Rick's method should work well. Before writing that function try:

= Format(DateAdd("m",[ExpirationDate],Now()),'yy') &
Choose(Month(DateAdd("m",[ExpirationDate],Now())),"JA","FE","MR","AL","MA","JN","JL","AU","SE","OC","NO","DE")
& Format(DateAdd("m",[ExpirationDate],Now()), 'd')

James A. Fortune

 

[Jessica]

Well done!!!!!! Thanks so much James, Rick, and Ofer

Hello All,


I have a report that has a field that looks up from an expiration
table =DateAdd("m",[ExpirationDate],Now()) the thing is that I need
the format to look like this 06MR9 (YYMMD). Also I would like to
have the month be shown as abbreviations for example January=JA
February=
FE March=MR it wouldn't always be the first 2 letters of the month.


Thanks so much,
Jess

You'd have to write a custom function for that. Wouldn't be too
difficult though. Basically split the date into it's three numeric
pieces, convert the year and day to strings, use a Case statement to
determine the desired month string and then push all three pieces
together for the return value.

Rick's method should work well. Before writing that function try:

= Format(DateAdd("m",[ExpirationDate],Now()),'yy') &
Choose(Month(DateAdd("m",[ExpirationDate],Now())),"JA","FE","MR","AL","MA","JN","JL","AU","SE","OC","NO","DE")
& Format(DateAdd("m",[ExpirationDate],Now()), 'd')

James A. Fortune