Dates In EXCEL

[Ross]

I having loads of issues with dates, even whe i set the
format to dd/mm/yy, or what ever else it only works of
some of the cell, i.e. it has no effect on some vales.
also it move a date like 12/6/03 which is right to
06/12/03.

Any one got any ideas/ know any good sites ect.
cheers Ross

 

[wiseman]

Ross,
Look at control panel, regional & language options and make sure your date
settings are OK there.
Also this may give you some ideas
http://www.mvps.org/dmcritchie/excel/datetime.htm
Barbara

 

[ROSS]

Thanks Barbar,
my regional setting are set up for uk dates.
the date is pasted in in uk formate

the data i have looks like this

ORDER NUMBER PRODUCT DEL DATE

1111111 Magic Beans 12/06/03
Luck Starts
Hummpy Dummpy
2222222 The Moon 24/06/03
Tinky Winky
Barny

My macro filled the empyt cells with the data above it,
unless it had a value in whic case, go to next cell.

---------------------------------------------------------
(this is the code)
'populate cell with acoutn mubers and dates if the order
line fdoes not have it


cycount = 7 'loops on part number as ThisWorkbook is
the only felid that has all the rows filled in!
popcounta = 7
popcountd = 7

Do Until Worksheets("1").Cells(cycount, 3) = ""

If Worksheets("1").Cells(popcounta, 1) <> "" Then
popcounta = popcounta + 1
Else
Worksheets("1").Cells(popcounta, 1) = Worksheets
("1").Cells(popcounta - 1, 1)
popcount = popcounta + 1
End If

If Worksheets("1").Cells(popcountd, 6) <> "" Then
popcountd = popcountd + 1
Else
Worksheets("1").Cells(popcountd, 6) = Worksheets
("1").Cells(popcountd - 1, 6)
popcountd = popcountd + 1
End If
-----------------------------------------------------

I then got the max and mins valus from this list(i.e. DEL
DATE, above) wrote a new list in a dif colum for each date
between these value.

The problem was that the max date was dispalyed as
06/12/03, which was not possible.

I tryed formating in excel, messing around with the
control pannle setting etc, but no joy.

However i have just fixed the problem and the data (inc
max min vals) is good and correct, i i don't really no why!

This what i did:

Regional setting = UK
format whole page to genral

then befor i past data, format the whole row where the
date will go to "d/mm/yy" with:

Columns("F:F").Select
Selection.NumberFormat = "d/mm/yy"

Now the thing is that the date are now displayed is us
format, but not only that but with "mm/dd/yy" not "mm/d/yy"

I cant make it out, but all the dates are correct.

Befor it was saying that 12/06/03, was 06/12/03, and stuff
like that, but these seams to have fix the problem,
although i not sure how robust it is!!!!!

Thanks
Rosco