date format question

[thewiseguy]

I have two sheets in my workbook. In sheet 2, I am trying to have my cells in column A equal my cells from Sheet 1, column A, as a date field specified in the format of 1/31/05. It works for most of the cells, however in certain cells where there is an "N/A" or nothing entered, the date field comes up as something "wacked" like 1/0/00. Any ideas on what i can do to always make these cells equal exactly what is my original sheets cells?

Thanks in advance.

TWG.

 

[nbrcrunch]

a mere =Sheet1!A1 copied down will do.

If you are getting errors then there is something wrong with the source (sheet1). In my experience, that is usually that while the cell appears to contain a date, it actually contains text masquerading as a date.

To test this, in a different cell, multiple the source cell by 1. If it doesn't return a value, you'll know why.

 

[Brian from Maui]

I have two sheets in my workbook. In sheet 2, I am trying to have my cells in column A equal my cells from Sheet 1, column A, as a date field specified in the format of 1/31/05. It works for most of the cells, however in certain cells where there is an "N/A" or nothing entered, the date field comes up as something "wacked" like 1/0/00. Any ideas on what i can do to always make these cells equal exactly what is my original sheets cells?

Thanks in advance.

TWG.

Filter with an IF function, as in,

=IF(Sheet1!A1="","",Sheet1!A1)

Post your formula that returns the #N/A

 

[nbrcrunch]

see below. My first attempt was not working properly.

 

[nbrcrunch]

Others with more talent in VBA will have a better offering, but I am sort of proud I figured this much out. (And at the same time embarrassed I'm not even further in understanding VBA).

The VBA module here will take the following conditions and convert them to valid dates:
1. Cells containing true dates
2. Cells containing text that appears like a date
3. Cells containing text that appears like a date with leading & trailing spaces.
4. Cells containing text that appears like a date with a leading single quote (')

The problem with the code is that instead of turning them into static dates, it turns them into a formula. I couldn't figure out how to do it the other way. Select the range you want to convert then run the macro.

__

Sub Macro2()

For Each cell In Selection

cell.Formula = "=DATEVALUE(" & Chr(34) & Trim(cell.Value) & Chr(34) & ")"

Next cell

End Sub