Look carefully at the picture below for a few seconds…
If you extract all numbers, from left to right, you can create a Date/Time value of Nov 28, 2014 5:08pm (and 51 seconds) in Microsoft Excel.
Here is our task……
Create a formula to add x amount of days, hours and minutes while keeping all of the text in the same order!
If we decide to add 3 days and 2 hours to each cell then we should see this:
How do we do this? Watch my video, download the file or read the steps below.
Watch my YouTube video
Download my Excel file
Get the Excel file here or from my OneDrive here (file 00121)
Follow These Steps
Step 1 EXTRACT DATE & TIME NUMBER POSITIONS
As the amount of text between the numbers can vary, we need to determine exactly where our date and time ‘pieces’ are found. We can do this using Excel’s SEARCH function.
{=MIN(IFERROR(SEARCH(Digits,C$10),””))} The SEARCH function uses a named range called ‘Digits’, which is an array constant ={0,1,2,3,4,5,6,7,8,9}, and gives us the position number of all of the digits found within the cell. We care about the smallest number. The ‘2’ is found is position number 6 in our text string seen here: “dfZSa2“. Note that the second formula looks like this: =MIN(IFERROR(SEARCH(Digits,C$10,C13+4),””)) as we aren’t looking from the beginning of the cell. We are looking for the next number after the first number.
Step 2 EXTRACT TEXT VALUES
This step is a bit easier. We use the LEFT function =LEFT(C$10,C13-1) and then the MID function =MID(C$10,C13+4,C14-C13-4) for the rest of them.
Step 3 BUILD ORIGINAL DATE TIME VALUE
Using the position numbers of the date & time values in Step 1, we can then add =DATE(MID(C$10,C13,4),MID(C$10,C14,2),MID(C$10,C15,2)) and =TIME(MID(C$10,C16,2),MID(C$10,C17,2),MID(C$10,C18,2)) to create a real date / time value.
Step 4 INPUT VARABILES
Now, simply input the number of days, hour and minutes that you want to add. In this case we are adding 3 days and 2 hours.
Step 5 CREATE DATES
Below, we have our new list of dates that increase by 3 days and 2 hours each.
Step 6 FINAL NEW TEXT STRINGS!
FINALLY THE LAST STEP ! Now, we just have to concatenate the text values with the date value components. The formula looks like this:
=C$21&YEAR(C42)&C$22&MONTH(C42)&C$23&DAY(C42)&C$24&HOUR(C42)&C$25&MINUTE(C42)&C$26&SECOND(C42)
Luckily, not all string manipulation requirements are like this but it’s incredible what you can do if you can see the pattern and break it down into a few steps.
Subscribe to my YouTube channel and learn more!
See my free templates here!