software

Is there an easy way to change numbers within a cell into dates with a
format, or formula within a macro? For example:

The Number in the cells are as follows:
406 which stands for April 2006
1205 Which stands for December 2005

I want to change those numbers that are there into a nicer looking date
format, like Apr-06 or something. THANKS!!!


If the value is in A1 Try =DATE(MOD(A1,100) 2000,TRUNC(A1/100),1)

format mmm-yy--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=544288quot;bodhisatvaofboogiequot; gt; wrote in
message ...
gt; Is there an easy way to change numbers within a cell into dates with a
gt; format, or formula within a macro? For example:
gt;
gt; The Number in the cells are as follows:
gt; 406 which stands for April 2006
gt; 1205 Which stands for December 2005
gt;
gt; I want to change those numbers that are there into a nicer looking date
gt; format, like Apr-06 or something. THANKS!!!

=DATE(2000 RIGHT(A1,2),LEFT(A1,LEN(A1)-2),1)
Format as mmm-yy
--
David Biddulph
Try something like this:

For a number in A1 (eg 406 or 1205)

This formula uses Excel's default year calculation
B1: =DATEVALUE(INT(A1/100)amp;quot;/1/quot;amp;MOD(A1,100))
Format B1 as a date

Does that help?
***********
Regards,
Ron

XL2002, WinXPquot;bodhisatvaofboogiequot; wrote:

gt; Is there an easy way to change numbers within a cell into dates with a
gt; format, or formula within a macro? For example:
gt;
gt; The Number in the cells are as follows:
gt; 406 which stands for April 2006
gt; 1205 Which stands for December 2005
gt;
gt; I want to change those numbers that are there into a nicer looking date
gt; format, like Apr-06 or something. THANKS!!!

Try
=DATE(2000 MOD(A1,100),INT(A1/100),1)

HTH. Best wishes Harald

quot;bodhisatvaofboogiequot; gt; skrev i
melding ...
gt; Is there an easy way to change numbers within a cell into dates with a
gt; format, or formula within a macro? For example:
gt;
gt; The Number in the cells are as follows:
gt; 406 which stands for April 2006
gt; 1205 Which stands for December 2005
gt;
gt; I want to change those numbers that are there into a nicer looking date
gt; format, like Apr-06 or something. THANKS!!!
=DATE(2000 RIGHT(A1,2),(--LEFT(A1,LEN(A1)-2)),1) formatted with the format of
your choice.
--
Gary''s Studentquot;bodhisatvaofboogiequot; wrote:

gt; Is there an easy way to change numbers within a cell into dates with a
gt; format, or formula within a macro? For example:
gt;
gt; The Number in the cells are as follows:
gt; 406 which stands for April 2006
gt; 1205 Which stands for December 2005
gt;
gt; I want to change those numbers that are there into a nicer looking date
gt; format, like Apr-06 or something. THANKS!!!

hmm...I'm confused a little. How are you wanting me to use that formula?
I'm trying to plug it in with a variety of methods with no success. How
would it look wihtin VBE when editing the code of a macro?

Or are you suggesting using it as a conditional format? IF so, I couldn't
get that to work either. break it down simple for me. I am still new to the
whole macro code world THANKS!!!

quot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; For a number in A1 (eg 406 or 1205)
gt;
gt; This formula uses Excel's default year calculation
gt; B1: =DATEVALUE(INT(A1/100)amp;quot;/1/quot;amp;MOD(A1,100))
gt; Format B1 as a date
gt;
gt; Does that help?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;bodhisatvaofboogiequot; wrote:
gt;
gt; gt; Is there an easy way to change numbers within a cell into dates with a
gt; gt; format, or formula within a macro? For example:
gt; gt;
gt; gt; The Number in the cells are as follows:
gt; gt; 406 which stands for April 2006
gt; gt; 1205 Which stands for December 2005
gt; gt;
gt; gt; I want to change those numbers that are there into a nicer looking date
gt; gt; format, like Apr-06 or something. THANKS!!!

Questions/Comments:

1)How is the original number getting in the cell? Is it entered directly in
the cell? imported? Pasted in?

2)The examples you posted would not be interpreted by Excel as dates in the
way you want. Formatting, alone, would not solve that problem. Hence, the
formula approaches posted my me and the other contributors. (By the way, the
formulas would be entered on the worksheet and would reference the quot;date
numberquot; that you want converted.)

3)Were you hoping to run a VBA program to change the number into a date?
(you didn't mention VBA in your original post) How many numbers do you need
converted to dates? For just one or two, running a program may be less
intuitive than using a formula....or just re-entering the value as a true
date.

4)Are there any other requirements that will impact the approach you would
use?

***********
Regards,
Ron

XL2002, WinXPquot;bodhisatvaofboogiequot; wrote:

gt; hmm...I'm confused a little. How are you wanting me to use that formula?
gt; I'm trying to plug it in with a variety of methods with no success. How
gt; would it look wihtin VBE when editing the code of a macro?
gt;
gt; Or are you suggesting using it as a conditional format? IF so, I couldn't
gt; get that to work either. break it down simple for me. I am still new to the
gt; whole macro code world THANKS!!!
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try something like this:
gt; gt;
gt; gt; For a number in A1 (eg 406 or 1205)
gt; gt;
gt; gt; This formula uses Excel's default year calculation
gt; gt; B1: =DATEVALUE(INT(A1/100)amp;quot;/1/quot;amp;MOD(A1,100))
gt; gt; Format B1 as a date
gt; gt;
gt; gt; Does that help?
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP
gt; gt;
gt; gt;
gt; gt; quot;bodhisatvaofboogiequot; wrote:
gt; gt;
gt; gt; gt; Is there an easy way to change numbers within a cell into dates with a
gt; gt; gt; format, or formula within a macro? For example:
gt; gt; gt;
gt; gt; gt; The Number in the cells are as follows:
gt; gt; gt; 406 which stands for April 2006
gt; gt; gt; 1205 Which stands for December 2005
gt; gt; gt;
gt; gt; gt; I want to change those numbers that are there into a nicer looking date
gt; gt; gt; format, like Apr-06 or something. THANKS!!!

The original numbers are being imported from another program by macros
already in place. So I have created a macro to organize that raw data into a
much nicer format more easily read by my clients. That column when imported
looks like that. The original program uses those numbers as a date, the
macro used for importing does not change them into a date, so I wanted to
incorporate a formula into my macro that would make the change to clean it
up. The change would occur for an entire column, not just one or two cells.
SO:

Change all number values in Column X into dates.

The somewhat confusing thing is that when imported the dates are just
numbers, so there are 3 numbers (406) for april 2006, but 4 numbers (1205)
for December 2005. If ALL numbers in that column had 4 value places then I
could simply do a format to a date. BUT, I couldn't figure out how to get it
to work out. SO here I am If you need any other input, let me know.
THANKS!!!

quot;Ron Coderrequot; wrote:

gt; Questions/Comments:
gt;
gt; 1)How is the original number getting in the cell? Is it entered directly in
gt; the cell? imported? Pasted in?
gt;
gt; 2)The examples you posted would not be interpreted by Excel as dates in the
gt; way you want. Formatting, alone, would not solve that problem. Hence, the
gt; formula approaches posted my me and the other contributors. (By the way, the
gt; formulas would be entered on the worksheet and would reference the quot;date
gt; numberquot; that you want converted.)
gt;
gt; 3)Were you hoping to run a VBA program to change the number into a date?
gt; (you didn't mention VBA in your original post) How many numbers do you need
gt; converted to dates? For just one or two, running a program may be less
gt; intuitive than using a formula....or just re-entering the value as a true
gt; date.
gt;
gt; 4)Are there any other requirements that will impact the approach you would
gt; use?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;bodhisatvaofboogiequot; wrote:
gt;
gt; gt; hmm...I'm confused a little. How are you wanting me to use that formula?
gt; gt; I'm trying to plug it in with a variety of methods with no success. How
gt; gt; would it look wihtin VBE when editing the code of a macro?
gt; gt;
gt; gt; Or are you suggesting using it as a conditional format? IF so, I couldn't
gt; gt; get that to work either. break it down simple for me. I am still new to the
gt; gt; whole macro code world THANKS!!!
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Try something like this:
gt; gt; gt;
gt; gt; gt; For a number in A1 (eg 406 or 1205)
gt; gt; gt;
gt; gt; gt; This formula uses Excel's default year calculation
gt; gt; gt; B1: =DATEVALUE(INT(A1/100)amp;quot;/1/quot;amp;MOD(A1,100))
gt; gt; gt; Format B1 as a date
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;bodhisatvaofboogiequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Is there an easy way to change numbers within a cell into dates with a
gt; gt; gt; gt; format, or formula within a macro? For example:
gt; gt; gt; gt;
gt; gt; gt; gt; The Number in the cells are as follows:
gt; gt; gt; gt; 406 which stands for April 2006
gt; gt; gt; gt; 1205 Which stands for December 2005
gt; gt; gt; gt;
gt; gt; gt; gt; I want to change those numbers that are there into a nicer looking date
gt; gt; gt; gt; format, like Apr-06 or something. THANKS!!!

See if this code gets you headed in the right direction:

You didn't mention if there might be gaps, text, or invalid date numbers
interspersed in the the Col_X range, so I allowed for all three.

Paste this code into a General Module

'------Start of Code-------
Option Explicit

Sub ChgImportNum2Date()
Dim rngCell As Range
Dim rngStart As Range
Dim intMaxRow As Integer
Dim intCtr As Integer

With ActiveSheet
intMaxRow = .UsedRange.Rows.Count
Set rngStart = .Range(quot;X1quot;)
For intCtr = 1 To (intMaxRow - 1)
Set rngCell = rngStart.Offset(RowOffset:=intCtr)
If IsNumeric(rngCell) Then
On Error Resume Next
rngCell.Value = DateValue(Int(rngCell / 100) amp; quot;/1/quot; amp; rngCell Mod
100)
rngCell.NumberFormat = quot;mmm - yyquot;
On Error GoTo 0
End If
Next intCtr
End With
Set rngStart = Nothing
Set rngCell = Nothing

End Sub

'------End of Code-------

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXPquot;bodhisatvaofboogiequot; wrote:

gt; The original numbers are being imported from another program by macros
gt; already in place. So I have created a macro to organize that raw data into a
gt; much nicer format more easily read by my clients. That column when imported
gt; looks like that. The original program uses those numbers as a date, the
gt; macro used for importing does not change them into a date, so I wanted to
gt; incorporate a formula into my macro that would make the change to clean it
gt; up. The change would occur for an entire column, not just one or two cells.
gt; SO:
gt;
gt; Change all number values in Column X into dates.
gt;
gt; The somewhat confusing thing is that when imported the dates are just
gt; numbers, so there are 3 numbers (406) for april 2006, but 4 numbers (1205)
gt; for December 2005. If ALL numbers in that column had 4 value places then I
gt; could simply do a format to a date. BUT, I couldn't figure out how to get it
gt; to work out. SO here I am If you need any other input, let me know.
gt; THANKS!!!
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Questions/Comments:
gt; gt;
gt; gt; 1)How is the original number getting in the cell? Is it entered directly in
gt; gt; the cell? imported? Pasted in?
gt; gt;
gt; gt; 2)The examples you posted would not be interpreted by Excel as dates in the
gt; gt; way you want. Formatting, alone, would not solve that problem. Hence, the
gt; gt; formula approaches posted my me and the other contributors. (By the way, the
gt; gt; formulas would be entered on the worksheet and would reference the quot;date
gt; gt; numberquot; that you want converted.)
gt; gt;
gt; gt; 3)Were you hoping to run a VBA program to change the number into a date?
gt; gt; (you didn't mention VBA in your original post) How many numbers do you need
gt; gt; converted to dates? For just one or two, running a program may be less
gt; gt; intuitive than using a formula....or just re-entering the value as a true
gt; gt; date.
gt; gt;
gt; gt; 4)Are there any other requirements that will impact the approach you would
gt; gt; use?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP
gt; gt;
gt; gt;
gt; gt; quot;bodhisatvaofboogiequot; wrote:
gt; gt;
gt; gt; gt; hmm...I'm confused a little. How are you wanting me to use that formula?
gt; gt; gt; I'm trying to plug it in with a variety of methods with no success. How
gt; gt; gt; would it look wihtin VBE when editing the code of a macro?
gt; gt; gt;
gt; gt; gt; Or are you suggesting using it as a conditional format? IF so, I couldn't
gt; gt; gt; get that to work either. break it down simple for me. I am still new to the
gt; gt; gt; whole macro code world THANKS!!!
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Try something like this:
gt; gt; gt; gt;
gt; gt; gt; gt; For a number in A1 (eg 406 or 1205)
gt; gt; gt; gt;
gt; gt; gt; gt; This formula uses Excel's default year calculation
gt; gt; gt; gt; B1: =DATEVALUE(INT(A1/100)amp;quot;/1/quot;amp;MOD(A1,100))
gt; gt; gt; gt; Format B1 as a date
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;bodhisatvaofboogiequot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Is there an easy way to change numbers within a cell into dates with a
gt; gt; gt; gt; gt; format, or formula within a macro? For example:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; The Number in the cells are as follows:
gt; gt; gt; gt; gt; 406 which stands for April 2006
gt; gt; gt; gt; gt; 1205 Which stands for December 2005
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I want to change those numbers that are there into a nicer looking date
gt; gt; gt; gt; gt; format, like Apr-06 or something. THANKS!!!