Date-Mid Function

[antmorano]

I have this formula: =DATE(1900+MID(G7,1,3),MID(G7,4,2),MID(G7,6,2))
and the cell it is referencing has this in it: 0870526 (with the cell
format set as custom as '0000000'

The result it is producing is: 4/6/2774

It should be 5/26/1987.

Does anyone know what is going wrong?

-Anthony Morano
Pension Intern

 

[Dave Peterson]

If you have G7 formatted as text (or started the entry with an apostrophe), then
your formula works ok.

But if G7 is a real number with a custom format to show that leading 0, then the
value in the cell is really just: 870526

And that screws up your =mid() pieces.

 

[bpeltzer]

Dave's exactly right. The formatting is only changing the DISPLAY of what's
in G7, not the cell contents which you're referencing in your formula.
You could adjust your formula by changing each G7 to be text(G7,"0000000").
Or, if the value in G1 is ALWAYS six digits, just adjust your arguments to
the mid function appropriately:
=date(1900+mid(g7,1,2),mid(g7,3,2),mid(g7,5,2))

 

[Fred Smith]

As you have a number, not text, use Int and Mod, rather than Mid, as in:

=DATE(1900+INT(G7/10000),MOD(INT(G7/100),100),MOD(G7,100))

Regards,
Fred.

 

[antmorano]

Ok- that's what I thought about the display only being changed. The
problem I'm going to run into is that I have about 28,000 records
where some value have a 0 and some have a 1. I don't run into the
problem where there is a 1 in the beginning- the 1 represents the
century. How should I go about formatting these?

-Anthony Morano

 

[Peo Sjoblom]

I haven't checked it but if the date is 000101 it will probably be 101 in
the cell
and the formula will fail


--


Regards,


Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

Try this formula...

=DATE(1900+MID(G7,1,LEN(G7)-4),LEFT(RIGHT(G7,4),2),--RIGHT(G7,2))

Rick

 

[Dana DeLouis]

Not sure if this applies to all your data, but...

[A1] = 870526

=DATEVALUE(TEXT(A1+19000000,"####\/##\/##"))

..and format the cell to show 5/26/1987

--
HTH :>)
Dana DeLouis

 

[Rick Rothstein \(MVP - VB\)]

I would suggest changing the forced slashes to dashes instead. I'm not sure
if Excel will use regional settings for the day/month order when slashed
dates with the year first are used, but I'm sure using dashes forces the
year-month-day interpretation no matter what the regional setting....

=DATEVALUE(TEXT(G7+19000000,"####-##-##"))

Rick

Not sure if this applies to all your data, but...

[A1] = 870526

=DATEVALUE(TEXT(A1+19000000,"####\/##\/##"))

..and format the cell to show 5/26/1987

--
HTH :>)
Dana DeLouis

I have this formula: =DATE(1900+MID(G7,1,3),MID(G7,4,2),MID(G7,6,2))
and the cell it is referencing has this in it: 0870526 (with the cell
format set as custom as '0000000'

The result it is producing is: 4/6/2774

It should be 5/26/1987.

Does anyone know what is going wrong?

-Anthony Morano
Pension Intern

 

[antmorano]

Rick- that formula worked great!!!!.... Thanks I really appreciate it.

Thanks to everyone for their input also.

-Anthony

 

[Peo Sjoblom]

DATEVALUE is a totally obsolete function unless one uses it for pedagogical
reasons (to show one wants a date)

=--TEXT(G7+19000000,"####-##-##")




--


Regards,


Peo Sjoblom

I would suggest changing the forced slashes to dashes instead. I'm not sure
if Excel will use regional settings for the day/month order when slashed
dates with the year first are used, but I'm sure using dashes forces the
year-month-day interpretation no matter what the regional setting....

=DATEVALUE(TEXT(G7+19000000,"####-##-##"))

Rick

Not sure if this applies to all your data, but...

[A1] = 870526

=DATEVALUE(TEXT(A1+19000000,"####\/##\/##"))

..and format the cell to show 5/26/1987

--
HTH :>)
Dana DeLouis

I have this formula: =DATE(1900+MID(G7,1,3),MID(G7,4,2),MID(G7,6,2))
and the cell it is referencing has this in it: 0870526 (with the cell
format set as custom as '0000000'

The result it is producing is: 4/6/2774

It should be 5/26/1987.

Does anyone know what is going wrong?

-Anthony Morano
Pension Intern

 

[Rick Rothstein \(MVP - VB\)]

I was concentrating so much on the arguments to the TEXT function that I
didn't even pay attention to the unnecessary DATEVALUE function it was
embedded in. Thanks for picking up on that.

Rick

DATEVALUE is a totally obsolete function unless one uses it for
pedagogical reasons (to show one wants a date)

=--TEXT(G7+19000000,"####-##-##")




--


Regards,


Peo Sjoblom

I would suggest changing the forced slashes to dashes instead. I'm not
sure if Excel will use regional settings for the day/month order when
slashed dates with the year first are used, but I'm sure using dashes
forces the year-month-day interpretation no matter what the regional
setting....

=DATEVALUE(TEXT(G7+19000000,"####-##-##"))

Rick

Not sure if this applies to all your data, but...

[A1] = 870526

=DATEVALUE(TEXT(A1+19000000,"####\/##\/##"))

..and format the cell to show 5/26/1987

--
HTH :>)
Dana DeLouis


I have this formula: =DATE(1900+MID(G7,1,3),MID(G7,4,2),MID(G7,6,2))
and the cell it is referencing has this in it: 0870526 (with the cell
format set as custom as '0000000'

The result it is producing is: 4/6/2774

It should be 5/26/1987.

Does anyone know what is going wrong?

-Anthony Morano
Pension Intern

 

[Fred Smith]

If G7 has 101, my formula converts it to 1900-01-01, which is correct.
1000101 is 2000-01-01, also correct.

Regards,
Fred.