| Home |
| Search |
| Today's Posts |
|
|
|
#1
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
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
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
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
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
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
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Converting Text Dates to Serial Numbers | Excel Worksheet Functions | |||
| Serial Numbers converted to text string | Excel Worksheet Functions | |||
| Serial Numbers that begin with Text | Excel Discussion (Misc queries) | |||
| Convert serial numbers to dates | Excel Worksheet Functions | |||
| Changing date serial numbers | Excel Discussion (Misc queries) | |||
![George[_5_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
