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
Nydee - Aug 7, 2013 at 12:01 AM
Related:
- Excel date problem
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
- Excel marksheet - Guide
- How to take screenshot in excel - Guide
3 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Aug 6, 2013 at 11:51 PM
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.
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.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Aug 2, 2013 at 04:52 AM
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
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
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.
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.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Aug 5, 2013 at 12:17 AM
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
message the uplodaded file name
Aug 7, 2013 at 12:01 AM