Excel date problem

Solved/Closed
Nydee
Posts
1
Registration date
Thursday August 1, 2013
Status
Member
Last seen
August 1, 2013
- Aug 1, 2013 at 09:18 PM
 Nydee - Aug 7, 2013 at 12:01 AM
Hi,

How can I change the date format from a format such as 22042013 - (this is not a text format). One formula I have been given is:

=IF(LEN(R14)=8,(LEFT(R14,2) & "/" & LEFT(RIGHT(R14,6),2) & "/"& RIGHT(R14,4)),(LEFT(R14,1) & "/" & LEFT(RIGHT(R14,6),2) & "/"& RIGHT(R14,4)))

These dates appear in a report I receive each month and I need dates that are more manageable :)

thanks

3 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Aug 6, 2013 at 11:51 PM
Nydee - Aug 6, 2013 08:41PM

a1 has value 22012013
try this
1.select A1
2. in xl 2007 click data ribbon and text to columns (somewhere in the middle). in xl2003 cliclk texttocolumns
3. in the window that comes up check "delimited"
4.click "next"
5.click tab
6. click next
7. check "date"
8.click arrow at the right end of date
9.choose DMY
10.click finish

the result depends upom the regional configuration for the data.
In spite of regional configuration yu can format as you like .

this you can do for a number of contigous cells. you have to highlight the cells and then follow ten steps


this takes ten steps
my formula you can type in B1 and copy down in one stroke. how
take the cursor to right bottom of B1 when cursor turns to black +
click that + and the formula is copied all the way down as long as data is available in column A.

whichever you are comfortable with you choose.
1
Many many thanks!! :)
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Aug 2, 2013 at 04:52 AM
what is your regional definiton of date entered in a cell
mine is
mm/dd/yyyy
if yours also is same then
type 22012013 in A1 and format it as number with 0 decimals.
otherwise it may automatically in scintific format
now enter this formula in B1
=(MID(A1,3,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4))+0
you will get perhaps
41386
format in any date format.
modify formula if the date is entered differently in your regio
0
Thank you venkat1926 - unfortunately I come up with a #VALUE! error - I tried typing it in and also copy and paste and neither worked. :(
0
Hi venkat1926 - thank you for your assistance - I met with an IT guy who showed me a really easy and simple solution to my problem. No more huge formulas to use! :)
When importing the document from text all I have to do is
select delimited - next
Select Tab and Other and enter | - then next ( it was here I was selecting finish and not getting the date format I wanted)
NOW I select next
Select date and dmy (my date format) high lite the date columns then click on finish
hey presto I have what I wanted - so easy!! it also allows me to sort by month which is what I need.
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Aug 5, 2013 at 12:17 AM
open speedyshar.com and upload your file (atleast a small extract of your file) and
message the uplodaded file name
0