dates are formatted as custom date but still display as numbers

T

Trainer

I have inherited a spreadsheet which has a column of dates but they are
displayed as numbers. When I click on the numbers, it shows them as date in
the formula bar and when I go into format it shows as a custom date format.
I've tried changing the format but they still show as numbers.

It looks as if someone's created the following macro (not good at macros).
What do I need to change to display the numbers as date?

Sub CreateDistributionForm()
'
' CreateDistributionForm Macro
' Macro recorded and updated by MONUC-HQ-ADC COS
'
' Keyboard Shortcut: Ctrl+Shift+F
'
idRow = ActiveCell.Row

With Worksheets("Incoming")
txtNum = Cells(idRow, 1).Value
txtRef = Cells(idRow, 2).Value
txtDate = Cells(idRow, 3).Value
txtSubject = Cells(idRow, 4).Value
txtFrom = Cells(idRow, 5).Value
End With

Worksheets("DistributionForm").Select
Range("E2").Value = txtNum
Range("A31").Value = txtRef
Range("A30").Value = txtDate
Range("D30") = txtSubject
Range("A32") = txtFrom
Range("A4") = Date


ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Worksheets("Incoming").Select

End Sub
 
J

joel

Looking at the code you have two dates. One that goes on the distribution
sheet in A4 and one in A30. The one in A4 is using the function date which
is probably showing correctly as a Date. The one in A30 is the one you may
be having a problem with. Try making the change below. I won't guareentee
it will solve the problem. If it doesn't post the data from A30 and I will
fix the problem. Make sure there isn't a single quote in front of the text
in A30. I suspect that the problem may have to do with international
differences in Dates. US puts month 1st and England put day first.
Depending which International settings are used on your PC it may be
backwarrds. First try this changes

From
Range("A30").Value = txtDate

to
Range("A30").Value = DateValue(txtDate)
Range("A30").numberformat = "MM/DD/YY"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top