display complete dates as numbers and incomplete as text in same column

[Ken Soona]

Hello everyone,

I am trying to format dates and am having trouble.

I have a series of birthdates, most of them are complete and are in mm/dd/yyyy, but some are missing the day.

Complete date - 01/30/2018
Incomplete date - 01/00/2018

I am having trouble getting excel to display the dates without giving me errors, I need to compare DOBs from one file to another.

And here is where it gets odd -

I have one column of dates (Col 1), where all the complete dates are formatted as numbers, and the incomplete dates show up as text (01/00/2018). I have no idea how I did this . I need to replicate this (Col 2) so I can compare Col 1 dates to Col 2 dates.

I have tried
DATE
DATEVALUE
Changing format to Custom mm/dd/yyyy
Multiplying dates by 1
Every format type I can think of.

Can anyone help? I want to say "well this just can't be done in Excel," except I'm staring at columns where it is done, I just can't seem to replicate it.

 

[snjpverma]

which day do you want in place of the Incomplete dates ?

<tbody>
</tbody><colgroup><col></colgroup>

 

[Ken Soona]

A complete date should read 01/30/2018
An incomplete date should read 01/00/2018

As I said, I have a column where it IS in this format, and when I get into the Format Cells option, it displays a serial number for the complete dates, and has text for the incomplete dates. I need to be able to replicate whatever I did that got it that way.

 

[cerfani]

maybe im just slow but i read your post several times and im not following what you are trying to do.

Anyways "01/00/2018" is just text, it is an invalid date. If you try to perform some kind of mathematical operation like you can with dates you will get an error... try using IFERROR if that is what you are doing with it and you can perform some other function if whatever you are doing errors.

Are you trying to vlookup a date? Tell us specifically what you are doing... show us your formulas.

 

[Ken Soona]

DOB to fix in cell A1, stored as text - 19530300
=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))
This produces 02/28/1953

DOB to fix in cell B2, stored as text - 03/00/1953
DATE(LEFT(B2,4),MID(B2,4,2),RIGHT(B2,2))
This produces 5/31/1955

I know what it's doing...it's having issues with the 00 value for day, I get that. What I need is a formula, or even format, that for each complete date, will store as serial, and for each incomplete date, it will store as text, in the same column.

Everything I'm doing either throws me an error b/c of serial problems, defaults to 01/00/1900, which is unhelpful, or stores as text, making comparison difficult.

Perhaps another way to ask the question - how can I force excel to recognize 00 as a part of a date, either the month or the day?

 

[Dave Patton]

Excel 2010
	A	B	C
1
2	19530300	28-Feb-53	1-Mar-53
3	03/00/1953	28-Feb-53	1-Mar-53
4
7b

Cell Formulas
Range	Formula
B2	=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
B3	=DATE(RIGHT(A3,4),LEFT(A3,2),MID(A3,4,2))
C2	=DATE(LEFT(A2,4),MID(A2,5,2),IF(RIGHT(A2,2)+0=0,1,RIGHT(A2,2)))
C3	=DATE(RIGHT(A3,4),LEFT(A3,2),MAX(MID(A3,4,2)+0,1))

Excel is using 0 day; the date shows as Feb 28 (the date prior to Mar 1)
If you want to specify a day, include that number in the formula. Column C shows 2 alternatives that show 1.

 

[Dave Patton]

Excel 2010
	A	B	C	D
2	19530300	28-Feb-53	1-Mar-53	19530300
3	19530322	22-Mar-53	22-Mar-53	22-Mar-53
7b

Cell Formulas
Range	Formula
B2	=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
B3	=DATE(LEFT(A3,4),MID(A3,5,2),RIGHT(A3,2))
C2	=DATE(LEFT(A2,4),MID(A2,5,2),IF(RIGHT(A2,2)+0=0,1,RIGHT(A2,2)))
C3	=DATE(LEFT(A3,4),MID(A3,5,2),IF(RIGHT(A3,2)+0=0,1,RIGHT(A3,2)))
D2	=IF(RIGHT(A2,2)+0=0,A2,DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)))
D3	=IF(RIGHT(A3,2)+0=0,A3,DATE(LEFT(A3,4),MID(A3,5,2),RIGHT(A3,2)))