| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
change date from 020315 to 03/15/02
how do I change the format in this date?
|
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change date from 020315 to 03/15/02
Assuming you original data is formatted as Custom yymmdd, change the format to:
mm/dd/yyyy -- Gary''s Student - gsnu200770 "CHARI" wrote: how do I change the format in this date? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change date from 020315 to 03/15/02
"Gary''s Student" wrote: Assuming you original data is formatted as Custom yymmdd, change the format to: mm/dd/yyyy -- Gary''s Student - gsnu200770 "CHARI" wrote: how do I change the format in this date? No, it's not formatted that way. The original format was 1020315 and I removed the 1 but I can't get the formatting to change. This was originally a text file I imported. Should I import it different or can I change it as is? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change date from 020315 to 03/15/02
Try this formula instead:
=DATE(2000+LEFT(A1,2),--MID(A1,3,2),RIGHT(A1,2)) -- Gary''s Student - gsnu200770 "CHARI" wrote: "Gary''s Student" wrote: Assuming you original data is formatted as Custom yymmdd, change the format to: mm/dd/yyyy -- Gary''s Student - gsnu200770 "CHARI" wrote: how do I change the format in this date? No, it's not formatted that way. The original format was 1020315 and I removed the 1 but I can't get the formatting to change. This was originally a text file I imported. Should I import it different or can I change it as is? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change date from 020315 to 03/15/02
"Gary''s Student" wrote: Try this formula instead: =DATE(2000+LEFT(A1,2),--MID(A1,3,2),RIGHT(A1,2)) -- Gary''s Student - gsnu200770 "CHARI" wrote: "Gary''s Student" wrote: Assuming you original data is formatted as Custom yymmdd, change the format to: mm/dd/yyyy -- Gary''s Student - gsnu200770 "CHARI" wrote: how do I change the format in this date? No, it's not formatted that way. The original format was 1020315 and I removed the 1 but I can't get the formatting to change. This was originally a text file I imported. Should I import it different or can I change it as is? I got back 08/15/2011 ..... not 03/15/02 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change date from 020315 to 03/15/02
Is the date supposed to be March 15, 2002?
Cliff Edwards |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change date from 020315 to 03/15/02
If the date format from your database is cyymmdd, here is the formula
to convert it to a value that Excel will recognize as the equivalent date: =IF(LEN(A1)=7,VALUE(CONCATENATE(MID(A1,4,2),"/",RIGHT(A1,2),"/",MID(A1,2,2))),VALUE(CONCATENATE(MID(A1,3,2), "/",RIGHT(A1,2),"/",LEFT(A1,2)))) The formula also takes into account whether the date format prior to 2000 was mmddyy and converts to date serial. You can format the date serial in any way you want. Cliff Edwards |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change date from 020315 to 03/15/02
"ward376" wrote: Is the date supposed to be March 15, 2002? Cliff Edwards yes, |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change date from 020315 to 03/15/02
"ward376" wrote: If the date format from your database is cyymmdd, here is the formula to convert it to a value that Excel will recognize as the equivalent date: =IF(LEN(A1)=7,VALUE(CONCATENATE(MID(A1,4,2),"/",RIGHT(A1,2),"/",MID(A1,2,2))),VALUE(CONCATENATE(MID(A1,3,2), "/",RIGHT(A1,2),"/",LEFT(A1,2)))) The formula also takes into account whether the date format prior to 2000 was mmddyy and converts to date serial. You can format the date serial in any way you want. Cliff Edwards PERFECT, THANK YOU!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| date in Cell to change colors if the date is beyond today's date | Excel Discussion (Misc queries) | |||
| Use date modified to change format & create filter to track change | Excel Worksheet Functions | |||
| Change conditional formatting to coloured alternate rows dependent on a change in date? | Excel Programming | |||
| change background row color with change of date in a cell | Excel Discussion (Misc queries) | |||
| Make date change in excel to current date when opening daily? | Excel Discussion (Misc queries) | |||
Linear Mode
