Date-Mid Function
 

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

Date-Mid Function
 

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.



wrote:

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

Date-Mid Function
 

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))

"Dave Peterson" wrote:

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.



wrote:

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

Date-Mid Function
 

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.

wrote in message
...
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

Date-Mid Function
 

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

Date-Mid Function
 

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


"Fred Smith" wrote in message
...
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.

wrote in message
...
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

Date-Mid Function
 

Try this formula...

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

Rick


wrote in message
...
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

Date-Mid Function
 

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


wrote in message ...

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

Date-Mid Function
 

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


"Dana DeLouis" wrote in message
...
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


wrote in message
...
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

Date-Mid Function
 

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

Thanks to everyone for their input also.

-Anthony

Date-Mid Function
 

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



"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Dana DeLouis" wrote in message
...
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


wrote in message
...
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

Date-Mid Function
 

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


"Peo Sjoblom" wrote in message
...
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



"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Dana DeLouis" wrote in message
...
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


wrote in message
...
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

Date-Mid Function
 

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

Didn't think of using the dash -, or --Text()
Thank you. :)
--
Dana DeLouis


"Peo Sjoblom" wrote in message ...

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



"Rick Rothstein (MVP - VB)" wrote in
message ...

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


"Dana DeLouis" wrote in message
...

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


wrote in message
...
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