Date Format #2

I have copied and pasted a word table into excel which was a pain in
itself.  The date format in word was Thu 21st June 2007.  But the
table would not sort into date order so I had to remove the "Thu"
part.  I then had to remove the paragraph sign as it was throwing
extra cells in excel when I pasted it.  Eventually the date went into
excel but now I cannot format the date.

When I look at the format 21st June 2007 it has defaulted to General
under "format cell".  When I change the format to say custome date it
does not make a difference, the only way it shacges is if I manually
type the date in.  Is there a quicker way?

0
9/28/2007 6:25:51 AM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
710 Views

Similar Articles

[PageSpeed] 39

Say you have a column of text values that should be dates, for example A1:

Thu 21st June 2007

First enter the following UDF:

Function date_it(r As Range) As Date
Dim v As String
v = r.Value
s = Split(v, " ")
n = Len(s(1)) - 2
v = Left(s(1), n) & " " & s(2) & " " & s(3)
date_it = DateValue(v)
End Function

Next format another cell as Date and enter:
=date_it(A1) to display:
6/21/2007

This will be a "real" date.

-- 
Gary''s Student - gsnu2007


"babypink2807@googlemail.com" wrote:

> I have copied and pasted a word table into excel which was a pain in
> itself.  The date format in word was Thu 21st June 2007.  But the
> table would not sort into date order so I had to remove the "Thu"
> part.  I then had to remove the paragraph sign as it was throwing
> extra cells in excel when I pasted it.  Eventually the date went into
> excel but now I cannot format the date.
> 
> When I look at the format 21st June 2007 it has defaulted to General
> under "format cell".  When I change the format to say custome date it
> does not make a difference, the only way it shacges is if I manually
> type the date in.  Is there a quicker way?
> 
> 
0
GarysStudent (1572)
9/28/2007 2:56:03 PM
Reply:

Similar Artilces:

Remove Date Timestamp in Excel?
Hi there, I was wondering if someone can help me out. I am using a csv file and want to calculate if things were done on time or not. Example: In the A column I would have "Date Expected" In the B column I would have "Date Completed" In the C column I have an IF formula such that if B<= A then "Meets" otherwise "Not Met" But I have a problem since I get the data from a csv file. The timestamp can give the impression that something does not meet. Example Date Expected: 09/18/2006 1:00am Date Completed: 09/18/2006 2:00am As per the formula it w...

How thorough is "Remove Office"? #2
On Jan 6, 2008 Diane Ross wrote: > Office uses files that were installed by Apple. If these files are corrupt > or damaged the problems will continue. That's one of the reasons we often > suggest using the combo Apple updaters. [snip] Hello Diane Thank you for this good advice. I do keep quite a good OS in that I always update using combo updaters. -- Patrick - Brighton, UK If you wish email me from my web-site: <http://www.patrickjames.me.uk> ...

2 separate e-mail accounts
is there a way to use outlook with two email accounts and not have them dump into the same inbox? Never mind, I finally figured it out. "endosmom" wrote: > is there a way to use outlook with two email accounts and not have them dump > into the same inbox? ...

CRM 1.2 Dutch
After installing MS CRM 1.2 Dutch, the web-client works correctly, but te Outlook Client misses some icons and when opening an account or prospect, etc, the screen is al messed up: text is disorganised pictures and (menu) incons are missing background is just white Does anybody know what causes the described problem and how to solve it? We have tried re-installing the client software and tested it on 2 different workstations, but unfortunately didn't solve the problem. In other words: HELP!! ...

conditional formatting
hi community how can i use the Conditional Formatting to format 2 criteria at a same time, an example: condition 1 if the cell value is greater than 0, it will format a shade color of gray condition 2 if the cell value is than than 1, it will format a red border line surrounding the cell so, if now my cell contain a value of 2, the cell will have a shade of gray and a border color of red. any help on this ??? thanks community for kind assistance :) -- oldLearner57 The 1st applicable conditional formatting rule is applied. So your second condition will n...

source from 2 tables
Hello, I have 2 tables 1- clients (PK autonumber) 2- ditributors (PK autonumber) I now have a form which track sales (thus a sales table). I need to be able to select from both clients and distributors. Is there any way to union the two. My issue is that both PK are autonumber so duplication is bound to occur.... so how can I get this to work? How should I proceed oh database gurus?! Thank you as always, Daniel P I didn't properly explain myself.... here are more details. The on the sales form, I enter the date of the sale and then have a cbo to associate which client mad...

Calander Problem #2
Hi When I try to invite someone to a meeting I can that he is busy or not, but our problem is that insted of seeing that the person is buy we can see in what he is busy? How can we stop it, so that we be able only to that he is busy? Than'x S ...

no address book #2
I recently had to format my hard drive. I saved my outlook.pst file and after installing outlook 2002 back on my computer i copied the pst file back in its appropriate folder. All my folders and old emails are back where they are suppose to be. I even have my contacts. However when i tried to create and email with Word, it tells me the address list could not be displayed. It says the contact folder associated with the address list could not be opened, it could be moved or deleted. Did i forget to save something as backup before formatting? Anyway i can get my address book back? Thanks for the ...

ascending page numbers for 300 page printout of 1 page? #2
i have one page i have to print in excel but i need to print 300 copies and each page needs an ascending number from the previous page. i.e. the first page should say 1, the second 2, etc. is there any way to do this without making 300 copy and pastes of the same sheet for the numbering? Try this Greg http://www.rondebruin.nl/print.htm#number -- Regards Ron de Bruin http://www.rondebruin.nl "Greg" <gregyuzik@gmail.com> wrote in message news:57669204.0409150932.34dd7c8a@posting.google.com... >i have one page i have to print in excel but i need to print 300 > copies...

custom cell formatting #3
Can anyone tell me why Excel will not let me use the customize option in formatting cells to "CS-###"? I would like to be able to type in "150" and have it automatically come up as "CS-150". I use this function quite frequently and can not understand why Excel will not allow me to use "CS-###" when any other letters work just fine. For example, it will let me do "TS-###", "SC-###", "XY-###", and so on with no problem at all. But for whatever reason it will not let me use "CS-###". Is it just my computer ...

Dates in WORD 2007
Hi I am running Windows XP and have just installed WORD 2007. Can someone please tell me how I stop the parser auto-completing dates for me? I like to write 29th May, 2010 - but as soon as I type that and hit <ENTER> I get 2010-05-29 on the end. It's driving me nuts!! R :( "Mansfield Terrier" <MansfieldTerrier@discussions.microsoft.com> wrote in message news:C4519479-5C8F-4DCB-A398-3284BCE3FA4E@microsoft.com... > Hi > I am running Windows XP and have just installed WORD 2007. Can someone > please tell me how I stop the parser auto-com...

Retrieving the date in a MAX column
This is a multi-part message in MIME format. ------=_NextPart_000_00D4_01C9BDD4.E2234B10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I need help in writing a Function in Excel 2003. The SS has dates running across the top of each column in Row 1, from = Column D to Column IT, in DATE format to show dd/mm/year. In Row 6 of = each column is a numeric value calculated from other cells in the = column. I want to know both the highest value for that Row 6 AND the = date on which it occurred. Doing a MAX across Row 6 gives me the highes...

Format e-mail newsletters to print on 1 page?
Recipients of e-mail try to print, but it gets cut-off on bottom. I send using the JPEG image format, but even w/out it cuts off the bottom. Appears the address lines from the e-mail are pushing it down... Tell me about it - you are going to have to edit your content. I spent over an hour doing that. I called a friend and we went back and forth until we came up with something that worked for the two of us. In my case I had a picture and a coupon that had to stay. It was not fun. I had to do the same thing the other week. Fortunately I had saved that file and used it as a basis for th...

Dlls #2
Hi, I would like to know how I could create several components (DLLs) and call them from a main application. I am trying to learn how I can call dlld from one another. I am trying to create some dialogs contained in one dll and another non-visual dll. The main application should be able to call and show the dialogs in the one dll, obtain some data and then pass this onto the other dll. I am using MFC for all this. Are there any examples or tutorials in any good websites please!! Hi, This link might be useful to u http://www.codeproject.com/cpp/ Girish. "Suudor Harry" <...

Have group members in wrong format
Hey all, I have used arrMemberOf = objGroup.GetEx("member") to obtain the members of a group but they come in as the "cn=...." and I need to just obtain the user name. How can I translate to that? I'm trying to do something like the follwoing after the fact but get an error: For Each strMember In arrMemberOf 'Grab the user details. strSN = strMember.SN strGivenName = strMember.givenName strMail = Split(strMember.mail,"@", -1) strFullMail = strMember.mail strCN = strMember.CN Man, curse of posting is that you f...

Disable automatic conversion of cell format?
Hello, i'm using Excel2002 for editing CSV-files which are exported from database tables. In this files i have per example a lot of cells with dates, which are formated in us style. The problem is excel converts this cells automatically in german style (i have the german version of office). Some other cells, mostly with ints are converted in something else too. Is there a posibillity to tell excel to convert nothing automatically? In the preferences and help files i've nothing found about that. Greetings, Christopher Reitz Hi one way: - save the file as '*.txt' file - use the...

Looking to copy a worksheet with all formats and formulas without coping inputed data
I am looking to copy a spreadsheet from one year to the next with all the same formats and formulas. I do not want to copy the inputed data since the inputed data with be new with the new year. Thank you for the input -- God's Kid ------------------------------------------------------------------------ God's Kid's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28426 View this thread: http://www.excelforum.com/showthread.php?threadid=480194 If it were me, I'd copy the whole sheet and delete the inputted data. ************ Anne Troy www.OfficeArt...

CRM 1.2: Find all shared objects of a user
.... which should be deleted in future. I painfully found out, that as soon as a user is deleted in crm and AD, it is not possible to change shares on any objects where this user has shares afterwards :-( Can I remove all shares of a user in one step? TIA Stefan ...

Access Query w/ Unix Date Criteria
I have a pass through query that pulls information from an oracle datasource. I need to be able to set a date critera for a field in my query. For Example I want to only pull data where RES_DATE is greater than 12/01/2003. The RES_DATE field type is some unix time (example- 1072881348) I have tried just setting normal data critera in the access query but it doesnt like the format. Does anybody know how to set this type of criteria? Any help is appreciated-Corey The Unix Date is the number of seconds since January 1st, 1970. To convert from a regular date to a Unix date, use DateDiff(&q...

Dates Formating
hi Me again new question --- how do you auto format dates in Excel. At the top of my table (see my previous question) I manually write the dates for every Monday in the year but I would like to do this automatically any ideas? Also how can I change the dates every six months without having to start a new table Gazmac1 can't you just put the date for the first Monday in a cell and then =thatcell+7 -- Don Guillett SalesAid Software dguillett1@austin.rr.com "gazmac1" <gazmac1@discussions.microsoft.com> wrote in message news:69C8B309-9891-490C-A9A0-52C7F53CF4BC@microsof...

Error detected in outlook.pst #2
Hi, When I try to open outlook an error message comes up saying 'error detected in the file outlook.pst. Quit all mail enabled applications, and then use the Inbox Repair Tool'. I have used the Inbox Repair Tool but that gets to a certain stage of scanning the file then an error causes itto stop. The .PST file is 1.96GB Any ideas as to how I can open outlook, or get the Inbox Repair tool to finish scanning. Cheers. Scott <anonymous@discussions.microsoft.com> wrote: > When I try to open outlook an error message comes up > saying 'error detected in the file outlook....

Why does an Excel 2007 file lose its format when reopened?
An Excel 2007 file (e.g. File.xlsx) loses its formatting when it is reopened. You may have too many cell formats/styles. To see if that's possibly true, click on the Cell Styles "More" arrow and see if you have a gazillion styles showing. If that's true, you can remove unused styles by saving the workbook as a web page, closing, reopening, save as a normal workbook. "Chrystopher" wrote: > An Excel 2007 file (e.g. File.xlsx) loses its formatting when it is reopened. ...

Urgent #2
I work at Motorola as a Desktop support guy. Please help me with this specific issue.The messaging interface has returned as unknown error.If the problem persitsclose out look and restart. I can't delete or move anything on my personal folder.I really appreciate it. Thank you. probably your PST file is damaged, try SCANPST (c:\program files\common files\system\mapi\1033) to repair your file. If SCANPST does not help you have the following options: - find PSTUPG19 using google and try to repair the file with this app - try to export your file into a new file or at least parts of it o...

window size #2
Need help - The worksheet window has minimized significantly after someoen else opened the file from another computer. I cannot just pull/drag to resize it. Any ideas how I can get the worksheet size to stay the same no matter where it is being used? (fully inside of the excel window)? Thanks much. Your best attempt may be to resize the window and save the workbook. If you can't get to the window's title bar, try: window|arrange|Tiled and resize the way you want. But I think that a lot of the way the window will appear depends on how the activewindow is set up. If my activ...

Exporting Date received and Date Sent to Access
How can export both 'date received' and 'date sent' fields to another program such as Access or Excel? However, if you use Access to set up a linked table connected to your = mail folder, you will see the Received date among the available = properties. (I don't remember whether Sent is also shown.) Third-party = export tools are also available; see = http://www.outlookcode.com/d/customimport.htm#tools --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jump...