Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problem
I have a spreadsheet with data generated by another program. There is one
column with dates in this format: 04-May-05. From what I can tell, Excel is treat this as a string, not a date. Next to this column, I use the following formula to convert a date such as 04-May-05 into 04-09-05 (this new column is formatted as yy-mm-dd). =DATE(RIGHT(D2,2),LOOKUP(MID(D2,4,3),Table!$A$1:$B $12),LEFT(D2,2)) The lookup is for changing Jan into 01, Feb into 02, etc. PROBLEM: When I do a pivot table with the column, a date such as 04-May-05 appears as 1904-09-05, rather than 2004-09-05. What should I do to make sure that the years are 20xx and not 19xx? In fact, is there a better way to do the date conversion? My method seems rather clumsy. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problem
Have you tried using the DateValue() function. It takes a single argument
that is a string and (if it can) converts it to a date... So if your string is in call A1 the in another cell add the formula =datevalue(A1) -- HTH... Jim Thomlinson "Shatin" wrote: I have a spreadsheet with data generated by another program. There is one column with dates in this format: 04-May-05. From what I can tell, Excel is treat this as a string, not a date. Next to this column, I use the following formula to convert a date such as 04-May-05 into 04-09-05 (this new column is formatted as yy-mm-dd). =DATE(RIGHT(D2,2),LOOKUP(MID(D2,4,3),Table!$A$1:$B $12),LEFT(D2,2)) The lookup is for changing Jan into 01, Feb into 02, etc. PROBLEM: When I do a pivot table with the column, a date such as 04-May-05 appears as 1904-09-05, rather than 2004-09-05. What should I do to make sure that the years are 20xx and not 19xx? In fact, is there a better way to do the date conversion? My method seems rather clumsy. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problem
Try using:
=DATE(20 & RIGHT(D2,2),LOOKUP(MID(D2,4,3),Table!$A$1:$B$12),L EFT(D2,2)) If I have amended your formula correct you should not be passing 2005 rather than 05 as the year, thus taking out any ambiguity. "Shatin" wrote in message ... I have a spreadsheet with data generated by another program. There is one column with dates in this format: 04-May-05. From what I can tell, Excel is treat this as a string, not a date. Next to this column, I use the following formula to convert a date such as 04-May-05 into 04-09-05 (this new column is formatted as yy-mm-dd). =DATE(RIGHT(D2,2),LOOKUP(MID(D2,4,3),Table!$A$1:$B $12),LEFT(D2,2)) The lookup is for changing Jan into 01, Feb into 02, etc. PROBLEM: When I do a pivot table with the column, a date such as 04-May-05 appears as 1904-09-05, rather than 2004-09-05. What should I do to make sure that the years are 20xx and not 19xx? In fact, is there a better way to do the date conversion? My method seems rather clumsy. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problem
Yes, DateValue() works fine! In fact, I did try DateValue initially. What I
got was number. I forgot that I needed to format it into a date format and so thought it didn't work. Now everything is OK. Thanks! "Jim Thomlinson" wrote in message ... Have you tried using the DateValue() function. It takes a single argument that is a string and (if it can) converts it to a date... So if your string is in call A1 the in another cell add the formula =datevalue(A1) -- HTH... Jim Thomlinson "Shatin" wrote: I have a spreadsheet with data generated by another program. There is one column with dates in this format: 04-May-05. From what I can tell, Excel is treat this as a string, not a date. Next to this column, I use the following formula to convert a date such as 04-May-05 into 04-09-05 (this new column is formatted as yy-mm-dd). =DATE(RIGHT(D2,2),LOOKUP(MID(D2,4,3),Table!$A$1:$B $12),LEFT(D2,2)) The lookup is for changing Jan into 01, Feb into 02, etc. PROBLEM: When I do a pivot table with the column, a date such as 04-May-05 appears as 1904-09-05, rather than 2004-09-05. What should I do to make sure that the years are 20xx and not 19xx? In fact, is there a better way to do the date conversion? My method seems rather clumsy. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exel increment date problem wrt todays date. | Excel Worksheet Functions | |||
Date Formula Problem - Leave date blank if Null | Excel Worksheet Functions | |||
Date problem | Excel Worksheet Functions | |||
Date problem! | Excel Discussion (Misc queries) | |||
Date Problem | Excel Programming |