Date conversions
 

OS: MS XP
EXCEL: 2000

The only value in the database that represents a date is a number in the
format 20070107. This would be 01/01/07.

So:
20070130 = 01/30/2007
20070131 = 01/31/2007
20070201 = 02/01/2007

I need to link into this table using the Function VLookUp.
20070107, 17
20070108, 23
20070109, 15

The values returned would be 12, 23 and 15.

The dates are changing all the time and I use the Now() function to get the
date. I dont want to use a look-up table to cross-reference the date.

So how to I convert the Value returned by the Now() function into what I need.

Example:
Date Now() I need
01/30/2007 39112 20070130
01/31/2007 39113 20070131
02/01/2007 39114 20070201

How do I convert the value returned by Now() into what I need without a
cross refferance table? Ex: 39113 into 20070201.

Thanks in advance.

--
Richard

Date conversions
 

Hi Richard,

With TODAY() in A1:

=YEAR(A1)&MONTH(A1)&DAY(A1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Richard" wrote in message ...
| OS: MS XP
| EXCEL: 2000
|
| The only value in the database that represents a date is a number in the
| format 20070107. This would be 01/01/07.
|
| So:
| 20070130 = 01/30/2007
| 20070131 = 01/31/2007
| 20070201 = 02/01/2007
|
| I need to link into this table using the Function VLookUp.
| 20070107, 17
| 20070108, 23
| 20070109, 15
|
| The values returned would be 12, 23 and 15.
|
| The dates are changing all the time and I use the Now() function to get the
| date. I don't want to use a look-up table to cross-reference the date.
|
| So how to I convert the Value returned by the Now() function into what I need.
|
| Example:
| Date Now() I need
| 01/30/2007 39112 20070130
| 01/31/2007 39113 20070131
| 02/01/2007 39114 20070201
|
| How do I convert the value returned by Now() into what I need without a
| cross refferance table? Ex: 39113 into 20070201.
|
| Thanks in advance.
|
| --
| Richard

Date conversions
 

=TEXT(NOW(),"yyyymmdd") will convert the result into a text representation in
the form you described. If your table has numbers rather than text, use
=VALUE(TEXT(NOW(),"yyyymmdd"))

"Richard" wrote:

OS: MS XP
EXCEL: 2000

The only value in the database that represents a date is a number in the
format 20070107. This would be 01/01/07.

So:
20070130 = 01/30/2007
20070131 = 01/31/2007
20070201 = 02/01/2007

I need to link into this table using the Function VLookUp.
20070107, 17
20070108, 23
20070109, 15

The values returned would be 12, 23 and 15.

The dates are changing all the time and I use the Now() function to get the
date. I dont want to use a look-up table to cross-reference the date.

So how to I convert the Value returned by the Now() function into what I need.

Example:
Date Now() I need
01/30/2007 39112 20070130
01/31/2007 39113 20070131
02/01/2007 39114 20070201

How do I convert the value returned by Now() into what I need without a
cross refferance table? Ex: 39113 into 20070201.

Thanks in advance.

--
Richard

Date conversions
 

Hi,

Not sure if this will but if all your data is in the form are dates then you
don't need to convert anything, excel will lookup the dates in any format.

example:
A1= 01/01/2007
B1= hello
A2= 01/01/2007
A3= 39083

if you use a vlookup on A2 and A3 on the range A1:B1 , you should get the
same result "hello".


HTH
Jean-Guy

"bpeltzer" wrote:

=TEXT(NOW(),"yyyymmdd") will convert the result into a text representation in
the form you described. If your table has numbers rather than text, use
=VALUE(TEXT(NOW(),"yyyymmdd"))

"Richard" wrote:

OS: MS XP
EXCEL: 2000

The only value in the database that represents a date is a number in the
format 20070107. This would be 01/01/07.

So:
20070130 = 01/30/2007
20070131 = 01/31/2007
20070201 = 02/01/2007

I need to link into this table using the Function VLookUp.
20070107, 17
20070108, 23
20070109, 15

The values returned would be 12, 23 and 15.

The dates are changing all the time and I use the Now() function to get the
date. I dont want to use a look-up table to cross-reference the date.

So how to I convert the Value returned by the Now() function into what I need.

Example:
Date Now() I need
01/30/2007 39112 20070130
01/31/2007 39113 20070131
02/01/2007 39114 20070201

How do I convert the value returned by Now() into what I need without a
cross refferance table? Ex: 39113 into 20070201.

Thanks in advance.

--
Richard

Date conversions
 

=DATE(YEAR(A1),MONTH(A1),DAY(A1))

Format cell as: yyyymmdd


"Richard" wrote:

OS: MS XP
EXCEL: 2000

The only value in the database that represents a date is a number in the
format 20070107. This would be 01/01/07.

So:
20070130 = 01/30/2007
20070131 = 01/31/2007
20070201 = 02/01/2007

I need to link into this table using the Function VLookUp.
20070107, 17
20070108, 23
20070109, 15

The values returned would be 12, 23 and 15.

The dates are changing all the time and I use the Now() function to get the
date. I dont want to use a look-up table to cross-reference the date.

So how to I convert the Value returned by the Now() function into what I need.

Example:
Date Now() I need
01/30/2007 39112 20070130
01/31/2007 39113 20070131
02/01/2007 39114 20070201

How do I convert the value returned by Now() into what I need without a
cross refferance table? Ex: 39113 into 20070201.

Thanks in advance.

--
Richard

Date conversions
 

Do you need to bother with =DATE(YEAR(A1),MONTH(A1),DAY(A1)) ?
Doesn't that get you back where you started from?
Won't =A1 do the same, if you format the cell as you've suggested?
--
David Biddulph

"Teethless mama" wrote in message
...
=DATE(YEAR(A1),MONTH(A1),DAY(A1))

Format cell as: yyyymmdd


"Richard" wrote:

OS: MS XP
EXCEL: 2000

The only value in the database that represents a date is a number in the
format 20070107. This would be 01/01/07.

So:
20070130 = 01/30/2007
20070131 = 01/31/2007
20070201 = 02/01/2007

I need to link into this table using the Function VLookUp.
20070107, 17
20070108, 23
20070109, 15

The values returned would be 12, 23 and 15.

The dates are changing all the time and I use the Now() function to get
the
date. I don't want to use a look-up table to cross-reference the date.

So how to I convert the Value returned by the Now() function into what I
need.

Example:
Date Now() I need
01/30/2007 39112 20070130
01/31/2007 39113 20070131
02/01/2007 39114 20070201

How do I convert the value returned by Now() into what I need without a
cross refferance table? Ex: 39113 into 20070201.

Thanks in advance.

--
Richard