Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates are formatted as custom date but still display as numbers
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 -- Trainer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates are formatted as custom date but still display as numbers
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" "Trainer" wrote: 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 -- Trainer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dates are formatted as custom date but still display as numbers | Excel Discussion (Misc queries) | |||
Numbers being Auto Date Formatted | Excel Discussion (Misc queries) | |||
how do I concatenate custom formatted numbers | Excel Worksheet Functions | |||
how do I concatenate custom formatted numbers | Excel Worksheet Functions | |||
Convert hard keyed/formatted numbers to dates | Excel Worksheet Functions |