Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format troubles
I have an Excel worksheet that has dates entered as General format like the
following: 19970103 I am trying to convert these to dates (MM/DD/YYYY), but everything I try does not work. Any help is greatly appreciated! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format troubles
Hi
in one column enter the formula as follow =DATE(VALUE(LEFT(A1,4)),VALUE(MID(A1,1,1)),VALUE(R IGHT(A1,1))) "jjackson97" wrote: I have an Excel worksheet that has dates entered as General format like the following: 19970103 I am trying to convert these to dates (MM/DD/YYYY), but everything I try does not work. Any help is greatly appreciated! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format troubles
I am assuming 01 is your month, so it would be
=DATE(LEFT(A1,4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2)) if 01 is your day, then =DATE(LEFT(A1,4),RIGHT(A1,2),LEFT(RIGHT(A1,4),2)) Then format the date to what you want. "jjackson97" wrote: I have an Excel worksheet that has dates entered as General format like the following: 19970103 I am trying to convert these to dates (MM/DD/YYYY), but everything I try does not work. Any help is greatly appreciated! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format troubles
Very close. I left off that some of the data was not with leading 0's for
the date an month. So there was stuff like 19951112, which got messy with your formula. However you got me in the right direction to tweak it to: =DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,5,2)),VALUE(R IGHT(A2,2))) For that I am grateful! THANKS! "Eduardo" wrote: Hi in one column enter the formula as follow =DATE(VALUE(LEFT(A1,4)),VALUE(MID(A1,1,1)),VALUE(R IGHT(A1,1))) "jjackson97" wrote: I have an Excel worksheet that has dates entered as General format like the following: 19970103 I am trying to convert these to dates (MM/DD/YYYY), but everything I try does not work. Any help is greatly appreciated! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format troubles
your welcome, have a great weekend
"jjackson97" wrote: Very close. I left off that some of the data was not with leading 0's for the date an month. So there was stuff like 19951112, which got messy with your formula. However you got me in the right direction to tweak it to: =DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,5,2)),VALUE(R IGHT(A2,2))) For that I am grateful! THANKS! "Eduardo" wrote: Hi in one column enter the formula as follow =DATE(VALUE(LEFT(A1,4)),VALUE(MID(A1,1,1)),VALUE(R IGHT(A1,1))) "jjackson97" wrote: I have an Excel worksheet that has dates entered as General format like the following: 19970103 I am trying to convert these to dates (MM/DD/YYYY), but everything I try does not work. Any help is greatly appreciated! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format troubles
A shorter method with less function calls...
=--TEXT(A2,"0000-00-00") You can format the cell in the date format you want. -- Rick (MVP - Excel) "jjackson97" wrote in message ... Very close. I left off that some of the data was not with leading 0's for the date an month. So there was stuff like 19951112, which got messy with your formula. However you got me in the right direction to tweak it to: =DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,5,2)),VALUE(R IGHT(A2,2))) For that I am grateful! THANKS! "Eduardo" wrote: Hi in one column enter the formula as follow =DATE(VALUE(LEFT(A1,4)),VALUE(MID(A1,1,1)),VALUE(R IGHT(A1,1))) "jjackson97" wrote: I have an Excel worksheet that has dates entered as General format like the following: 19970103 I am trying to convert these to dates (MM/DD/YYYY), but everything I try does not work. Any help is greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert european foreign date format to US date format | Excel Worksheet Functions | |||
format troubles - possible cut/paste issue | Excel Discussion (Misc queries) | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
date sorting troubles | Setting up and Configuration of Excel | |||
Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) |