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

Jibroni

New Member
Joined
Apr 7, 2022
Messages
25
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
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_NOSTATUSCOURSEDOMAINSTART_DATE
06052022_GST_402
100​
11202VShared
07042022_GMT_401
100​
21201VShared
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?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi & welcome to MrExcel.
Any reason you need a macro, you can do it with this formula
Excel Formula:
=DATE(MID(A2,5,4),LEFT(A2,2),MID(A2,3,2))
 
Upvote 0
Hi & welcome to MrExcel.
Any reason you need a macro, you can do it with this formula
Excel 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?
 
Upvote 0
If you happy to have a formula, then just drag it down to the last row of data.
 
Upvote 0
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?
 
Upvote 0
Do you want the formula in the cells, or just the result?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
If I can use one formula for every cell in column E, I would think that would be the best way.
 
Upvote 0
Ok, how about
VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top