How to change date & time settings of yahoomail?

Different date formats

• QUESTION: How are You? I hope You will have a little time to help me again here to solve this small trouble with dates. I paste dates fromto Excel. I have 3 different pattern like: 2009/03/04 A sentence here. 2009/05 Another sentence here. 2009 Again a sentence. I would like to have these dates be converted to the US format i.e.: 04/03/2009 A sentence here. 05/2009 Another sentence here. 2009 Again a sentence. I tried to solve with: Selection.NumberFormatLocal = "dd/mm/yyyy" but I had some extra days and months where I actually do not need them, and the year just turned to a value like 1905 with some motnsh and day. Is there any way to treat these three different formats to have the result I would like? I would appreciate if You could suggest some solution for this problem. All the best, attis ANSWER: attis, Numberformat formats a number that is in a cell. Dates are stored as numbers that represent the elapsed number of days from a base date. So if you pasted in the date and it was interpreted as a date, then setting the numberformat should work. (I would only thing numberformatlocal would be appropriate or needed if you were using one of the named formats such as Short Data). In any event, using numberformat or numberformatlocal will have no effect unless the data is stored as a number. Pasting in your date may causeto interpret it as a date or may not. You say you paste it in, but I don't know if that is manually or with Excel, but the results is more important than the method. If the date is not interpreted as a date and is stored in the cell as a string, then you will need to parse out the various parts - then stored them as a date and format the cell as you wish it to appear. All dates must be a specific day. You can display it as a year or as a month and year, but it needs to be stored as a single day/date. If you just want to show it as text, then you don't have that restriction. the other problem you could have is that it is interpreted as a date, but it would be interpreted as per your regional settings. So if this could cause the month and day to be reversed, it would be as long as the date was legitimate being reversed (I can't say about your regional settings because the examples you showed are ambiguous in terms of what month and what day). That is about the best I can tell you based on what you have said. Chip Pearson has pages of information at his web site on all aspects ofand dates. Here is a good starting point: http://www.cpearson.com/Excel/datetime.htm but if you have a specific question, post back. ---------- FOLLOW-UP ---------- QUESTION: Thank You for your kind and detailed explanation on the date matter. I had a look at the attached link too but could not find similar pattern I am looking for.... I will try to make myself clear and simple hoping some solution from You. I have adocument where I have following records: 2009/03/04 A sentence here. 2009/05 Another sentence here. 2009 Again a sentence. After pasting toI have the following: 2009/03/04 A sentence here. May-09 Another sentence here. 2009 Again a sentence. The first is the regional setting for date here in Japan I guess. The second turns to a short date after pasting to Excel. The third is a four digit number after pasting. I would like to change them to: first: dd/mm/yyyy second: mm/yyyy third: leave as it is (a four digit number) I tried to create a for loop with if "inlet" but did not work for me.... Dim rng As Range Set rng = Sheets(1).UsedRange For each Cell in rng If pattern1 Then Selection.NumberFormatLocal = "dd/mm/yyyy" If pattern2 Then Selection.NumberFormatLocal = "mm/yyyy" If pattern3 Then leave as it is Next Do You apparently see or have some solution for such problem? If so, I whould be very grateful. for your kind help. All the best, attis

• Answer:

  attis, first, saying you pasted this data intodoesn't tell me whether it is stored as a number or date or is just a text string. You can only number format numbers/dates. The below macro will work on all numbers/dates. If you find it is missing entries, then the code would need to be modified to search for text strings that are made up all of numbers and slashes. You can try this one first. It marks the number cells it alters with a yellow background and numbers it doesn't alter with an orange background. If there are cells that are missed that should be altered or processed, that indicates they are stored as text values. Try using this as a test sub Test() Dim rng as Range, r as Range, cell as Range Dim s as String set rng = sheets(1).UsedRange on Error resume next set r = rng.specialcells(xlconstants,xlNumbers) on Error goto 0 if not r is nothing then r.select for each cell in r s = cell.Text if instr(1,s,"/",vbTextcompare) > 0 or _ instr(1,s,"-",vbTextcompare) > 0 then ' mark the cells that were changed with yellow ' you can delete the marking of the cell if you want cell.Interior.colorIndex = 6 cnt = len(s) - Replace(s,"/","") if cnt = 2 then cell.Numberformat = "dd/mm/yyyy" else cell.Numberformat = "mm/yyyy" end if else ' unchanged numbers marked as orange cell.interior.colorIndex = 44 end if Next end if end sub