Date formating issue

[maverickmonster]

Hi

I have some date data that is stored backwards and am correcting it as part of a process.

I have written something (below) which should do this.

The current date is held as "19860317" under the general format. I can not format in excel as a date.

When i run the program it seams to work for the majority of data as when i look at it in the unformatted output sheet in excel some of the dates are unformatted excel numbers e.g 34530 but others are as text "14/09/1987"

The only thing i can see is that the ones that are formated as text are the dates with the dDay over 12 ?

textyear = Worksheets("Screen 7 data").Cells(x, 6)
dYear = Left(textyear, 4)
dDay = Right(textyear, 2)
dMonth = Left(Right(textyear, 4), 2)
Worksheets("Screen 7 results").Cells(x, 6) = dDay & "/" & dMonth & "/" & dYear

 

[xlbo]

VBA has different date settings than your native excel. Guarenteed that it is trying to convert to mm/dd/yyyy and therefore cannot convert days > 12 as there are no MONTHS > 12. To get around this, you need to use the Date SERIAL number and then convert to the appropriate date format afterwards:

textyear = Worksheets("Screen 7 data").Cells(x, 6)
dYear = Left(textyear, 4)
dDay = Right(textyear, 2)
dMonth = Left(Right(textyear, 4), 2)
Worksheets("Screen 7 results").Cells(x, 6) = [b]DATESERIAL([dYear],[dMonth],[dDay])[/b]

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[Shippwreck]

Hi,

Possibly the solution is much simpler, when i copy "19860317" into a new sheet (cell A1) and then add forward slashes so it looks like "1986/03/17" excel automatically rearranges it for me to 03/17/1986

so possibly all your macro needs to do is add the slashes in not actually do any formatting...

Hope this helps

Shippwreck

http://www.orlandoluxuryvilla-pool.com

 

[TonyJollans]

Hi maverickmonster,

If your text dates are all 8-digit numbers then you don't need code:

... assuming they are in column A
... insert a new column B
... in cell B1 put [blue][tt]=DATEVALUE(TEXT(A1,"####\/##\/##"))[/tt][/blue]
... format B1 as Date to see the formatted date
... copy B1 down as far as your data in column A
... Copy and Paste Special > Values column B to itself
... remove column A

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[maverickmonster]

Thanks all for response's

I have used xlbo's code, i would do use the excel code normally but as i have to right a huge chunk of code i may as well include this so the whole sheet is with out formula

Cheers

Maverickmonster