MS Excel Formatting Hi, I get a file now that has two columns of dates. When the

Question

MS Excel Formatting

Hi,

I get a file now that has two columns of dates. When the people who enter the data do not know the day on which an action occurred, they will enter the date as 4001979 (April 1979) or 10001979 (October 1979) where the two zeros are used for the date field. I would like to fix it so that those two zeros change to 01 but I don't want to have to do a Find/Replace for each month.

Is there some formatting change I can make using wild cards?

For example, I would like to replace all the single-digit month dates by having Excel look for data in the column that fits this pattern - #00#### - and change the 00 to 01 while leaving the first number and the last 4 as is. Same with the two-digit months - ##00####.

Would any of you have a way of doing this?

Thanks.

Explanation / Answer

I'm not certain it can be done with special formatting, but it could be done with a formula in another column like this, assuming the date entries are real numbers and for this example that the entry is in column E (at row 2)

=IF(MOD(E2,10^6)<10^4,E2+10^4,E2)

MOD(E2,10^6) will return just the year for entries with 00 for the day, which is less than 10,000, so for those we add 10^4 (10000) to them to put a 1 in the day portion.

After applying the formula to all the rows, you could then select all of those cells and COPY them and then select the original cells and use Edit --> Paste Special with the Values option selected to replace the original 4001979 with the 4011979 type entries. After doing that, you can delete the formulas used to get the new results.

Change the Const values to point to the proper column & first row in the code and it should be good to go:

Sub AddDayWhereNeeded()
'sheet with date entries must be active when you run this macro
Const firstDatesColumn = "D" ' change as needed
Const firstDateRow = 2 ' change as needed
'for the second column
Const secondDatesColumn = "E" ' change as needed
Const secondDateRow = 2 ' change as needed

Dim lastRow As Long
Dim datesRange As Range
Dim anyDateEntry As Range
Dim calcState As Integer

lastRow = Range(firstDatesColumn & Rows.Count).End(xlUp).Row
'for performance improvement we will turn off
' ScreenUpdating
' Event Processing
' recalculation
With Application
    calcState = .Calculation
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
End With
'just in case so things get restored
'if something goes wrong
On Error GoTo RecoverAndExit

If lastRow < firstDateRow Then
    GoTo CheckSecondColumn 'no date entries to work with in this column
End If
Set datesRange = Range(firstDatesColumn & firstDateRow & ":" _
   & firstDatesColumn & lastRow)
For Each anyDateEntry In datesRange
    If anyDateEntry Mod 1000000 < 10000 Then
      anyDateEntry = anyDateEntry + 10000
    End If
Next
CheckSecondColumn:
'now work with the second column
lastRow = Range(secondDatesColumn & Rows.Count).End(xlUp).Row
If lastRow < secondDateRow Then
    GoTo RecoverAndExit 'no date entries to work with
End If
Set datesRange = Range(secondDatesColumn & secondDateRow & ":" _
   & secondDatesColumn & lastRow)
For Each anyDateEntry In datesRange
    If anyDateEntry Mod 1000000 < 10000 Then
      anyDateEntry = anyDateEntry + 10000
    End If
Next
RecoverAndExit:
If Err <> 0 Then
    Err.Clear ' just ignore it for now
End If
On Error GoTo 0 ' reset error trapping
'restore what we must that we disabled before
With Application
    .Calculation = calcState
    .EnableEvents = True
End With
End Sub

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.