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
Format Date
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Format Date
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Format Date
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Format Date
Hi Hans,
I got a type mismatch. I'm uploading the actual spreadsheet so you can see if I did this correctly.
Thanks,
Leesha
I got a type mismatch. I'm uploading the actual spreadsheet so you can see if I did this correctly.
Thanks,
Leesha
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Format Date
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...
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
Last edited by Rudi on 11 Aug 2014, 18:12, edited 1 time in total.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Format Date
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
Thanks!
Leesha
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Format Date
I think we all have that problem
Excellent.
Excellent.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Format Date
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
Thanks,
Leesha
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Format Date
Hi,
This should work...
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...
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Format Date
I just now had time to get back to this! Where does time go. This worked perfectly!
Thanks!
Thanks!