How to force date format

Posted on 2009-06-29
Last Modified: 2012-05-11
I'm transfering data from Access to Excel and I am having trouble forcing the date to format as example: Oct-07.  I used the format in Excel, but it keeps converting to 10-07-2009 even through its correct in Access.  My thought is to use code to force it.  Need help with correct format.

'Force MM-D format
        objSht.Range("E7:Q155").Format ("mm d")
Question by:mato01
  • 4
  • 3
  • 3
  • +1
LVL 39

Expert Comment

ID: 24737202
How are you transfering data from Access to Excel?

In Excel, try:
Range("E7:Q155").NumberFormat = "mm d"

About dates in Access:
Access stores all dates as doubles as the number of days since 12/30/1899 12:00:00 AM  (Format(0,"mm/dd/yyyy hh:nn:ss AM/PM") = 12/30/1899 12:00:00 AM).  For example 09/11/2005 10:56:40 AM is stored as 38606.4560185185 or 38606.4560185185 days since 12/30/1899 12:00:00 AM  (CDbl(#09/11/2005 10:56:40 AM#) = 38606.4560185185).  You can input and display the date/time in any format you choose.  

Time without a date will default to 12/30/1899 since the integer portion of the double is zero.

Also keep in mind that Access is an American product so VBA is sometimes expecting mm/dd/yyyy even when the regional setting is set for something else.  yyyy/mm/dd will always work for VBA input.

LVL 44

Expert Comment

ID: 24737339
even though you may have a date in Access formatted as mmm-yy,  underlying the 'appearance' is the whole date

In Excel below I have =Today() in the first column, I formatted column B as mmm-yy, and column C as yyyy-mm-dd

2009-06-29      Jun-09      2009-06-29

While you might think that the column B date is stricltly Jun-09, by copying it to column C we see that it is actually the complete date for today.

Expert Comment

ID: 24737365
In Excel highlight the cell(s) you want to format and right click.  Pick Format Cells.  On the Number tab in the Category pick Custom(at the bottom). Over to the right in the Type: box type in mmm-dd and click OK.

Author Comment

ID: 24737497
Here is the code I'm using to transfer the data from my form to the excel.  THe date format is correct in the form Oct-07; however, when it transfers to Excel it changes to 10-07-2009.  I've formatted the template Excel file to Oct-07, but whenever I transfer it changes back to 10-07-2009.  This is why I thought I could code it to make it work. Any thoughts.
'--- note the use of the Option Explicit in the above line. forces all variables to be declared

Private Sub Command146_Click()

    Dim objXL As Excel.Application

    Dim objWkb As Excel.Workbook

    Dim objSht As Excel.Worksheet

    Dim iRow As Integer

    Dim iRowS As Integer

    Dim rst As Recordset

    Dim db As Database


    DoCmd.Echo False

    '--- assumes use of DAO references.

    Set db = CurrentDb()


    Set rst = db.OpenRecordset("SELECT * FROM tblCodeAllocation ORDER BY Division;")  '--- open recordset to all data in table CustData


    If rst.EOF And rst.BOF Then   '--- if no data, stop

        MsgBox "No data to display"


        Set rst = Nothing

        Exit Sub

    End If


    Set objXL = New Excel.Application  '--- start a new instance of Excel



    objXL.Visible = True

    Set objWkb = objXL.Workbooks.Open("C:\users\mato01\documents\CustList.xls")   '--- open workbook --- change to match where XLS template is

    Set objSht = objWkb.Worksheets(1)   '--- first tab in workbook

    DoCmd.Echo False

    iRow = 6          '--- first row to print data will be 3+1 (as rows 1,2,3 have column headers and page headers)

    iRowS = iRow + 1  '--- keep track of first row with data so we can do totals




    Do While Not rst.EOF

        iRow = iRow + 1


        objSht.Range("A" & iRow) = rst!GMNA_NAIPC

        objSht.Range("B" & iRow) = rst!View

        objSht.Range("C" & iRow) = rst!C_T

        objSht.Range("D" & iRow) = rst!BookMgr

        objSht.Range("E" & iRow) = rst!Division

        objSht.Range("F" & iRow) = rst!Brand

        objSht.Range("G" & iRow) = rst!Buildout

        objSht.Range("H" & iRow) = rst!LastWkProd

        objSht.Range("I" & iRow) = rst!CutOffSEOPT

        objSht.Range("J" & iRow) = rst!CutOffSEOPT

        objSht.Range("K" & iRow) = rst!NAIPCCutoff

        objSht.Range("L" & iRow) = rst!DomesticCutOff

        objSht.Range("M" & iRow) = rst!QuickOrder

        objSht.Range("N" & iRow) = rst!OrderEntry

        objSht.Range("O" & iRow) = rst!StartUp

        objSht.Range("P" & iRow) = rst!OrderEstimate

        objSht.Range("Q" & iRow) = rst!AssemblyCenter










     'Change the Font

        objSht.Range("E7:Q155").Font.Bold = False

        objSht.Range("E7:Q155").Font.Name = "Arial"

        objSht.Range("E7:Q155").Font.Size = 8




   ' objSht.Range("A" & iRow + 1) = "TOTAL SALES"

   ' objSht.Range("C" & iRow + 1) = "=sum(C" & iRowS & ":C" & iRow & ")"



    '--- finally, insert the date/time

   objSht.Range("E" & iRow + 4).Select

   objSht.Range("E" & iRow + 4) = Format(Now(), "mmm d, yyyy  hh:nn")



    Set objSht = Nothing

    Set objWkb = Nothing

    Set objXL = Nothing

    Set db = Nothing

    Set rst = Nothing


DoCmd.Echo True

MsgBox "Transfer Complete"

End Sub

Open in new window

LVL 44

Expert Comment

ID: 24737604
Do you want to wind up with a string or a bona fide date?  Any time you format a date in Excel, the underlying numeric value does not change, only the 'appearance' as I said in my previous post.  Are you going to tell us which field is the culprit?
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


Author Comment

ID: 24737751
Columns F thru P should be a string (date)  as you can type either a date or text in the form.
LVL 39

Expert Comment

ID: 24737771
     'Change the Font
add this line:
objSht.Range("E7:Q155").NumberFormat = "mm d"
LVL 39

Accepted Solution

thenelson earned 50 total points
ID: 24737854
<Columns F thru P should be a string (date)  as you can type either a date or text in the form.>

You can change the lines to (row F for example):
objSht.Range("F" & iRow) = Format (rst!Brand, "mm d")
LVL 44

Expert Comment

ID: 24737861
If you want to see  mmm-dd then change the last line of nelson's solution above to

objSht.Range("E7:Q155").NumberFormat = "mmm-dd"

Author Closing Comment

ID: 31597970
Worked.  Thanks
LVL 39

Expert Comment

ID: 24739582
You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy Computing!


Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now