Help with DateSerial

[mattc66 via AccessMonster.com]

I am using the below code. However it when the date in my data is before 2000
I get the wrong year displayed. How could I set this up to format the date in
the 1900 and 2000's?

The data comes in as DDMMYY


=IIf(IsNull([DATE_EXP]),Null,DateSerial(2000 + Val(Left([DATE_EXP],2)),Val
(Mid([DATE_EXP],3,2)), Val(Right([DATE_EXP],2))))

 

[John W. Vinson]

I am using the below code. However it when the date in my data is before 2000
I get the wrong year displayed. How could I set this up to format the date in
the 1900 and 2000's?

The data comes in as DDMMYY


=IIf(IsNull([DATE_EXP]),Null,DateSerial(2000 + Val(Left([DATE_EXP],2)),Val
(Mid([DATE_EXP],3,2)), Val(Right([DATE_EXP],2))))

The DateSerial function is clever enough to treat a number less than 100
appropriately. Values from 0 through 29 are treated as 2000 through 2029, 30
through 99 as 1930-1999. So just leave off the 2000+ and you should be fine.

 

[John W. Vinson]

I am using the below code. However it when the date in my data is before 2000
I get the wrong year displayed. How could I set this up to format the date in
the 1900 and 2000's?

The data comes in as DDMMYY


=IIf(IsNull([DATE_EXP]),Null,DateSerial(2000 + Val(Left([DATE_EXP],2)),Val
(Mid([DATE_EXP],3,2)), Val(Right([DATE_EXP],2))))

Oops! Your algorithm and my response are both wrong if the date comes in as
ddmmyy. DateSerial's three arguments are year, month, and day; and you're
feeding it day, month, and year - e.g. 010531 SHOULD come out May 1, 1931 (if
it's ddmmyy), but either your suggestion or my revision will come out May 31,
2001.

If it is in fact ddmmyy, try:

DateSerial([DATE_EXP] MOD 100, [DATE_EXP] \ 100 MOD 100, [DATE_EXP] \ 10000)

if Date_Exp is a Number field, or

DateSerial(Val(Right([DATE_EXP], 2), Val(Mid([DATE_EXP], 3, 2)),
Val(Left([DATE_EXP], 2))

 

[mattc66 via AccessMonster.com]

I was wrong. Its YYMMDD

I am using the below code. However it when the date in my data is before 2000
I get the wrong year displayed. How could I set this up to format the date in

[quoted text clipped - 4 lines]

=IIf(IsNull([DATE_EXP]),Null,DateSerial(2000 + Val(Left([DATE_EXP],2)),Val
(Mid([DATE_EXP],3,2)), Val(Right([DATE_EXP],2))))

Oops! Your algorithm and my response are both wrong if the date comes in as
ddmmyy. DateSerial's three arguments are year, month, and day; and you're
feeding it day, month, and year - e.g. 010531 SHOULD come out May 1, 1931 (if
it's ddmmyy), but either your suggestion or my revision will come out May 31,
2001.

If it is in fact ddmmyy, try:

DateSerial([DATE_EXP] MOD 100, [DATE_EXP] \ 100 MOD 100, [DATE_EXP] \ 10000)

if Date_Exp is a Number field, or

DateSerial(Val(Right([DATE_EXP], 2), Val(Mid([DATE_EXP], 3, 2)),
Val(Left([DATE_EXP], 2))

 

[John W. Vinson]

I was wrong. Its YYMMDD

OK, just swap the first and third arguments.

(Actually you were right to begin with, just a typo later...)

 

[mattc66 via AccessMonster.com]

I am getting a message that the expression is missing a closing parenthesis.
When I add one it says still missing so I add another and I get a message
that says wrong number of arguments.

DateSerial(Val(Left([SHIP_DATE], 2), Val(Mid([SHIP_DATE], 3, 2)),Val(Right(
[SHIP_DATE], 2)


Any Ideas? As a note the data is Text and it is YYMMDD

I am using the below code. However it when the date in my data is before 2000
I get the wrong year displayed. How could I set this up to format the date in

[quoted text clipped - 4 lines]

=IIf(IsNull([DATE_EXP]),Null,DateSerial(2000 + Val(Left([DATE_EXP],2)),Val
(Mid([DATE_EXP],3,2)), Val(Right([DATE_EXP],2))))

Oops! Your algorithm and my response are both wrong if the date comes in as
ddmmyy. DateSerial's three arguments are year, month, and day; and you're
feeding it day, month, and year - e.g. 010531 SHOULD come out May 1, 1931 (if
it's ddmmyy), but either your suggestion or my revision will come out May 31,
2001.

If it is in fact ddmmyy, try:

DateSerial([DATE_EXP] MOD 100, [DATE_EXP] \ 100 MOD 100, [DATE_EXP] \ 10000)

if Date_Exp is a Number field, or

DateSerial(Val(Right([DATE_EXP], 2), Val(Mid([DATE_EXP], 3, 2)),
Val(Left([DATE_EXP], 2))

 

[John W. Vinson]

I am getting a message that the expression is missing a closing parenthesis.
When I add one it says still missing so I add another and I get a message
that says wrong number of arguments.

DateSerial(Val(Left([SHIP_DATE], 2), Val(Mid([SHIP_DATE], 3, 2)),Val(Right(
[SHIP_DATE], 2)

It can indeed be tricky figuring out where the parens go! Let's break this
down into its components:

DateSerial(
Val(Left([SHIP_DATE], 2),
Val(Mid([SHIP_DATE], 3, 2)),
Val(Right([SHIP_DATE], 2)

What you need is three Integer values. The first row's closing paren closes
the Left() function, but not the Val() function!

Try

DateSerial(
Val(Left([SHIP_DATE], 2)),
Val(Mid([SHIP_DATE], 3, 2)),
Val(Right([SHIP_DATE], 2))
)

It can help to mentally step through the string, counting +1 for each left
paren and -1 for each right paren. You should reach 0 at the end of the
string, and the value of the number at each point tells you how deep in the
nest you are.

Sorry about the typo in my original post - hope it didn't waste too much of
your time!