Format Date

[Leesha]

Hi,
I have an excel file that I need to create a macro for that changes the format of an excel file that is exported from the users software. The software puts in the day number but not the entire date. I'm attaching a sample. I tried formatting it based on the Month and Year that are included in the report but couldn't get it to work.
Thanks,
Leesha

[Rudi]

You could tweak something like this:

Code: Select all

Sub CreateDate()
Dim sY As String, sM As String, sD As String
Dim lEnd As Long
Dim rData As Range, rC As Range
    sY = Range("E2").Value  'Get year
    sM = Range("D2").Value  'Get month
    lEnd = Cells(1,Columns.Count).End(xlToLeft).Column
    Set rData = Range("F1", Cells(1, lEnd)) 'Calculate range for dates
    Application.ScreenUpdating = False
    For Each rC In rData.Cells
        sD = rC.Value   'Get day
        rC.Value = CDate(sY & " " & sM & " " & sD)  'Create date
    Next rC
    Application.ScreenUpdating = True
End Sub

[Leesha]

Hi Hans,
I got a type mismatch. I'm uploading the actual spreadsheet so you can see if I did this correctly.
Thanks,
Leesha

[Rudi]

Hi Leesha,

Don't blame Hans for my bad coding

I see in your actual file you removed column A where the descriptions went.
I had to modify the cell references that picked up the Month and Year
Also, the type mismatch occurred because your day numbers go explicitly to 31 where June only has 30 days.

I compensated for this in the macro code below...

Code: Select all

Sub DateChange()
Dim sY As String, sM As String, sD As String
Dim iLD As Integer, i As Integer
Dim lEnd As Long
Dim rData As Range
    sY = Range("D2").Value  'Get year
    sM = Range("C2").Value  'Get month
    iLD = Day(Application.EoMonth(CDate(sY & " " & sM), 0)) 'Last day of month
    lEnd = Cells(1, Columns.Count).End(xlToLeft).Column
    Set rData = Range("E1", Cells(1, lEnd - 1)) 'Calculate range for dates
    Application.ScreenUpdating = False
    For i = 1 To iLD
        sD = rData.Cells(i).Value   'Get day
        rData.Cells(i).Value = CDate(sY & " " & sM & " " & sD)  'Create date
    Next i
    Application.ScreenUpdating = True
End Sub

[Leesha]

LOL! I'm so used to Hans answering that I didn't notice in the email that it wasn't from him! The code you sent worked perfectly!!

Thanks!
Leesha

[Rudi]

I think we all have that problem
Excellent.

[Leesha]

Another question. Form months that don't have 31 days, what would the code look like to delete out the columns that don't have a date? I know the user will ask for it so I'm trying to get it set before she asks!!

Thanks,
Leesha

[Rudi]

Hi,

This should work...

Code: Select all

Sub DateChange()
Dim sY As String, sM As String, sD As String
Dim iLD As Integer, i As Integer
Dim lEnd As Long
Dim rData As Range
    sY = Range("D2").Value  'Get year
    sM = Range("C2").Value  'Get month
    iLD = Day(Application.EoMonth(CDate(sY & " " & sM), 0)) 'Last day of month
    lEnd = Cells(1, Columns.Count).End(xlToLeft).Column
    Set rData = Range("E1", Cells(1, lEnd - 1)) 'Calculate range for dates
    Application.ScreenUpdating = False
    For i = 1 To iLD
        sD = rData.Cells(i).Value   'Get day
        rData.Cells(i).Value = CDate(sY & " " & sM & " " & sD)  'Create date
    Next i
    If iLD < 31 Then
        Range(Cells(1, iLD + 5), Cells(1, lEnd - 1)).EntireColumn.Delete
    End If
    Application.ScreenUpdating = True
End Sub

Caveat: Please note that the code is very dependent on the positions of the Month, Year and 1-31 numbers. If your spreadsheet changes structure the code will misfire again. If you think it is necessary, we can make it a bit more flexible by prompting the user to select the range to convert to dates. Up to you...

[Leesha]

I just now had time to get back to this! Where does time go. This worked perfectly!
Thanks!