convert date to number (excel 2000)

Topic

New Reply

how to convert date to number or number to date in such format below using same function or vba
2-23-02 to 20223
3-24-02 to 20324
or
20223 to 2-23-02
20324 to 2-24-02
any one please help me on this
than you!!!
20324 to 3-24-02

Reply | Quote

Replies

If your date is in A1 then =VALUE(TEXT(A1,”mmddyy”)), would return a number comprised of the date elements. Leave out the VALUE fumction and the date is returned as string,

The revers is a little trickier, as the length of teh number can be 5 or 6. For a value in A1, try

=IF(LEN(A1)=5,DATE(RIGHT(A1,2),LEFT(A1,1),MID(A1,2,2)),DATE(RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2)))

Andrew C

Reply | Quote

Joe8888, some of what you are asking is unpredictable (and dangerous) because of the leading single digit year (is it 1992 or 2002?), and is also uncertain because I need to tell if “2-23-02 ” is really text or a true date, in which latter case the underlying number is treated by Excel as 02-23-2002. (Which reveals my US-centric perspective of date separation slashes, i.e. mm/dd/yy)

Assuming that the leading year will always be one or two digits, and the second format is a real date, here are conversion macros (with some unnecessary bells and whistles, since I converted them from another one of my own):

Option Explicit

Sub XDatetoN()
‘2-23-02 to 20223
Dim rngCell As Range
Dim strCVal As String
Dim intValLen As Integer
If vbYes = MsgBox(“Are you certain your want to convert these dates” & vbLf _
& ” from mm-dd-yy to yymmdd numbers?”, vbYesNo, “XL Date to Number”) Then
Application.ScreenUpdating = False
For Each rngCell In Selection
strCVal = rngCell.Value
intValLen = Len(strCVal)
If intValLen > 10 And strCVal “0” Then
Beep
MsgBox “Invalid date!”, vbExclamation, “XL Date to Number”
Application.ScreenUpdating = True
rngCell.Select
Exit Sub
End If
If strCVal “0” Then
rngCell.Value = Right(strCVal, 2) & _
Left(strCVal, 2) & _
Mid(strCVal, 4, 2)
End If
Next rngCell
Selection.NumberFormat = “General”
End If
Application.ScreenUpdating = True
End Sub

Sub NDatetoX()
‘20223 to 2-23-02
Dim rngCell As Range
Dim strCVal As String
Dim intValLen As Integer
If vbYes = MsgBox(“Are you certain your want to convert these numbers” & vbLf _
& ” from yymmdd to mm-dd-yy date?”, vbYesNo, “Number to XL Date”) Then
Application.ScreenUpdating = False
For Each rngCell In Selection
strCVal = rngCell.Value
intValLen = Len(strCVal)
If intValLen > 6 And strCVal “0” Then
Beep
MsgBox “Invalid date!”, vbExclamation, “Number to XL Date”
Application.ScreenUpdating = True
rngCell.Select
Exit Sub
End If
If strCVal “0” Then
rngCell.Value = Mid(strCVal, IIf(intValLen = 5, 2, 3), 2) & _
“-” & Right(strCVal, 2) & “-” & _
Left(strCVal, intValLen – 4)
End If
Next rngCell
Selection.NumberFormat = “mm-dd-yy”
End If
Application.ScreenUpdating = True
End Sub

Because of the risks I noted, please test this against a more extensive selection of your own data and see if it bombs on any it shouldn’t.

Usage is to highlight the desired range and run the macro. By design it allows and skips iver any cell whose value is simply zero (“0”). If it finds a value that won’t convert, it stops, beeps and selects the miscreant cell. To restart you’ll have to skip that cell and select the remaining range.

You could also do this with formulas, but I get monster reports from my data people with date in yyyymmdd format, and macros are a faster more efficient way to convert.

Reply | Quote

=VALUE(TEXT(A1,”mmddyy”)) is the one I needed. thank you all for the help.

Reply | Quote