[AccessD] OT - Excel Date Formatting

Hi again Mark!

Ok. I see the problem now (I think). I hit this problem (feature?) a little
while ago. Excel stores the info differently to the way it displays it. My
solution was to write a function the converted the data into a text field
but formatted the way I wanted it.

I did much the same as you but needed to append leading zeros.

The code below works for Hours and Minutes but I'm sure you could mod it for
Day, Month, Year!

----------------Code snippet)--------
Function Date2Text(MyDate As Date)
Dim MyText As String
Dim MyHour As String
Dim MyMinute As String

Dim intHour As Integer
Dim intMinute As Integer

intHour = Len(Hour(MyDate))
If intHour = 1 Then
MyHour = "0" & Hour(MyDate)
Else
MyHour = Hour(MyDate)
End If

intMinute = Len(Minute(MyDate))

If intMinute = 1 Then
MyMinute = "0" & Minute(MyDate)
Else
MyMinute = Minute(MyDate)
End If
Date2Text = MyHour & ":" & MyMinute

End Function

> -----Original Message-----
> From: Mitsules, Mark S. (Newport News) [mailto:Mark.Mitsules at ngc.com]
> Sent: Monday, June 14, 2004 2:37 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] OT - Excel Date Formatting
> 
> 
> Chris,
> 
> Thanks for your offer.  Yes that does change the appearance 
> but M$ seems to
> know better than I when it comes to importing the underlying data into
> Access and will keep the time data during an import.  I have 
> no use for the
> time data.  My method works but seems "clunky".
> 
> 
> Mar
> 
> 
> -----Original Message-----
> From: Foote, Chris [mailto:Chris.Foote at uk.thalesgroup.com] 
> Sent: Monday, June 14, 2004 9:25 AM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] OT - Excel Date Formatting
> 
> 
> Hi Mark!
> 
> Can you not just change the formatting of the column from 
> mm/dd/yyyy h:mm:ss
> to mm/dd/yyyy in the format cells dialogue?
> 
> If this is not quite what you want reply to me as I've got a 
> hand-rolled
> function to convert Excel dates/times to text.
> 
> Regards
> Chris Foote - UK
> 
> > -----Original Message-----
> > From: Mitsules, Mark S. (Newport News) 
[mailto:Mark.Mitsules at ngc.com]
> Sent: Monday, June 14, 2004 2:16 PM
> To: '[AccessD]'
> Subject: [AccessD] OT - Excel Date Formatting
> 
> 
> Group,
> 
> What is the simplest way to transform a column of dates...?
> 
> >From this: 5/3/2004  2:41:33 PM
> To this: 5/3/2004
> 
> Currently I'm using =MONTH(D2)&"/"&DAY(D2)&"/"&YEAR(D2), then 
> copying and
> using Paste Special (Values) to get the desired result.  Is 
> there a better
> way?
> 
> Thanks,
> 
> Mark