Based on col dates create col of dates for the first day of that same month and year

**welchs101** · 06-28-2011, 08:13 PM

I have a column of dates say in col B. In col-A i want the following:

ColA ** ColB
1/1/11 ** 1/14/11
1/1/11 ** 1/28/11
2/1/11 ** 2/4/11
1/1/11 ** 1/12/11
3/1/11 ** 3/14/11

Basically, i have a col dates in B.....i want the first day of that month/year in col-A.

I am enclosing a file showing what i want. I can do it in excel but not sure how to "efficiently" do it in vba.

**cgkmal** · 06-28-2011, 08:42 PM

Hi welchs101,

I'm not sure if I undestand, but try this:

In A2 put "=DAY(B2)" --> this will give you the day of the date you have in B2

or

In A2 put "=B2-DAY(B2)+1" --> this will give you the first day of that month you have in B2.

Copy down any of the formulas if works for you.

Hope this helps,

Regards.

**welchs101** · 06-28-2011, 08:45 PM

thanks. i will try that. do you know if this is also available via vba code?

thanks.

**Haseeb Avarakkan** · 06-28-2011, 09:32 PM

Hello,

I am an absolutely newbie in VBA. I don't know if this fix your problem. Anyway try this in a Copy of your workbook


Sub FirstDay()

Dim LastRowR As Long, Length As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

Range("A:A").ClearContents
Range("A1") = "Concatenated" 'Change A1 to the header cell

For Length = 2 To LastRow 'Change 2 to the first starting row
    If IsDate(Cells(Length, "B")) Then
    Cells(Length, "A").Value = Format(Cells(Length, "B") - Day(Cells(Length, "B")) + 1, "m/d/yyyy")
End If
Next Length

Application.ScreenUpdating = True

End Sub

Based on col dates create col of dates for the first day of that same month and year

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Based on col dates create col of dates for the first day of that same month and year

Re: Based on col dates create col of dates for the first day of that same month and y

Re: Based on col dates create col of dates for the first day of that same month and y

Re: Based on col dates create col of dates for the first day of that same month and y

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions