Displaying date as a real number

[Guest]

Hello!

I have a worksheet containing two datasets. Each set of data has associated
dates--but they are entered as different numbers. One set has the date in
format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one
digit in the source data). The other set has the date entered as a date,
i.e. 5/1/2001. I have the columns set up so that they display the same
numbers, but the original data within the columns is formatted differently.

I have to look at the data to make sure that the values in the two columns
match. However, it's a huge dataset, so doing this manually is going to take
a long time. I wanted to insert a logical argument
[=IF(value1=value2,true,false] in a separate column to save time, but since
the source data are in different formats, I get a FALSE every time.

Any ideas on how I convert these beasts into identical formats so that I can
use the logical argument strategy? The displayed numbers must not be changed
to the odd Excel date-counting format, unfortunately--if that were the case
it wouldn't be so difficult.

Thanks for any suggestions,

lisa

 

[Rick Rothstein \(MVP - VB\)]

If I read your post correctly, each date column is formatted uniformly
within itself; that is, one column is formatted as actual dates and the
other as the 5-digit number. Assume Column A is formatted as a date and
Column B as the 5-digit number. Put this formula in row 2 (assuming that is
the row your data starts in)...

=IF(A2=DATE(2000+LEFT(B2),MID(B2,2,2),RIGHT(B2,2)),TRUE,FALSE)

Rick

 

[T. Valko]

You can convert the numeric string to a date like this.

the year is one digit in the source data

Assuming the year is *always* in the 2000 decade

A1 = 10501
B1 = 5/1/2001

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))

Returns 5/1/2001

So, to comapre the 2 dates:

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1

--
Biff
Microsoft Excel MVP

Hello!

I have a worksheet containing two datasets. Each set of data has
associated
dates--but they are entered as different numbers. One set has the date in
format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is
one
digit in the source data). The other set has the date entered as a date,
i.e. 5/1/2001. I have the columns set up so that they display the same
numbers, but the original data within the columns is formatted
differently.

I have to look at the data to make sure that the values in the two columns
match. However, it's a huge dataset, so doing this manually is going to
take
a long time. I wanted to insert a logical argument
[=IF(value1=value2,true,false] in a separate column to save time, but
since
the source data are in different formats, I get a FALSE every time.

Any ideas on how I convert these beasts into identical formats so that I
can
use the logical argument strategy? The displayed numbers must not be
changed
to the odd Excel date-counting format, unfortunately--if that were the
case
it wouldn't be so difficult.

Thanks for any suggestions,

lisa

 

[Guest]

Hi,

In excel the number 10501 (depending on an options setting) represents 30
Sep 1928 and 1 may 2001 is represented by the number 37012 so to get a
comparison to evaluate as true then you will have to add or subtract a fiddle
factor. Why not simply use properly formatted dates?

Mike

 

[Guest]

Perfect. Awesome. WAY beyond what I could have thought of--thank you so
much! I'm going to go try this out right now.

-lisa

If I read your post correctly, each date column is formatted uniformly
within itself; that is, one column is formatted as actual dates and the
other as the 5-digit number. Assume Column A is formatted as a date and
Column B as the 5-digit number. Put this formula in row 2 (assuming that is
the row your data starts in)...

=IF(A2=DATE(2000+LEFT(B2),MID(B2,2,2),RIGHT(B2,2)),TRUE,FALSE)

Rick

Hello!

I have a worksheet containing two datasets. Each set of data has
associated
dates--but they are entered as different numbers. One set has the date in
format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is
one
digit in the source data). The other set has the date entered as a date,
i.e. 5/1/2001. I have the columns set up so that they display the same
numbers, but the original data within the columns is formatted
differently.

I have to look at the data to make sure that the values in the two columns
match. However, it's a huge dataset, so doing this manually is going to
take
a long time. I wanted to insert a logical argument
[=IF(value1=value2,true,false] in a separate column to save time, but
since
the source data are in different formats, I get a FALSE every time.

Any ideas on how I convert these beasts into identical formats so that I
can
use the logical argument strategy? The displayed numbers must not be
changed
to the odd Excel date-counting format, unfortunately--if that were the
case
it wouldn't be so difficult.

Thanks for any suggestions,

lisa

 

[Guest]

I would LOVE it if the dates were properly formatted. But the problem is
that I did not create the two datasets; they were created over years in two
different programs (a basic text thing, which yielded the 5-digit date
number, and Excel).

Thanks,

lisa

Hi,

In excel the number 10501 (depending on an options setting) represents 30
Sep 1928 and 1 may 2001 is represented by the number 37012 so to get a
comparison to evaluate as true then you will have to add or subtract a fiddle
factor. Why not simply use properly formatted dates?

Mike

Hello!

I have a worksheet containing two datasets. Each set of data has associated
dates--but they are entered as different numbers. One set has the date in
format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is one
digit in the source data). The other set has the date entered as a date,
i.e. 5/1/2001. I have the columns set up so that they display the same
numbers, but the original data within the columns is formatted differently.

I have to look at the data to make sure that the values in the two columns
match. However, it's a huge dataset, so doing this manually is going to take
a long time. I wanted to insert a logical argument
[=IF(value1=value2,true,false] in a separate column to save time, but since
the source data are in different formats, I get a FALSE every time.

Any ideas on how I convert these beasts into identical formats so that I can
use the logical argument strategy? The displayed numbers must not be changed
to the odd Excel date-counting format, unfortunately--if that were the case
it wouldn't be so difficult.

Thanks for any suggestions,

lisa

 

[Guest]

Thank you so much! Amazing.

You can convert the numeric string to a date like this.

the year is one digit in the source data

Assuming the year is *always* in the 2000 decade

A1 = 10501
B1 = 5/1/2001

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))

Returns 5/1/2001

So, to comapre the 2 dates:

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1

--
Biff
Microsoft Excel MVP

Hello!

I have a worksheet containing two datasets. Each set of data has
associated
dates--but they are entered as different numbers. One set has the date in
format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is
one
digit in the source data). The other set has the date entered as a date,
i.e. 5/1/2001. I have the columns set up so that they display the same
numbers, but the original data within the columns is formatted
differently.

I have to look at the data to make sure that the values in the two columns
match. However, it's a huge dataset, so doing this manually is going to
take
a long time. I wanted to insert a logical argument
[=IF(value1=value2,true,false] in a separate column to save time, but
since
the source data are in different formats, I get a FALSE every time.

Any ideas on how I convert these beasts into identical formats so that I
can
use the logical argument strategy? The displayed numbers must not be
changed
to the odd Excel date-counting format, unfortunately--if that were the
case
it wouldn't be so difficult.

Thanks for any suggestions,

lisa

 

[Rick Rothstein \(MVP - VB\)]

.....the two datasets; they were created over years...

Over years? How many years? If more than 7, then the formulas Biff and I
supplied you will have to be modified... we both assumed all dates happened
in 2000 or later.

Rick

 

[T. Valko]

I noted that in my reply and the OP seems to be satisfied!

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thank you so much! Amazing.

You can convert the numeric string to a date like this.

the year is one digit in the source data

Assuming the year is *always* in the 2000 decade

A1 = 10501
B1 = 5/1/2001

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))

Returns 5/1/2001

So, to comapre the 2 dates:

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1

--
Biff
Microsoft Excel MVP

Hello!

I have a worksheet containing two datasets. Each set of data has
associated
dates--but they are entered as different numbers. One set has the date
in
format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year
is
one
digit in the source data). The other set has the date entered as a
date,
i.e. 5/1/2001. I have the columns set up so that they display the same
numbers, but the original data within the columns is formatted
differently.

I have to look at the data to make sure that the values in the two
columns
match. However, it's a huge dataset, so doing this manually is going
to
take
a long time. I wanted to insert a logical argument
[=IF(value1=value2,true,false] in a separate column to save time, but
since
the source data are in different formats, I get a FALSE every time.

Any ideas on how I convert these beasts into identical formats so that
I
can
use the logical argument strategy? The displayed numbers must not be
changed
to the odd Excel date-counting format, unfortunately--if that were the
case
it wouldn't be so difficult.

Thanks for any suggestions,

lisa

 

[Rick Rothstein \(MVP - VB\)]

Ah, so you did... I didn't read the text of your message... I just looked at
the formula you included.

Rick

 

[Guest]

Yes, both formulas worked--thank you! The data is all from 2001-2007, from
each month of those years. So, there's lots of it, but all from years in
which the "2000+" bit of your lovely formulas works perfectly.

Thanks again,

lisa

 

[yesterdaytoday11]

thanks, this helped alot with our dates formatted like 20070703 for 7/1/2007
with a little editing to
=DATE(LEFT(A9,4),MID(A9,5,2),RIGHT(A9,2)). Now I can use DAYS360 to compare
with dates in the MM/DD/YYYY format

You can convert the numeric string to a date like this.

the year is one digit in the source data

Assuming the year is *always* in the 2000 decade

A1 = 10501
B1 = 5/1/2001

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))

Returns 5/1/2001

So, to comapre the 2 dates:

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1

--
Biff
Microsoft Excel MVP

Hello!

I have a worksheet containing two datasets. Each set of data has
associated
dates--but they are entered as different numbers. One set has the date in
format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year is
one
digit in the source data). The other set has the date entered as a date,
i.e. 5/1/2001. I have the columns set up so that they display the same
numbers, but the original data within the columns is formatted
differently.

I have to look at the data to make sure that the values in the two columns
match. However, it's a huge dataset, so doing this manually is going to
take
a long time. I wanted to insert a logical argument
[=IF(value1=value2,true,false] in a separate column to save time, but
since
the source data are in different formats, I get a FALSE every time.

Any ideas on how I convert these beasts into identical formats so that I
can
use the logical argument strategy? The displayed numbers must not be
changed
to the odd Excel date-counting format, unfortunately--if that were the
case
it wouldn't be so difficult.

Thanks for any suggestions,

lisa

 

[T. Valko]

Try this:

A1 = 20070703

Meaning: yyyy mm dd

=--TEXT(A1,"0000\/00\/00")

Format as Date

--
Biff
Microsoft Excel MVP

thanks, this helped alot with our dates formatted like 20070703 for
7/1/2007
with a little editing to
=DATE(LEFT(A9,4),MID(A9,5,2),RIGHT(A9,2)). Now I can use DAYS360 to
compare
with dates in the MM/DD/YYYY format

You can convert the numeric string to a date like this.

the year is one digit in the source data

Assuming the year is *always* in the 2000 decade

A1 = 10501
B1 = 5/1/2001

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))

Returns 5/1/2001

So, to comapre the 2 dates:

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1

--
Biff
Microsoft Excel MVP

Hello!

I have a worksheet containing two datasets. Each set of data has
associated
dates--but they are entered as different numbers. One set has the date
in
format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year
is
one
digit in the source data). The other set has the date entered as a
date,
i.e. 5/1/2001. I have the columns set up so that they display the same
numbers, but the original data within the columns is formatted
differently.

I have to look at the data to make sure that the values in the two
columns
match. However, it's a huge dataset, so doing this manually is going
to
take
a long time. I wanted to insert a logical argument
[=IF(value1=value2,true,false] in a separate column to save time, but
since
the source data are in different formats, I get a FALSE every time.

Any ideas on how I convert these beasts into identical formats so that
I
can
use the logical argument strategy? The displayed numbers must not be
changed
to the odd Excel date-counting format, unfortunately--if that were the
case
it wouldn't be so difficult.

Thanks for any suggestions,

lisa

 

[yesterdaytoday11]

Excellent, this works too but what does the double negative sign do, reverse
the order? Where is this format found in Excel 'numbers tab and what does the
back slash forward slash mean?

Try this:

A1 = 20070703

Meaning: yyyy mm dd

=--TEXT(A1,"0000\/00\/00")

Format as Date

--
Biff
Microsoft Excel MVP

thanks, this helped alot with our dates formatted like 20070703 for
7/1/2007
with a little editing to
=DATE(LEFT(A9,4),MID(A9,5,2),RIGHT(A9,2)). Now I can use DAYS360 to
compare
with dates in the MM/DD/YYYY format

You can convert the numeric string to a date like this.

the year is one digit in the source data
Assuming the year is *always* in the 2000 decade

A1 = 10501
B1 = 5/1/2001

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))

Returns 5/1/2001

So, to comapre the 2 dates:

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1

--
Biff
Microsoft Excel MVP


Hello!

I have a worksheet containing two datasets. Each set of data has
associated
dates--but they are entered as different numbers. One set has the date
in
format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year
is
one
digit in the source data). The other set has the date entered as a
date,
i.e. 5/1/2001. I have the columns set up so that they display the same
numbers, but the original data within the columns is formatted
differently.

I have to look at the data to make sure that the values in the two
columns
match. However, it's a huge dataset, so doing this manually is going
to
take
a long time. I wanted to insert a logical argument
[=IF(value1=value2,true,false] in a separate column to save time, but
since
the source data are in different formats, I get a FALSE every time.

Any ideas on how I convert these beasts into identical formats so that
I
can
use the logical argument strategy? The displayed numbers must not be
changed
to the odd Excel date-counting format, unfortunately--if that were the
case
it wouldn't be so difficult.

Thanks for any suggestions,

lisa

 

[T. Valko]

what does the double negative sign do

The TEXT() function returns a *text* string. The double unary "--" coerces
the text number that is the date serial number to a numeric number and then
formatting the cell displays it as a recognizeable date.

Where is this format found in Excel 'numbers tab

This format is not available in the menu Format>Cells>Number. You'd have to
create it in the Custom category.

what does the back slash forward slash mean?

"0000 \ / 00 \ / 00"

The forward slashes are the slashes used as date separators:

1/1/2009

The backslash is a "delimiter". With the number string: 20070703

The format means the first 4 digits are a group followed by a slash, the
next 2 digits are a group followed by a slash and the last 2 digits are a
group.

This is "briefly" mentioned in Excel help under custom number formats.


--
Biff
Microsoft Excel MVP

Excellent, this works too but what does the double negative sign do,
reverse
the order? Where is this format found in Excel 'numbers tab and what does
the
back slash forward slash mean?

Try this:

A1 = 20070703

Meaning: yyyy mm dd

=--TEXT(A1,"0000\/00\/00")

Format as Date

--
Biff
Microsoft Excel MVP

thanks, this helped alot with our dates formatted like 20070703 for
7/1/2007
with a little editing to
=DATE(LEFT(A9,4),MID(A9,5,2),RIGHT(A9,2)). Now I can use DAYS360 to
compare
with dates in the MM/DD/YYYY format

:

You can convert the numeric string to a date like this.

the year is one digit in the source data
Assuming the year is *always* in the 2000 decade

A1 = 10501
B1 = 5/1/2001

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))

Returns 5/1/2001

So, to comapre the 2 dates:

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1

--
Biff
Microsoft Excel MVP


Hello!

I have a worksheet containing two datasets. Each set of data has
associated
dates--but they are entered as different numbers. One set has the
date
in
format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the
year
is
one
digit in the source data). The other set has the date entered as a
date,
i.e. 5/1/2001. I have the columns set up so that they display the
same
numbers, but the original data within the columns is formatted
differently.

I have to look at the data to make sure that the values in the two
columns
match. However, it's a huge dataset, so doing this manually is
going
to
take
a long time. I wanted to insert a logical argument
[=IF(value1=value2,true,false] in a separate column to save time,
but
since
the source data are in different formats, I get a FALSE every time.

Any ideas on how I convert these beasts into identical formats so
that
I
can
use the logical argument strategy? The displayed numbers must not
be
changed
to the odd Excel date-counting format, unfortunately--if that were
the
case
it wouldn't be so difficult.

Thanks for any suggestions,

lisa

 

[Fred Smith]

I would also verify whether you want the DAYS360 function. I'll bet not.
Days360 will return 30 days between Feb 1st and March 1st of any year. If
you want the proper number of days, just subtract, as in:

=b1-a1

Regards,
Fred.

thanks, this helped alot with our dates formatted like 20070703 for
7/1/2007
with a little editing to
=DATE(LEFT(A9,4),MID(A9,5,2),RIGHT(A9,2)). Now I can use DAYS360 to
compare
with dates in the MM/DD/YYYY format

You can convert the numeric string to a date like this.

the year is one digit in the source data

Assuming the year is *always* in the 2000 decade

A1 = 10501
B1 = 5/1/2001

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))

Returns 5/1/2001

So, to comapre the 2 dates:

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1

--
Biff
Microsoft Excel MVP

Hello!

I have a worksheet containing two datasets. Each set of data has
associated
dates--but they are entered as different numbers. One set has the date
in
format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the year
is
one
digit in the source data). The other set has the date entered as a
date,
i.e. 5/1/2001. I have the columns set up so that they display the same
numbers, but the original data within the columns is formatted
differently.

I have to look at the data to make sure that the values in the two
columns
match. However, it's a huge dataset, so doing this manually is going
to
take
a long time. I wanted to insert a logical argument
[=IF(value1=value2,true,false] in a separate column to save time, but
since
the source data are in different formats, I get a FALSE every time.

Any ideas on how I convert these beasts into identical formats so that
I
can
use the logical argument strategy? The displayed numbers must not be
changed
to the odd Excel date-counting format, unfortunately--if that were the
case
it wouldn't be so difficult.

Thanks for any suggestions,

lisa