Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formatting from text
I have a rather large spreadsheet where someone has input the date as text
eg. 12th January 2004, and 2nd April 2001. Is there a simple way that I can get this into a numerical format ie 12/01/2004 so can easily use it to calculate time periods along with other date I have? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formatting from text
One way:
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"st",""),"nd", ""),"th","") Format as a date. In-place: Do three Edit/Replaces (i.e., replace st, nd and th with nothing). In article , morlo wrote: I have a rather large spreadsheet where someone has input the date as text eg. 12th January 2004, and 2nd April 2001. Is there a simple way that I can get this into a numerical format ie 12/01/2004 so can easily use it to calculate time periods along with other date I have? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formatting from text
There may be simpler ways, but one option (to get rid of the "th" and "nd",
and thus convert to something Excel will read as a date) is =--(LEFT(A1,FIND(" ",A1)-3)&RIGHT(A1,LEN(A1)-FIND(" ",A1))) -- David Biddulph "morlo" wrote in message ... I have a rather large spreadsheet where someone has input the date as text eg. 12th January 2004, and 2nd April 2001. Is there a simple way that I can get this into a numerical format ie 12/01/2004 so can easily use it to calculate time periods along with other date I have? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formatting from text
works a treat - thanks!
"JE McGimpsey" wrote: One way: =--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"st",""),"nd", ""),"th","") Format as a date. In-place: Do three Edit/Replaces (i.e., replace st, nd and th with nothing). In article , morlo wrote: I have a rather large spreadsheet where someone has input the date as text eg. 12th January 2004, and 2nd April 2001. Is there a simple way that I can get this into a numerical format ie 12/01/2004 so can easily use it to calculate time periods along with other date I have? Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formatting from text
Forgot to add "rd", but you probably caught it...
In article , morlo wrote: works a treat - thanks! "JE McGimpsey" wrote: One way: =--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"st",""),"nd", ""),"th","") |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formatting from text
On Mon, 22 Oct 2007 06:51:03 -0700, morlo
wrote: I have a rather large spreadsheet where someone has input the date as text eg. 12th January 2004, and 2nd April 2001. Is there a simple way that I can get this into a numerical format ie 12/01/2004 so can easily use it to calculate time periods along with other date I have? Thanks! You can use this UDF. If the target is already a proper date, it won't change it, but if it isn't it will make the change. You will have to format the cell with the function as a date, or else it will appear as a serial number (e.g. 12 jan 2004 = 37998) If the contents cannot be converted into a date, you will get a VALUE error. You can enter the formula as: =reDate(cell_ref) To enter the UDF, <alt-F11 opens the VBEditor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens: ========================================== Option Explicit Function reDate(str) As Date Dim re As Object Dim ResultString As String Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "(\d+)[A-Za-z]{2}" ResultString = re.Replace(str, "$1") reDate = DateValue(ResultString) End Function =============================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting for cell date to equal today's date | Excel Worksheet Functions | |||
how do i convert text to date (mm/yy text to mm/dd/yyyy date)? | Excel Discussion (Misc queries) | |||
Formula Text String: Formatting Text and Numbers? | Excel Discussion (Misc queries) | |||
Text & Date Formatting | Excel Worksheet Functions | |||
Formatting text and date together | Excel Discussion (Misc queries) |