L
Legal Learning
I want to enter my date like this: 010105 abd have it format it like this:
January 1, 2005. Help!
January 1, 2005. Help!
J
JE McGimpsey
F
Frank Kabel
Hi
you'll need VBA for this. You may try the following addin to just achieve
this:
http://www.xldynamic.com/source/xld.QDEDownload.html
you'll need VBA for this. You may try the following addin to just achieve
this:
http://www.xldynamic.com/source/xld.QDEDownload.html
G
Guest
i'm afraid you might have a slight problem.
In order for xl to recognize a date, it must be in a
format that it can recognize as a date.
with the cell formated at date, xl recognizes 101005 as
the date number and formats it to aug 31, 1927.
In order for xl to recognize a date, it must be in a
format that it can recognize as a date.
with the cell formated at date, xl recognizes 101005 as
the date number and formats it to aug 31, 1927.
A
Alex
CLG
This can be done in Excel. It is a little 'messy' but here we go.
Enter your dates in column A. Just type them as numbers no matter how they
appear. For example,
010105 will appear as 10105
260105 will appear as 260105
Now in Cell B1 cut and paste the following formula:
=VALUE(IF(LEN(A1)=5,"0"&LEFT(A1,1)&"/"&MID(A1,2,2)&"/"&RIGHT(A1,2),LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2)))
This will turn your cell values into a number format that Excel recognises
as a date.
Now highlight all the cells in column B and right click the mouse and
select...
<Format Cells...> and select <Custom> in the 'category' box.
In the space under 'Type:' add the following....
mmmm d, yyyy
This will convert all your dates to how you want them.
Now select column B and select <copy>. Then select cell A1 and select <Paste
special> and select <Values>.
Now column A will have your dates just as you like them.
This process is not ideal when working with dates but it saves using VBA!
If you have any problems then please do not hesitate to contact me. I will
endeavour to assist further.
Regards
Alex
This can be done in Excel. It is a little 'messy' but here we go.
Enter your dates in column A. Just type them as numbers no matter how they
appear. For example,
010105 will appear as 10105
260105 will appear as 260105
Now in Cell B1 cut and paste the following formula:
=VALUE(IF(LEN(A1)=5,"0"&LEFT(A1,1)&"/"&MID(A1,2,2)&"/"&RIGHT(A1,2),LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2)))
This will turn your cell values into a number format that Excel recognises
as a date.
Now highlight all the cells in column B and right click the mouse and
select...
<Format Cells...> and select <Custom> in the 'category' box.
In the space under 'Type:' add the following....
mmmm d, yyyy
This will convert all your dates to how you want them.
Now select column B and select <copy>. Then select cell A1 and select <Paste
special> and select <Values>.
Now column A will have your dates just as you like them.
This process is not ideal when working with dates but it saves using VBA!
If you have any problems then please do not hesitate to contact me. I will
endeavour to assist further.
Regards
Alex
A
Alex
Frank Kabel
Check out my post. I found a way of solving the date problem in Excel. See
what you think. It is a bit clumsy but as a one-off it works ok.
Alex
Check out my post. I found a way of solving the date problem in Excel. See
what you think. It is a bit clumsy but as a one-off it works ok.
Alex
A
Alex
CLG
I forgot to add...once you have typed the formula I gave into cell B1 you
can just drag the formula down to work out all the other dates you need to
convert...
Alex
I forgot to add...once you have typed the formula I gave into cell B1 you
can just drag the formula down to work out all the other dates you need to
convert...
Alex
F
Frank Kabel
Hi Alex
though this works this has one drawback: It's not in the same cell
If this is o.k. for the OP then he may use a formula solution 8less
overhead).
BTW a shorter formula to achieve this would be:
=--TEXT(A1,"00-00-00")
though this works this has one drawback: It's not in the same cell
If this is o.k. for the OP then he may use a formula solution 8less
overhead).
BTW a shorter formula to achieve this would be:
=--TEXT(A1,"00-00-00")