Serious date problem

  • Thread starter Thread starter Angela
  • Start date Start date
A

Angela

Hey,

I have a column with date as m/d/yyyy format

When I use format([datecolumn],"dd/mm/yyyy")

For 1/12/2009 it changes to 01/12/2009
For 1/10/2009 it changes to 01/10/2009
For 2/3/2009 it changes to 02/03/2009
For 3/2/2009 it changes to 03/02/2009
For 1/13/2009 it changes to 13/01/2009
For 12/17/2009 it changes to 17/12/2009
For 12/10/2009 it changes to 12/10//2009

As you can see, the month is before first forward slash "/". It can be
"m" or "mm" but not more than 12 since there are only12 months.
The date is between first & second "/" which is "d" or "dd".

Well the way I see it, if we can count the value before 1st "/" and
if its single, we can add a zero "0" to it to.. like for all digits
below 10 to make it a valid month in "mm" format.

Same goes for the value count between two "/" to make it a valid "dd".
The total would be 2/2/4 = 10 (2 values each, a slash, 2values,
aslash, 4 values)

Example 2/3/2009 would be come 02/03/2009.

Ofcourse then we can swap the two mid values from the figure to left &
the left one to the mid to get the required.

Example 02/03/2009 is actually February 03, 2009 which we can make
03/02/2009 using combination of left,right & mid function on
02/03/2009.


I would appreciate if someone can put this into a code.
 
Angela said:
Hey,

I have a column with date as m/d/yyyy format

When I use format([datecolumn],"dd/mm/yyyy")

For 1/12/2009 it changes to 01/12/2009
For 1/10/2009 it changes to 01/10/2009
For 2/3/2009 it changes to 02/03/2009
For 3/2/2009 it changes to 03/02/2009
For 1/13/2009 it changes to 13/01/2009
For 12/17/2009 it changes to 17/12/2009
For 12/10/2009 it changes to 12/10//2009
Click to expand...

Is this in the output of a query? Of your example table above only these
are correct...
For 1/13/2009 it changes to 13/01/2009
For 12/17/2009 it changes to 17/12/2009
Click to expand...

The rest could not possibly be the result of
format([datecolumn],"dd/mm/yyyy") on the values in the first column unless
they are not formatted m/d/yyyy as you say they are.

Is this field actually set up as a DateTime type or is it text that just
appear to be dates?
 
Hey,

No that not the output.

The column is text I guess.

I have come up with a work around.

I have used cdate([datecolumn]) to get it in a 10 length like mm/dd/
yyyy.

For all first 2 greater than 12, cdate already put them in correct
date format.

Now since first 2 cannot be greater than equal to 12, by 2 I mean 01,
02, 03, 04... for 1,2,3,4.
I swapped it in mid and got mid in the start to get the desired.

So
2/11/2007 (February 11, 2007)
becomes 02/11/2007 (using cdate)
and finally 11/02/2007 (swaped 02 to mid & 11 in start.. kinda
rearranged)

11/24/2007 changed to 24/11/2007 with cdate() function and since 24 is
12 so above condition ignored it.
Click to expand...

Final column result is

11/02/2007
24/11/2007

Solved! : )

I hope this mechanic can be put in VBA as a function to avoid a little
lengthy formula of iif,mid,left/right used above.
 
Angela said:
Hey,

No that not the output.

The column is text I guess.

I have come up with a work around.

I have used cdate([datecolumn]) to get it in a 10 length like mm/dd/
yyyy.

For all first 2 greater than 12, cdate already put them in correct
date format.

Now since first 2 cannot be greater than equal to 12, by 2 I mean 01,
02, 03, 04... for 1,2,3,4.
I swapped it in mid and got mid in the start to get the desired.

So
2/11/2007 (February 11, 2007)
becomes 02/11/2007 (using cdate)
and finally 11/02/2007 (swaped 02 to mid & 11 in start.. kinda
rearranged)

11/24/2007 changed to 24/11/2007 with cdate() function and since 24 is
12 so above condition ignored it.
Click to expand...

Final column result is

11/02/2007
24/11/2007

Solved! : )

I hope this mechanic can be put in VBA as a function to avoid a little
lengthy formula of iif,mid,left/right used above.
Click to expand...

Once you have used CDate to change the value into an actual date type then
all you need to do is use the Format function on that result.

Format(cdate([datecolumn]),"dd/mm/yyyy")
 
Hey,

I tried that but it doesn't give me the required.

Example:

The date in list are:

12/14/2007
12/31/2007
1/31/2007
7/5/2007
7/6/2007

as m/d/y format (MDY) i guess

Now both are actually July 2007 i.e. July 05 & July 06 (05/07/2007 &
06/07/2007).

If I use format(cdate([field]),"dd/mm/yyyy") it gives May 07 & June 07
i.e. 07/05/2007 & 07/06/2007 which is wrong.
It is working for first 3 dates with cdate() in example even without
using format() function.
 
Angela said:
Hey,

I tried that but it doesn't give me the required.

Example:

The date in list are:

12/14/2007
12/31/2007
1/31/2007
7/5/2007
7/6/2007

as m/d/y format (MDY) i guess

Now both are actually July 2007 i.e. July 05 & July 06 (05/07/2007 &
06/07/2007).

If I use format(cdate([field]),"dd/mm/yyyy") it gives May 07 & June 07
i.e. 07/05/2007 & 07/06/2007 which is wrong.
It is working for first 3 dates with cdate() in example even without
using format() function.
Click to expand...

When used against a date string that could be either month-day or day-month,
CDate() will default to the pattern based on your Windows Regional settings
in Control Panel.

For most United States users the regional settings would be month-day. Do
you perhaps have yours set up as day-month?

For a function that does not care about Regional Settings use DateSerial.

Format(DateSerial(Split([field],"/")(2),Split([field],"/")(1),
Split([field],"/")(0)),"mm/dd/yyyy")
 
Back
Top