Date problem #6

I have a column containing
4/21/2004
10/7/2003
5/5/2004
Which I need to convert to UK dates.
because of the variables(4/, 10/) I am unable to use mid 
etc in seperate columns.
also =TEXT(f2,"DD/MM/YY")fails to work.
0
8/20/2004 12:19:57 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
534 Views

Similar Articles

[PageSpeed] 22

Hi
are these values 'real' date values. If yes a simple format should do

--
Regards
Frank Kabel
Frankfurt, Germany

"Gerry" <Gerry.Briant@goodrich.com> schrieb im Newsbeitrag
news:9a4601c486b0$01ac1f40$a501280a@phx.gbl...
> I have a column containing
> 4/21/2004
> 10/7/2003
> 5/5/2004
> Which I need to convert to UK dates.
> because of the variables(4/, 10/) I am unable to use mid
> etc in seperate columns.
> also =TEXT(f2,"DD/MM/YY")fails to work.

0
frank.kabel (11126)
8/20/2004 12:30:34 PM
No Frank, they were not just real date values.
I was able to resolve with the help of the great people 
here by 
"I had the orginal date in G4
Then in G5 I put 
=IF(MID(G4,2,1)="/","0"&G4,G4)
this adds a zero to make it mm
then in the column next to this (i.e. G6 for me) put
=IF(MID(H4,5,1)="/",LEFT(H4,3)&"0"&RIGHT(H4,6),H4)
this is based on the new one you just calculated therefore 
this is mm/dd/yyyy"
Then by inserting extra rows with mid functions in 
converted all.
Thanks To All

>-----Original Message-----
>Hi
>are these values 'real' date values. If yes a simple 
format should do
>
>--
>Regards
>Frank Kabel
>Frankfurt, Germany
>
>"Gerry" <Gerry.Briant@goodrich.com> schrieb im Newsbeitrag
>news:9a4601c486b0$01ac1f40$a501280a@phx.gbl...
>> I have a column containing
>> 4/21/2004
>> 10/7/2003
>> 5/5/2004
>> Which I need to convert to UK dates.
>> because of the variables(4/, 10/) I am unable to use mid
>> etc in seperate columns.
>> also =TEXT(f2,"DD/MM/YY")fails to work.
>
>.
>
0
Gerry
8/20/2004 1:30:33 PM
Another option would be to use Data|Text to columns.

Use mdy as the format of the field (column????), but when you're done change the
format to dd/mm/yyyy (or what you like.)

But data|text to columns is a quick way to turn "text" dates into real dates.



Gerry wrote:
> 
> I have a column containing
> 4/21/2004
> 10/7/2003
> 5/5/2004
> Which I need to convert to UK dates.
> because of the variables(4/, 10/) I am unable to use mid
> etc in seperate columns.
> also =TEXT(f2,"DD/MM/YY")fails to work.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/20/2004 8:10:31 PM
Reply:

Similar Artilces:

Stationery #6
I need to turn off the stationery option for everyone in my organization. Is this possible? ...

Oulook Express 6 Connection problem
Recently, I am having serious problems not being able to secure a connection when sending e-mail via Outlook Express6. I am getting a Cannot find server message Number 0x800cccCO131. Many times, Outlook disconnects altogether and I frequently get a "Can't Find Page" message for either Inbox, or Sent folders although in some instances thhe message disappears and the folder appears as I wsitch folders. This is all very disconcerting and affects my business as well as costing many hours of lost downtime. Need help fast (before I lose my mind--or business). Stan Bodner Hi - ...

Averaging a Value between Two Serial Dates
Hi all, I have two spreadsheets. The first spreadsheet contains two columns, one for start date/time (in serial format) and the second for stop date/time (in serial format). The second spreadsheet contains two columns. One containing a serial date/time and the other containing a meteorological value (eg. temperature). I want Excel to read the start and stop date/time from the first spreadhseet and then search the second spreadsheet for all temperature values which were recorded during this time frame. Lastly, I want Excel to average these values. Is this possible using a single Excel func...

Find Oldest date based on two criteria.
I am having an issue trying to figure out this issue.. I have tried a MIN, but it doesnt seem to work correctly.. I have three columns One with a State, one with a Code and one with a date. The Code can have say EA12 EA23 EA45 and FG12 FG34 FG56 What I need to have happen is that when State= NY and CODE Starts with EA find me the oldest date in the column. Example A B C NY EA12 1/1/2009 NY EA24 2/28/2009 CT EA12 3/2/2009 NY FG34 4/5/2009 ME FG45 5/1/2009 NY EA12 ...

trying to export .aba file from Palm Desktop to .csv file and having problems with irregular characters
I'm getting these odd square characters in my notes section of the .csv file which I think are carriage returns in palm desktop that are unrecognizable to excel. Any idea how to eliminate them? Here's a pic of what they look like" http://img176.imageshack.us/my.php?image=howtoeliminatesquareseu6.jpg > I'm getting these odd square characters in my notes section of > the .csv file which I think are carriage returns in palm desktop that > are unrecognizable to excel. Any idea how to eliminate them? > Here's a pic of what they look like" I hate to ask what...

offline sync problem
Yesterday I had a customer's 8 register system lose internet connectivity, so all the registers went off-line. They are using RMS 1.2 SR3 on a SBS Professional 2003 Server with XP SP2 registers. When internet connectivity was restored today, all but one of the registers sync'd. On the register that did not, the last transaction in the batch was at about 7:40pm yesterday. No transactions were recorded after that time. I manually took the register off-line today and could not find any batch or blind-closed batch with any transactions after 7:40pm. I also checked, via MS Acce...

Date and Time Stamp with a keystroke
If I want to use a keystroke in Outlook to insert a date/time stamp (like ctrl+; in excel) how is this set up? Thanks in advance... http://www.outlook-tips.net/howto/date_stamp.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, certolnut asked: | If I want to use a keystroke in Outlook to insert a date/time stamp | (like ctrl+; in excel) how is this set up? | | Thanks in advance... ...

Date Function
I am running a query from a table that limits the output of data to a specific time frame. I am working on the last month minus 12 months. So what I want to see is from April of this year to May of last year. This is progressive as the months change. i have been using: Between (DateAdd("m",-1,Date())) And (DateAdd("m",-12,Date())) This gives me this years May which I do not want. Any help would be appreciated -- Message posted via http://www.accessmonster.com On Wed, 09 May 2007 19:18:04 GMT, "rollover99 via AccessMonster.com" <u14080@uwe> wrote: ...

Problem with Table Column Not Expanding
I've got text in a table column that extends beyond the visible edge of the column. It appears that Word 2007 is not actually extending the table as it should but stops right at the page break. On the Table tab of Table properties, make sure text wrapping is set to None. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "David C. Holley" <David.C.Holley> wrote in message news:%23IRfeJbgKHA.5564@TK2MSFTNGP06.phx.gbl... > I've got text in a table column that extends beyond the visible edge...

dashed line printing problem
Hi, I am having problem in printing charts which has dashed lines. Whenever I make a graph which has dashed line instead of solid line, on the computer screen those lines appears to be dashed but when I print the graph those lines come to be solid. I believe it's bug in excel. I was reading and microsoft suggest installing service pack 3 for the office to get the problem rectified. I installed the service pack but the problem still persists. Can somebody help me if he know the work around with this problem. Prashant ...

Problems with a form in Access 2000
Hello all, We are using Windows 2000 pro with Access 2000. We are having this problem on one machine only. There is an input form that has a memo field in it. When the guy tries to hightlight some text in that memo field, it highlights the whole memo field as soon as he clicks in it instead of letting him choose what data to highlight. Sticky keys are not on, on his machine, I checked that. The database has a front end and a back end and he is working in the front end. To make the problem go away, he has to restart his machine. What could possibly be the problem? Thanks, GMC ...

problem with header rule...
i seems to have a problem with only one rule type.. i have a rule that says to permanently delete any mail which contains certain words in the header.. i also have the same basic one for words in senders and recipients names... but on those rules i have no problems with it just checkin the perm. delete only, but on the header rule it turns on the stop processing more rules checkbox automatically if i have the perm. delete on... i dont want the stop one on.. just the perm delete... for now i just moved this rule to last place so it is the last one to process anything ayways, but i wou...

Microsoft Outlook has encountered a problem and needs to close
After un-installing Norton Internet Security using Add/Remove Programs I get the message "Microsoft Outlook has encountered a problem and needs to close" when trying to run Outlook. I have tried removing all account info, running with /safe etc, reinstalling office xp, reinstalling Windows 2000 but still cannot start outlook. Any ideas anyone??? ...

Problem with Outlook 98 Not Closing
We have some clients who recently began running Outlook 2003 over dialup RAS without any problems, but when they use a LAN or dialup connected machine with Outlook 98, the Outlook client won't fully close unless they manually close it with task manager or logoff or restart the machine. A dialog stating that Outlook is exiting remains on the screen and doesn't disappear. The problem began once they started using Outlook 2003. Interestingly enough, Outlook 97 exits without any problems. Does 2003 make some changes on the Exchange server that cause Outlook 98 to try to run some...

calender problems
By mistake I imported(old Schedule plus import) another user's calender into my outlook (I meant to just look at his calender). Now, of course, "people" (I still have this in testing mode) so no big deal, yet) think they are looking at my calender instead of his. How can I fix this? Also, his admin assistant has to view his calender, however, she has to use another Exchange server and you can't use two Exchange servers. Since I screwed up the import, viewing her boss's calender(in OWA) is impossible. Please advise. "amyb" <amy@npsg.ncifcrf.gov> wr...

Problem when trying to make initially invisible a dialog base app
Hi all.. I programmed a dialog based application that shows an icon in the traybar. Since all functionality is made from a menu shown when user clicks on that icon, I want the dialog to be invisible at startup. I tried it by using SetWindowPos(........., SWP_HIDEWINDOW) and ShowWindow(SW_HIDE) API's in OnInitDialog method but always the dialog is shown. What can I do? Thanks Jaime > > I programmed a dialog based application that shows an icon in the traybar. > Since all functionality is made from a menu shown when user clicks on that > icon, I want the dialog to be inv...

Update Problem 06-28-10
Recently, I downloaded & installed updates by going to the Windows Update site -- just like I regularly do, . The update process went fine -- all updates were installed. NOW, however, the program (Acronis) that I regularly use to backup my hard-drive crashes with a BSOD specifying a kernel error. I guess that I will have to UNinstall these updates to be able to run that program. Is there any way I can find out which update is causing the problem so I only have to UNinstall only one of these updates? I use WinXP Pro SP2. On 2010-06-28 2:52 p.m., MN wrote: > Rece...

Risk and Decision Analysis @Risk, Evolver, Decisiontools, Roy Kelly FloorTrader Tools 8.3, AmiBroker 5.0, MultiCharts 2.1, NinjaTrader 6, OwnData 2.6, VantagePoint 7.0.11.2, other ...
Risk and Decision Analysis @Risk, Evolver, Decisiontools, Roy Kelly FloorTrader Tools 8.3, AmiBroker 5.0, MultiCharts 2.1, NinjaTrader 6, OwnData 2.6, VantagePoint 7.0.11.2, other ... please send e-mail to : ola 'AT' mail 'DOT' gr , ola3 'AT' mailbox 'DOT' gr , ( please substitute 'AT' with '@' , and 'DOT' with '.' ) , ola@mail.gr, ola3@mailbox.gr, ======================================= RISK ANALYSIS with @RISK --- @RISK 4.5.7 for Excel, Compatible with Excel 2007, ( Standard, Porfessional, Industrial ) @RISK 4.5.6 Internat...

Problem with XmlSerializer
When i make a call to XmlSerializer xs = new XmlSerializer(System.Type.GetType("myobject")); I get this error Key cannot be null. Parameter name: key what is the cause of this? <jw56578@gmail.com> schrieb im Newsbeitrag news:1126047360.427054.306130@g47g2000cwa.googlegroups.com... > When i make a call to > > XmlSerializer xs = new XmlSerializer(System.Type.GetType("myobject")); > I get this error > Key cannot be null. Parameter name: key > > what is the cause of this? > Don't use a string...use an object XmlSerializer xs = new XmlS...

Dates
When using date formulas and displaying the Month in letters is there a way to make the month letters be in ALL CAPS? -- Benjamin Hi Benjamin, Not with formatting, but with a formula like =UPPER(TEXT(TODAY(),"ddd mmmm dd yyyy")) -- Kind regards, Niek Otten "Benjamin" <benjamin@discussions.microsoft.com> wrote in message news:063E2229-70C8-4EFA-9E4E-B48F6BD60335@microsoft.com... > When using date formulas and displaying the Month in letters is there a > way > to make the month letters be in ALL CAPS? > -- > Benjamin ...

Problems with CString (Conversion from VC6 to VC2005)
Hi all, I have converted a project from VC6.0 to VC2005 (MFC). Now I have a compiling problem. Following error: error C2079: 'myString' uses undefined class 'CString'. CString should now be a template in place of a class. By another project theres is a forward declaration of 'class CString'. I get the following message: error C2371: 'CString' : redefinition; different basic types Thats an indication for the template, but what do I have to do to compile my project again. Regards, Matthias looks like a header file path issue. It seems that when you compi...

set cell value and workbook Creation date
How can I set the value of a cell with the creation date of the workbook? Each workbook is beaing created from a custom template. TIA W William, Either through a macro or through a User-Defined-Function: Sub TryNow() ActiveCell.Value = ActiveWorkbook.BuiltinDocumentProperties("Creation Date").Value End Sub Use this like =CD() Function CD() As Variant CD = Application.Caller.Parent.Parent.BuiltinDocumentProperties("Creation Date").Value End Function HTH, Bernie MS Excel MVP "William Hamilton" <william@NOthevirtualSPAM.PLEASEco.THANKSnz> wrote in me...

XmlDocument xsi: Problem
I've been looking through MSDN, books and googling for a couple hours but haven't found the solution yet. I'm using CSharp in VS .NET 2003. I'm trying to use XmlDocument to save XML files that are valid to a schema I've developed. I want the root element to look like this: <TestCase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="TestCaseSchema_03.xsd"> I've tried several variations of XmlElement.SetAttribute() and " XmlAttribute att = [XmlDocument].CreateAttribute() " The closest I'...

Address space problems on IMC ( Exchange 5.5 )
We have several IMC's but we want emails from certain outside domains to use a particular IMC, and not all of them, but have this IMC only process emails for this domain ( it sends it all to a smart host ).... We have configured address space on all IMC for this outside domain, have set the cost higher on all IMCs beside the preferred one, but it still seems to only use the * address space. If we set the cost higher for the * address space on that particular IMC, all emails for all domains including one listed in the address space goes trough other IMC's... If we set the * addr...

Outlook 2000 Backup Add-In Problem
I'm having trouble with the Outlook 2000 Backup Add-In.. On clicking File/Backup and exiting Outlook I get "Cannot copy Outlook. Access is denied, the source file may be in use." I've done searches on Google and eliminated every possible process that might possibly have a hold on Outlook.. Namely the Nokia connection manager which syncs a Phone w/Outlook. No luck. I eliminated every process Windows would allow me to kill. No luck. I've rebooted without the Nokia Connection Wizard booted. (X-ed it out in the registry on CurrentVersion/Run) No luck. So Outlook wo...