0

when I donwload a data file it has date in this format(for example 20140701 which is actually 07/01/2014)

how can I make a macro do the conversion or is there a formula which takes this value and converts it into the corresponding date. actually I want to automate this and put it in the macro. I have tried all solutions of almost similar cases on stackoverflow. but they don't seem to work.

user3850376
  • 23
  • 4

1 Answers1

3

This would be one way if the value was in A1... and assuming all dates in source are 8 characters in length in YYYYMMDD format.

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

enter image description here

Date is a function accepting year, month day input and converting it to a valid date. Since we assume YYYYMMDD format, we can parse out the appropriate values using left, mid and right functions.

This formula could be used as part of a macro to insert a column fill in formula, fill down, then remove original column with YYYYMMDD format. (assuming you copy and paste as VALUES (since formula will not work after original values removed)

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • What if some dates are in this format: 2014071 (instead of 20140701) or even more natural one (2014/07/01)? – EarlyCoder Dec 18 '17 at 17:59
  • @EarlyCoder I'd use an if and len to determine the length and apply the left, mid right as needed. However if we had a date of 2014111 I wouldn't know if that meant jan 11 or nov 1st... which is why 4x2x2 is more reasonable. for a date format. – xQbert Feb 21 '18 at 21:22