# date format question

#### thewiseguy

##### Well-known Member
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?

TWG.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.

thewiseguy said:
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?

TWG.

Filter with an IF function, as in,

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

Post your formula that returns the #N/A

see below. My first attempt was not working properly.

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

Replies
5
Views
255
Replies
6
Views
390
Replies
7
Views
276
Replies
1
Views
984
Replies
3
Views
176

1,203,634
Messages
6,056,457
Members
444,866
Latest member
cr130

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back