Date conversions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Hi Richard,

With TODAY() in A1:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| 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
 
=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"))
 
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?
 
Back
Top