Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
change DD/MM/YY to MM/DD/YY
I need to change DD/MM/YY to MM/DD/YY in a description field but not in all
cells. The date is in the middle of description. All American description fields have dates in MM/DD/YY and all European description fields have DD/MM/YY. Please keep in mind that this is NOT a date field and the field contains other words besides the date. The date is in the middle of the description. How do I do that? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
change DD/MM/YY to MM/DD/YY
Vic
Can you provide any logic with which to find the date in the cell entry? Something perhaps like: The date always starts with the 7th character in the cell. Or The first character in the date is always the first numerical character in the cell. Or The date is always 8 characters long and is the only part of the entry that has slashes. Or ??? HTH Otto "Vic" wrote in message ... I need to change DD/MM/YY to MM/DD/YY in a description field but not in all cells. The date is in the middle of description. All American description fields have dates in MM/DD/YY and all European description fields have DD/MM/YY. Please keep in mind that this is NOT a date field and the field contains other words besides the date. The date is in the middle of the description. How do I do that? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
change DD/MM/YY to MM/DD/YY
Hi Otto,
I can select European rows and apply the change to them only. There are 2 possible descriptions in European rows: 1. Quest has it requisition #7847. Sample collected on 6/17/08 2. Not listed on Quest report. Sample collected on 4/23/08 I only need to change Europeans to look like Americans or all Americans to look like European dates. I can't have any mix. The preference will be teh European date format. I would like to change the above dates to look like this: 17/06/08 and 23/04/08 with zero added to a day and a month. Thank you. "Otto Moehrbach" wrote: Vic Can you provide any logic with which to find the date in the cell entry? Something perhaps like: The date always starts with the 7th character in the cell. Or The first character in the date is always the first numerical character in the cell. Or The date is always 8 characters long and is the only part of the entry that has slashes. Or ??? HTH Otto "Vic" wrote in message ... I need to change DD/MM/YY to MM/DD/YY in a description field but not in all cells. The date is in the middle of description. All American description fields have dates in MM/DD/YY and all European description fields have DD/MM/YY. Please keep in mind that this is NOT a date field and the field contains other words besides the date. The date is in the middle of the description. How do I do that? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
change DD/MM/YY to MM/DD/YY
Vic
Your response doesn't address any of the questions I posted. Am I missing something? Otto "Vic" wrote in message ... Hi Otto, I can select European rows and apply the change to them only. There are 2 possible descriptions in European rows: 1. Quest has it requisition #7847. Sample collected on 6/17/08 2. Not listed on Quest report. Sample collected on 4/23/08 I only need to change Europeans to look like Americans or all Americans to look like European dates. I can't have any mix. The preference will be teh European date format. I would like to change the above dates to look like this: 17/06/08 and 23/04/08 with zero added to a day and a month. Thank you. "Otto Moehrbach" wrote: Vic Can you provide any logic with which to find the date in the cell entry? Something perhaps like: The date always starts with the 7th character in the cell. Or The first character in the date is always the first numerical character in the cell. Or The date is always 8 characters long and is the only part of the entry that has slashes. Or ??? HTH Otto "Vic" wrote in message ... I need to change DD/MM/YY to MM/DD/YY in a description field but not in all cells. The date is in the middle of description. All American description fields have dates in MM/DD/YY and all European description fields have DD/MM/YY. Please keep in mind that this is NOT a date field and the field contains other words besides the date. The date is in the middle of the description. How do I do that? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
change DD/MM/YY to MM/DD/YY
Hi Otto,
The date is always at the end of the field, it has 2 slashes and in format MM/DD/YY. Leading zero is always truncated in MM and DD. Thank you. "Otto Moehrbach" wrote: Vic Your response doesn't address any of the questions I posted. Am I missing something? Otto "Vic" wrote in message ... Hi Otto, I can select European rows and apply the change to them only. There are 2 possible descriptions in European rows: 1. Quest has it requisition #7847. Sample collected on 6/17/08 2. Not listed on Quest report. Sample collected on 4/23/08 I only need to change Europeans to look like Americans or all Americans to look like European dates. I can't have any mix. The preference will be teh European date format. I would like to change the above dates to look like this: 17/06/08 and 23/04/08 with zero added to a day and a month. Thank you. "Otto Moehrbach" wrote: Vic Can you provide any logic with which to find the date in the cell entry? Something perhaps like: The date always starts with the 7th character in the cell. <=== No Or The first character in the date is always the first numerical character in the cell. <=== No Or The date is always 8 characters long and is the only part of the entry that has slashes. <=== No to 8 characters and Yes to slashes Or ??? HTH Otto "Vic" wrote in message ... I need to change DD/MM/YY to MM/DD/YY in a description field but not in all cells. The date is in the middle of description. All American description fields have dates in MM/DD/YY and all European description fields have DD/MM/YY. Please keep in mind that this is NOT a date field and the field contains other words besides the date. The date is in the middle of the description. How do I do that? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
change DD/MM/YY to MM/DD/YY
In cells A1 thru D1 enter:
I will meet you on 12/25/2009 =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) =LEN(B1) =LEFT(A1,LEN(A1)-C1) & TEXT(DATEVALUE(B1),"dd/mm/yyy") We see in A1 thru D1: I will meet you on 12/25/2009 12/25/2009 10 I will meet you on 25/12/2009 So the date at the end of the phrase in A1 has been converted to European format in D1. -- Gary''s Student - gsnu200856 "Vic" wrote: Hi Otto, The date is always at the end of the field, it has 2 slashes and in format MM/DD/YY. Leading zero is always truncated in MM and DD. Thank you. "Otto Moehrbach" wrote: Vic Your response doesn't address any of the questions I posted. Am I missing something? Otto "Vic" wrote in message ... Hi Otto, I can select European rows and apply the change to them only. There are 2 possible descriptions in European rows: 1. Quest has it requisition #7847. Sample collected on 6/17/08 2. Not listed on Quest report. Sample collected on 4/23/08 I only need to change Europeans to look like Americans or all Americans to look like European dates. I can't have any mix. The preference will be teh European date format. I would like to change the above dates to look like this: 17/06/08 and 23/04/08 with zero added to a day and a month. Thank you. "Otto Moehrbach" wrote: Vic Can you provide any logic with which to find the date in the cell entry? Something perhaps like: The date always starts with the 7th character in the cell. <=== No Or The first character in the date is always the first numerical character in the cell. <=== No Or The date is always 8 characters long and is the only part of the entry that has slashes. <=== No to 8 characters and Yes to slashes Or ??? HTH Otto "Vic" wrote in message ... I need to change DD/MM/YY to MM/DD/YY in a description field but not in all cells. The date is in the middle of description. All American description fields have dates in MM/DD/YY and all European description fields have DD/MM/YY. Please keep in mind that this is NOT a date field and the field contains other words besides the date. The date is in the middle of the description. How do I do that? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
change DD/MM/YY to MM/DD/YY
On Mon, 8 Jun 2009 11:34:02 -0700, Vic wrote:
Hi Otto, I can select European rows and apply the change to them only. There are 2 possible descriptions in European rows: 1. Quest has it requisition #7847. Sample collected on 6/17/08 2. Not listed on Quest report. Sample collected on 4/23/08 I only need to change Europeans to look like Americans or all Americans to look like European dates. I can't have any mix. The preference will be teh European date format. I would like to change the above dates to look like this: 17/06/08 and 23/04/08 with zero added to a day and a month. Thank you. I'm not sure if you want to make all look like Europeans, or all look like Americans. The following macro will reverse the dd/mm to mm/dd (or vice versa) on the rows you select. It checks to make sure the "last word" contains to "/"'s, but doesn't do any other error checking. So you can use this to switch those positions; the result (all American or all European) will depend on what you select. To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), first select the entries you wish to process. Then <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. When you are satisfied that it works correctly, you can remove the Offset(0,1) argument (see the documentation in the Macro, and your results will replace the original. ====================== Option Explicit Sub RevDayMonth() Dim c As Range Dim s Dim sFirstPart As String Dim sDate As String Dim sTemp As String For Each c In Selection 'assumes you select only entries to be processed With c 'remove leading/trailing spaces for ease of processing sTemp = Trim(.Value) sFirstPart = Left(sTemp, InStrRev(sTemp, " ")) sDate = Right(sTemp, Len(sTemp) - InStrRev(sTemp, " ")) s = Split(sDate, "/") 'make sure that there are two /'s in string as check If UBound(s) = 2 Then sTemp = Right("0" & s(0), 2) s(0) = Right("0" & s(1), 2) s(1) = sTemp sDate = Join(s, "/") End If 'when debugged, remove the .Offset(0,1) .Offset(0, 1).Value = sFirstPart & sDate End With Next c End Sub =================================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change cell contents when pull down menu choices change | Excel Worksheet Functions | |||
how to change formula in shared sheet without loss of change histo | Excel Worksheet Functions | |||
Excel bar chart formatting of bars to change colors as data change | Excel Discussion (Misc queries) | |||
Use date modified to change format & create filter to track change | Excel Worksheet Functions | |||
Change workbook sheet reference using cell A1 to change a vairable | Excel Worksheet Functions |