0

What formula can I use to transform the string into a date value that will appear as 01/01/2018?

Jo Jo
  • 73
  • 1
  • 9

4 Answers4

4

If you are trying to do this from a formula into another cell, and not in situ (For which, use "Text to Columns" as per Jeeped's answer), you can either combine DATE and MID, or use REPLACE and convert with +0 or DATEVALUE:

=DATE(MID(A1,1,4), MID(A1,5,2), MID(A1,7,2))

OR

=REPLACE(REPLACE(A1,7,0,"-"),5,0,"-")+0

OR

=DATEVALUE(REPLACE(REPLACE(A1,7,0,"-"),5,0,"-"))

(Where A1 is the date to convert)

The first formula just cuts the number up into 2018 01 01 and uses those as Year, Month and Day. The second 2 work by first Inserting (i.e. REPLACE 0 characters) a hyphen at position 7 ("201801-01") and then at position 5 ("2018-01-01") and converting the string back to a number/date.

Chronocidal
  • 6,827
  • 1
  • 12
  • 26
  • You are a star! the first example solved my issue :) – Jo Jo Mar 07 '18 at 09:50
  • You can use T2C either in-place or output to an alternate column. All you have to do is specify the Destination: (located on the same screen as I captured in my response below). –  Mar 07 '18 at 10:00
2

Use Data, TextToColumns, Fixed width, Date: YMD, Finish. Possibly Date: YDM depending on your string date format (you provided an ambiguous example).

enter image description here

  • I have done this and all it does it divide my string into 3 different columns under this format: Column1: 2018 ; Column 2: 1 / Column 3: 1. It didn't transform it into a date value. – Jo Jo Mar 07 '18 at 09:43
  • You need to remove the extra fields on the second dialog screen. I didn't mention this because mine didn't show ten when I tested. Just double-click the vertical lines splitting the date or drag them away. –  Mar 07 '18 at 09:50
  • ... or even use Delimited instead and turn off all delimiters. –  Mar 07 '18 at 09:53
1

use the code below

=RIGHT((text),2)&"/"&MID((text),5,2)&"/"&LEFT((text),4)
Moti Korets
  • 3,738
  • 2
  • 26
  • 35
R_N
  • 11
  • 3
0

Use the DATEVALUE() function which takes as input a text format. Not that if your output cell is not in Date format you will see a simple int insteade of the expected 1/1/2016.

I refer you to the DATEVALUE function documentation for more insight on the input format and so on

Pclaverie
  • 164
  • 2
  • 17
  • 1
    This gave me `#VALUE` – CallumDA Mar 07 '18 at 09:46
  • This is because your original cell (the one of the text to input) is not in the 'text' format – Pclaverie Mar 07 '18 at 09:47
  • I tried using datevalue before but for =Datevalue("20180101") I get #Value! which kinda makes sense. – Jo Jo Mar 07 '18 at 09:47
  • I have changed my column from general to Text - I get the same issue – Jo Jo Mar 07 '18 at 09:48
  • I tried formatting as *text* and also using `'20180101` (with apostrophe to force the string) and neither worked – CallumDA Mar 07 '18 at 09:48
  • Indeed the input should be =Datevalue("2018/01/01") you can get that from manipulating your original string and appending some "/" For example you can do Datevalue(REPLACE(REPLACE("20180101",5,0,"/"),8,0,"/")) – Pclaverie Mar 07 '18 at 09:49