I need some assistance in making my life a LOT easier! I key in dates a great deal and would prefer not to mess with "/". Can someone point me to an easy way to modify my spreadsheet where I can set it up and type in dates in a format like "080804" and get Excel to recognize it as a "8/8/04"? It thinks I want to use a serial date and tells me my date is in the year 2121.
Thanks for any assistance!
Re: Automatically convert a number (080804) to a date (8/8/2004)
in this formula, column A should be changed to text format, then in column B put:
=LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2)
enter 080804 into A1, then B2 will become 08/08/04
hope this helps
Re: Automatically convert a number (080804) to a date (8/8/2004)
Post this code in the sheet module of the sheet you have the dates to enter. You can enter your date as MMDDYY and it will place the date serial in the cell you type your date in. You can format the cell to any valid date format.
Option Explicit
Dim varDate As Variant
Dim sDate As String
Dim isect As Range
Private Sub Worksheet_Change(ByVal Target As Range)
'If cell changed is not in date sheet range then exit
Set isect = Application.Intersect(Range("F:F"), Target)
If isect Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
sDate = Format(Target.Value, "000000")
varDate = Left(sDate, 2) & "," & Mid(sDate, 3, 2) & "," & Right(sDate, 2)
On Error GoTo DateError
Target.Value = DateValue(varDate)
Application.EnableEvents = True
Exit Sub
DateError:
'If invalid date or cell not formated for dates then error occurs
MsgBox "Date Input Error"
Target.Value = ""
Application.EnableEvents = True
End SubHope this is what you are looking for
Re: Automatically convert a number (080804) to a date (8/8/2004)
Excellent! Thanks so much for the help. This will be of much value to me and hopefully my teammates in the future.
wise_s_w
Don’t have an account yet? Register yourself now and be a part of our community!