I'd like to make sure that the date in a column is always the first of the month..
eg: 5/09/08 => 5/1/08
7/14/09 => 7/1/08
Convert Date To 1st Day Of Month
-
-
-
Re: Convert Date To Same Date But First Day Of Month
Try
=DATE(YEAR(A1),MONTH(A1),1)
VBA Noob
ADDED BY ADMIN
Select the column and go Edit>Replace
US date format
Replace /*/
With /1/NON US date format
Replace */
With 1/ -
Re: Convert Date To Same Date But First Day Of Month
Thanks, this works.
Now, how might I devise a vba macro to run through an entire column and make this necessary change? I tried doing a normal function but it can't reference itself..
-
Re: Convert Date To Same Date But First Day Of Month
Maybe
CodeSub ConvertDate() Dim i As Long, LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To LastRow If IsDate(Cells(i, "A")) Then Cells(i, "A").Value = DateSerial(Year(Cells(i, "A")), Month(Cells(i, "A")), 1) End If Next i End SubVBA Noob
-
Re: Convert Date To Same Date But First Day Of Month
Brilliant. Works like a charm.
:party:
-
-
Re: Convert Date To Same Date But First Day Of Month
shippy01, please lower your assumed experience. No need for VBA as Copy will do the same.
-
Re: Convert Date To 1st Day Of Month
How would a copy iterate a column such as this?
I lowered my experience.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!