| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| insert date | Excel Worksheet Functions | |||
| Date format issue | New Users to Excel | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Another Date issue. | Excel Worksheet Functions | |||
| Date Conversions from UTC to EST | Excel Worksheet Functions | |||
Hybrid Mode
