Excel Not Accepting Dates in 1800s

S

Sonia

1. I have been trying to export a report from Brio into excel. Wha
I'm experiencing is any dates in my report prior to 1900 get replace
in excel with the number symbol (#####). Anyone know how to fix this?

2. Another thing I can't seem to find out. When I export a one-colum
list, the formatting is retained in excel. Is there any way to ge
excel to format one long list into multiple columns so that the list i
not so long?

Thank you!
Soni
 
N

Norman Harker

Hi Sonia!

Excel only handles dates after 31-Dec-1900.

VBA will handle dates before that and this is exploited by a very
useful Addin downloadable from:

John Walkenbach:
Extended Date Functions
http://j-walk.com/ss/excel/files/general.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
G

Gord Dibben

Sonia

For your second question.........

Manually............

If your data is an column A starting at Cell A1, then the following
formula, entered in Cell B1 and filled across 10 columns and down 100
rows will produce your 10 columns of 100 rows. Any more than 1000 original
rows, you do the math and make alterations.

=INDIRECT("A"&(ROW()+(COLUMN()-2)*100))

The 2 refers to the column of Cell B1; if you're putting the formula in
a different column, use the appropriate number for that column.

Copy>Paste Special(in place) the results then delete the original column A.

VBA Macro...............

Public Sub SplitToCols()
Dim NUMCOLS As Integer
Dim i As Integer
Dim colsize As Long
On Error GoTo fileerror

NUMCOLS = InputBox("Choose Final Number of Columns")
colsize = Int((ActiveSheet.UsedRange.Rows.Count + _
(NUMCOLS - 1)) / NUMCOLS)
For i = 2 To NUMCOLS
Cells((i - 1) * colsize + 1, 1).Resize(colsize, 1).Copy Cells(1, i)
Next i
Range(Cells(colsize + 1, 1), Cells(Rows.Count, 1)).Clear
fileerror:
End Sub

Gord Dibben Excel MVP
 
Top