Solved i want to change a date to the 1st of every month

January 29, 2013 at 10:21:17
Specs: Windows XP
I would like to know how to change dates to be the 1st of every month. for example changing 10/20/12 to 10/1/12.

See More: i want to change a date to the 1st of every month

Report •

January 29, 2013 at 11:38:34
✔ Best Answer

This can be done easily by calculating the date in a new cell. Let's say you have your dates in column A. You can calculate the dates you need in column B with this;


I suspect however that you want to make the change in the cell where the date appears. Normally this is not a good idea because inevitably someone will ask you what the "real" date is supposed to be and that will be lost because you overwrote it.

But if you must then you would need to use a macro like this (again assumes dates are in column A

Sub chdate()

lRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lRow
   If IsDate(Cells(i, 1)) Then
         Cells(i, 1) = Year(Cells(i, 1)) & "/" & Month(Cells(i, 1)) & "/" & 1
   End If

End Sub

If you need to go the Macro way let me know the layout of your spreadsheet e.g.
which column your dates are in, what row they will start in, will there be any blank rows etc., and then we can adapt the macro to your requirements.

Report •

January 30, 2013 at 11:25:47
Worked perfectly! Thank you!

Report •

January 30, 2013 at 12:00:25
Which ... one .... ?

Report •

Related Solutions

January 30, 2013 at 12:15:13

Report •

January 30, 2013 at 12:41:01
Of course. Silly question.

Report •

January 30, 2013 at 13:30:31

I only needed to change the date so that I could then use a Vlookup formula.

Report •

Ask Question