|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 2nd, 2008, 08:48 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing text dates to date serial numbers
I have the following in a spread sheet:
Pay Date 01/05/2007 #VALUE! 01/05/2007 #VALUE! 01/05/2007 #VALUE! 01/11/2007 #VALUE! the Pay Date is in column A1 and the function is in B2 the function that returns the "#VALUE!" error is "=DATEVALUE(A2) why do I get an error? I'm using Excel 2003 (11.8146.8132) SP2 
|
|
#2
January 2nd, 2008, 09:10 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing text dates to date serial numbers
DATEVALUE requires that the argument be a *TEXT* representation of a date.
If your dates are true Excel dates then they're NUMBERS. If you want the date serial number: =A1 Format as GENERAL or NUMBER -- Biff Microsoft Excel MVP "George" wrote in message ... I have the following in a spread sheet: Pay Date 01/05/2007 #VALUE! 01/05/2007 #VALUE! 01/05/2007 #VALUE! 01/11/2007 #VALUE! the Pay Date is in column A1 and the function is in B2 the function that returns the "#VALUE!" error is "=DATEVALUE(A2) why do I get an error? I'm using Excel 2003 (11.8146.8132) SP2
|
|
#3
January 2nd, 2008, 09:31 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing text dates to date serial numbers
When I type =A2 in cell C2, I get 01/05/2007 in cell C2, when I put a '
infront of the 01/05/2007 to force it to be text, I still get the #VALOE! error Geo "T. Valko" wrote: DATEVALUE requires that the argument be a *TEXT* representation of a date. If your dates are true Excel dates then they're NUMBERS. If you want the date serial number: =A1 Format as GENERAL or NUMBER -- Biff Microsoft Excel MVP "George" wrote in message ... I have the following in a spread sheet: Pay Date 01/05/2007 #VALUE! 01/05/2007 #VALUE! 01/05/2007 #VALUE! 01/11/2007 #VALUE! the Pay Date is in column A1 and the function is in B2 the function that returns the "#VALUE!" error is "=DATEVALUE(A2) why do I get an error? I'm using Excel 2003 (11.8146.8132) SP2
|
|
#4
January 2nd, 2008, 09:58 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing text dates to date serial numbers
Not sure why you're wanting to use DATEVALUE.
If I type this into cell A2: '1/1/2008 =DATEVALUE(A2) returns 39448. This will do the same thing: =A2+0 If A2 = a true Excel date and all you want is the date serial number (which is what DATEVALUE returns) this is easier to use: =A2 And format as GENERAL or NUMBER -- Biff Microsoft Excel MVP "George" wrote in message ... When I type =A2 in cell C2, I get 01/05/2007 in cell C2, when I put a ' infront of the 01/05/2007 to force it to be text, I still get the #VALOE! error Geo "T. Valko" wrote: DATEVALUE requires that the argument be a *TEXT* representation of a date. If your dates are true Excel dates then they're NUMBERS. If you want the date serial number: =A1 Format as GENERAL or NUMBER -- Biff Microsoft Excel MVP "George" wrote in message ... I have the following in a spread sheet: Pay Date 01/05/2007 #VALUE! 01/05/2007 #VALUE! 01/05/2007 #VALUE! 01/11/2007 #VALUE! the Pay Date is in column A1 and the function is in B2 the function that returns the "#VALUE!" error is "=DATEVALUE(A2) why do I get an error? I'm using Excel 2003 (11.8146.8132) SP2
|
|
#5
January 2nd, 2008, 10:20 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing text dates to date serial numbers
I'm just trying to learn from reading the posts here.
Just like you, I'm wondering what George wants to do with the contents of cells B2 and down the column. Perhaps if George would tell you, you could help him better. "T. Valko" wrote: Not sure why you're wanting to use DATEVALUE. If I type this into cell A2: '1/1/2008 =DATEVALUE(A2) returns 39448. This will do the same thing: =A2+0 If A2 = a true Excel date and all you want is the date serial number (which is what DATEVALUE returns) this is easier to use: =A2 And format as GENERAL or NUMBER -- Biff Microsoft Excel MVP "George" wrote in message ... When I type =A2 in cell C2, I get 01/05/2007 in cell C2, when I put a ' infront of the 01/05/2007 to force it to be text, I still get the #VALOE! error Geo "T. Valko" wrote: DATEVALUE requires that the argument be a *TEXT* representation of a date. If your dates are true Excel dates then they're NUMBERS. If you want the date serial number: =A1 Format as GENERAL or NUMBER -- Biff Microsoft Excel MVP "George" wrote in message ... I have the following in a spread sheet: Pay Date 01/05/2007 #VALUE! 01/05/2007 #VALUE! 01/05/2007 #VALUE! 01/11/2007 #VALUE! the Pay Date is in column A1 and the function is in B2 the function that returns the "#VALUE!" error is "=DATEVALUE(A2) why do I get an error? I'm using Excel 2003 (11.8146.8132) SP2
|
|
#6
January 2nd, 2008, 10:30 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing text dates to date serial numbers
I'm ready and willing!
-- Biff Microsoft Excel MVP "TRYING" wrote in message ... I'm just trying to learn from reading the posts here. Just like you, I'm wondering what George wants to do with the contents of cells B2 and down the column. Perhaps if George would tell you, you could help him better. "T. Valko" wrote: Not sure why you're wanting to use DATEVALUE. If I type this into cell A2: '1/1/2008 =DATEVALUE(A2) returns 39448. This will do the same thing: =A2+0 If A2 = a true Excel date and all you want is the date serial number (which is what DATEVALUE returns) this is easier to use: =A2 And format as GENERAL or NUMBER -- Biff Microsoft Excel MVP "George" wrote in message ... When I type =A2 in cell C2, I get 01/05/2007 in cell C2, when I put a ' infront of the 01/05/2007 to force it to be text, I still get the #VALOE! error Geo "T. Valko" wrote: DATEVALUE requires that the argument be a *TEXT* representation of a date. If your dates are true Excel dates then they're NUMBERS. If you want the date serial number: =A1 Format as GENERAL or NUMBER -- Biff Microsoft Excel MVP "George" wrote in message ... I have the following in a spread sheet: Pay Date 01/05/2007 #VALUE! 01/05/2007 #VALUE! 01/05/2007 #VALUE! 01/11/2007 #VALUE! the Pay Date is in column A1 and the function is in B2 the function that returns the "#VALUE!" error is "=DATEVALUE(A2) why do I get an error? I'm using Excel 2003 (11.8146.8132) SP2
|
|
#7
January 2nd, 2008, 10:44 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing text dates to date serial numbers
In B2: =TEXT(A1,"m/d/yyyy")
format cell as General, copy down "George" wrote: I have the following in a spread sheet: Pay Date 01/05/2007 #VALUE! 01/05/2007 #VALUE! 01/05/2007 #VALUE! 01/11/2007 #VALUE! the Pay Date is in column A1 and the function is in B2 the function that returns the "#VALUE!" error is "=DATEVALUE(A2) why do I get an error? I'm using Excel 2003 (11.8146.8132) SP2
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
