Numbers To Dates

[Guest]

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!!!

 

[Dav]

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

format mmm-yy

 

[David Biddulph]

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!!!

=DATE(2000+RIGHT(A1,2),LEFT(A1,LEN(A1)-2),1)
Format as mmm-yy

 

[Guest]

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)&"/1/"&MOD(A1,100))
Format B1 as a date

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

XL2002, WinXP

 

[Harald Staff]

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

HTH. Best wishes Harald

 

[Guest]

=DATE(2000+RIGHT(A1,2),(--LEFT(A1,LEN(A1)-2)),1) formatted with the format of
your choice.

 

[Guest]

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!!!

 

[Guest]

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 "date
number" 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, WinXP

 

[Guest]

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!!!

 

[Guest]

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("X1")
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) & "/1/" & rngCell Mod
100)
rngCell.NumberFormat = "mmm - yy"
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, WinXP

 

[Guest]

Wonderful!!! that worked perfectly. THANKS!!!!

 

[Guest]

one SLIGHT problem. IF there is a 0 in the cell, then it should just remain
blank or 0.....The formula you gave me changed the 0 into a Jan-00. Help me
understand where that is in the formula so that I might make the alteration.
PLEASE!!! THANKS!!!

 

[Guest]

Try changing this line:
If IsNumeric(rngCell) Then

to this:
If IsNumeric(rngCell) And rngCell.Value > 0 Then

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

XL2002, WinXP

 

[Guest]

excellent. Now I get it Thanks!!!

Try changing this line:
If IsNumeric(rngCell) Then

to this:
If IsNumeric(rngCell) And rngCell.Value > 0 Then

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

XL2002, WinXP