Noob: don't know how to write IF statements for =mid formula (simple code)

Hi all,

Sample Book.xlsx
please refer to this sample workbook!


I am currently using a report that gives me the field ex: “12/28/2014 - 1/10/2015 (Final Invoice)”. I am currently using logic in excel with the =mid formula to break up this line into two parts, start and end date. With that line broken up into two strings, excel doesnt recognize it as a date yet so I use =datevalue after which it works. I use logic in excel to break up the period into working days per month (worksheet G:R), so I can prorate things.

The Problem:
As of row 1979 the string becomes “1/11/2015 - 1/24/2015 (Final Invoice)” use contains fewer characters then the previous and my mid formula translate the End Date to “1/24/2015 (“ because of that bracket at the end the =datevalue function won’t work. I know I can go in and change the mid formula to start at character 12 instead of 13, or have length of 10 characters instead of 11 to fix this. But I cannot do on every report it’s too time consuming….

I was thinking since =mid formula doesn't work, to use VBA code with IF statements if that line starts with a 1-9 (single digits) then get my mid formula to look at 12 characters instead of 13, and if its 10-12 (double digits) then look at 13 characters in my mid formula.....Does anyone know code to get this done. I would really appreciate it. any things help, i'm open to taking a different route if you see another way to do this.

Thank you John! this works too, turns out I don't need to use VBA anymore becasue of you guys ....does anyone know how to mark thread as SOLVED

This works and is great. Thank you Ali!

You're welcome. Glad to help out. Note my edit to my post.