Macro to insert dates into cells based on values from another cells

[Jibroni]

Hello,

I need a macro that inserts dates into cells based on values from cells in another column.

Here's what my spreadsheet looks like:

CLASS_NO	STATUS	COURSE	DOMAIN	START_DATE
06052022_GST_402	100​	11202V	Shared
07042022_GMT_401	100​	21201V	Shared
07062022_GMT_330	100​	3267​	Shared

I need to fill in the START_DATE column with start dates in format MM/DD/YYYY. The dates are based on the part of the string of numbers and letters from the CLASS_NO column that appear as a date. For example, in the first row you see "06052022_GST_402" and the first string of numbers are a date, June 5, 2022. The format for all cells in that column is such that the first eight numbers always form a date "MMDDYYYY" and are followed by some string for example "_" three letters "_" some more numbers.

I was able to come up with this macro:

Sub Insert_Saba_Start_Date()

string_date = Split("06052022_GST_402", "_")(0)

string_year = Right(string_date, 4)
string_day = Mid(string_date, 3, 2)
string_month = Left(string_date, 2)

date_date = DateValue(string_month & "/" & string_day & "/" & string_year)

Range("F2").Value = date_date

End Sub

However, this macro does only the specific value mentioned in my example. How do I create a macro that will create insert the date when the date string in CLASS_NO column is different every time?

 

[Fluff]

Hi & welcome to MrExcel.
Any reason you need a macro, you can do it with this formula

=DATE(MID(A2,5,4),LEFT(A2,2),MID(A2,3,2))

 

[Jibroni]

Hi & welcome to MrExcel.
Any reason you need a macro, you can do it with this formula

=DATE(MID(A2,5,4),LEFT(A2,2),MID(A2,3,2))

Hi @Fluff Thanks for your reply. That formula works, but only for the value in A2. If I wanted to run this formula for the rest of the values in column A, how would I do that?

 

[Fluff]

If you happy to have a formula, then just drag it down to the last row of data.

 

[Jibroni]

Hi Again, Here's a macro to run the formula for each row.

Range("F2").Formula = "=DATE(MID(A2,5,4),LEFT(A2,2),MID(A2,3,2))"
Range("F3").Formula = "=DATE(MID(A3,5,4),LEFT(A3,2),MID(A3,3,2))"
Range("F4").Formula = "=DATE(MID(A4,5,4),LEFT(A4,2),MID(A4,3,2))"
Range("F5").Formula = "=DATE(MID(A5,5,4),LEFT(A5,2),MID(A5,3,2))"

It does what I need it to do, but just wanted to see if there's any easier way than copying this formula down for a hundred rows?

 

[Fluff]

Do you want the formula in the cells, or just the result?

 

[Jibroni]

Hi, sorry, I am new to VBA, so I might be misunderstanding you. If I put the formula "=DATE(MID(A2,5,4),LEFT(A2,2),MID(A2,3,2))" into E2, it gives me the value from A2 that I want. Perfect. However, If I put the formula in E3, it gives me the date value from A2, but I want the date value from A3. So I need a way to fill in all of column E with the dates from all of the values in column A.

The macro I shared above will accomplish that, but in a very repetitive way. I would guess there is a simpler way.

 

[Fluff]

I would guess there is a simpler way.

There is, but do you want to keep the formula in col E, or do you want the actual value, which is what the code you originally posted was doing.

 

[Jibroni]

If I can use one formula for every cell in column E, I would think that would be the best way.

 

[Fluff]

Ok, how about

Sub Jibroni()
   Range("E2:E" & Range("A" & Rows.Count).End(xlUp)).Formula = "=DATE(MID(A2,5,4),LEFT(A2,2),MID(A2,3,2))"
End Sub