# The Excel text date to numerical date task **URL:** \ **Category:** Factual Questions\ **Created:** [May 23, 2022, 11:37pm UTC](https://boards.straightdope.com/t/the-excel-text-date-to-numerical-date-task/964965 "2022-05-23T23:37:40Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [May 23, 2022, 11:37pm UTC](https://boards.straightdope.com/t/the-excel-text-date-to-numerical-date-task/964965/1 "2022-05-23T23:37:40Z")
Calling the Excel gurus. You have been given a table of data in Excel, extracted from a text source, thousands of rows. One column is a date. It’s displayed as say dd/mm/yyy. It could be '16/10/2016 or '6/06/2016 or '6/6/2016 The value is right aligned in the column and on checking you can confirm it’s text. bugger You need to get the values into numerical dates, for reporting purposes whatever display format you need. Now if the column of text dates all have the leading zeros ie the values are all 10 characters then the task is easier but in any case I break the d, m, and y text components up, convert them to numbers and them combine them back together using date(y,m,d) So if all the strings are in the format '0d/0m/yyyyy then the formula: =DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2))) … does the job. If some of the date strings are missing the leading zeros on the day or month components it gets rather messier to build the formula which works on all the strings: If the four permutations '0d/0m/yyyy or 'd/0m/yyyy or '0d/m/yyyy or 'd/m/yyyy are possible: =DATE(VALUE(RIGHT(A5,4)),VALUE(MID(A5,FIND("/",A5)+1,FIND("/",A5,3)-(FIND("/",A5)+1))),VALUE(LEFT(A5,FIND("/",A5)-1))) works … well sort of. There is probably alternative, equally clunky constructions. But there must be a more elegant, less bulky, less prone to error, faster way(s) to do it than the above? Using delimiters and text to columns maybe? Thank you for any opinions. PT ---
**Author:** ![Dr.Strangelove](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dr.strangelove/32/6613_2.png) [@Dr.Strangelove](https://boards.straightdope.com/u/Dr.Strangelove)\ **Post date:** [May 24, 2022, 12:05am UTC](https://boards.straightdope.com/t/the-excel-text-date-to-numerical-date-task/964965/2 "2022-05-24T00:05:56Z")
Can’t you just depend on format autodetection? Copy the column to a text file to strip out all formatting. Then paste the column back into your document and it should auto-convert date-looking things into date-formatted cells. The DMY ordering will depend on your system settings, but aside from that it will handle optional leading zeroes or 2-digit years. ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [May 24, 2022, 12:31am UTC](https://boards.straightdope.com/t/the-excel-text-date-to-numerical-date-task/964965/3 "2022-05-24T00:31:36Z")
Hadn’t considered that! I spend a fair bit of time fighting Excel format autodetection eg guys whose surname is “May” get alphabetically to the top of a list because Excel autodetects the surname as a date etc. Using it to advantage sounds a winner. ---
**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\ **Post date:** [May 24, 2022, 10:11am UTC](https://boards.straightdope.com/t/the-excel-text-date-to-numerical-date-task/964965/4 "2022-05-24T10:11:11Z")
Usually, whether the text string has leading zeros or not, =datevalue() will return the correct serial number of any date broken up by /. You then just format that however you like. ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [May 24, 2022, 10:47pm UTC](https://boards.straightdope.com/t/the-excel-text-date-to-numerical-date-task/964965/5 "2022-05-24T22:47:41Z")
@don_t_ask You da man! 🙃 Excellent! Thank you. ---
**Author:** ![Northern\_Piper](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/northern_piper/32/5304_2.png) [@Northern\_Piper](https://boards.straightdope.com/u/Northern_Piper)\ **Post date:** [May 25, 2022, 12:18am UTC](https://boards.straightdope.com/t/the-excel-text-date-to-numerical-date-task/964965/6 "2022-05-25T00:18:30Z")
> [@penultima\_thule](#): > > You da man! 🙃 I see what you did there. 😊 ---
**Author:** ![Arakhin](https://avatars.discourse-cdn.com/v4/letter/a/e99b99/32.png) [@Arakhin](https://boards.straightdope.com/u/Arakhin)\ **Post date:** [May 25, 2022, 3:06am UTC](https://boards.straightdope.com/t/the-excel-text-date-to-numerical-date-task/964965/7 "2022-05-25T03:06:33Z")
Another alternative which I regularly use is adding zero. If, for example, you have the text string “5/24/22” in cell A1 and type the formula =A1+0 in cell B1, you’ll get 44705 in cell B1 which is the date value for 5/24/22 (and you can then format that as a date). The advantage with this method is that as long as the text string is in what would otherwise be a valid number format it’ll convert it, so it works for values other than dates too. So if A1 contained the text string “7702.6” then adding zero would return the number 7702.6. ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [May 25, 2022, 3:16am UTC](https://boards.straightdope.com/t/the-excel-text-date-to-numerical-date-task/964965/8 "2022-05-25T03:16:12Z")
@Arakhin That is, well … frankly devious! 🙃 Thank you! Two elegant solutions and one effective one. Bewdy bonzer