Extract Date From String, Variable Date Formats

33 viewsexcelvba
0

I have looked at so many threads doing similar things, but they usually have one or a couple different date formats they’re dealing with. My situation has many different formats and I’m not sure there’s a way for me to write something that is all encompassing to capture all of them. I’ve been making individual scripts for each use case up until now, but it’s becoming cumbersome and I’d like a one size fits all solution, if possible.

I am trying to pull the date from the filename of the current workbook and format it as mm-dd-yyyy. Here is non comprehensive list of some of the formats I’m running into:

  • MMM dd yyyy
  • mm_dd_yyyy
  • m.dd.yy
  • yyyymmdd
  • mm-yyyy
  • MMMM yyyy

Sometimes the date is at the beginning, sometimes the middle, sometimes the end. None of it is consistent, so if anyone could help me find some way to bridge this gap, I would be so grateful!