strange date format problem

Hi,

I downloaded some data on excel using Bloomberg. One column has dates
and the other has numbers. Dates appear as 01/01/1980 format which I
want in Jan-80 format. I have changed the format cell properties but it
still appears as 01/01/1980. I have to goto individual cell and press
F2 <enter> so see the effect. Dont know why? I cant even copy paste
format after that ....

TIA/amag

0
amitntu (29)
6/3/2006 6:07:46 AM
excel 39879 articles. 2 followers. Follow

2 Replies
842 Views

Similar Articles

[PageSpeed] 37

I am not sure, but is your workbook set to Auto-Recalculate?

Try pushing <F9> to do a manual re-calculation, see if that changes the
formats.  If it does, go to Tools (on the menu bar), Options,
Calculation, check the box next to Automatic.

Are the dates stored as text?


-- 
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=12822
View this thread: http://www.excelforum.com/showthread.php?threadid=548063

0
6/3/2006 7:26:46 AM
It sounds like those date values aren't really dates.  If you select the range
of troublesome "dates" and do:

Edit|replace
what:  /  (slash)
with:  /
replace all

Excel will notice that they're really dates.



amag wrote:
> 
> Hi,
> 
> I downloaded some data on excel using Bloomberg. One column has dates
> and the other has numbers. Dates appear as 01/01/1980 format which I
> want in Jan-80 format. I have changed the format cell properties but it
> still appears as 01/01/1980. I have to goto individual cell and press
> F2 <enter> so see the effect. Dont know why? I cant even copy paste
> format after that ....
> 
> TIA/amag

-- 

Dave Peterson
0
petersod (12004)
6/3/2006 12:04:03 PM
Reply:

Similar Artilces:

Strange Output
Hello, everybody !!! Why this fragment of code shows : ac cout<<"a\??/??/bc"; Thanks All !!! "Alex Dmitriev" <firejump@mail.ru> wrote in message news:ecTgPp8pEHA.3800@TK2MSFTNGP14.phx.gbl... > Hello, everybody !!! > Why this fragment of code shows : ac > > cout<<"a\??/??/bc"; http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vccelng4/html/elconTrigraphs.asp '??/' is a '\' so you end up with "a\\\bc" http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vccelng4/html/elc...

WLM copy and paste problem
When I copy a Word Document and paste it into a new email in WL; I get error message saying Current security settings prohibit running Active X controls on this page, or you have blocked a publisher of one of the controls". I get rid of the message and paste the document into the email and the words run off the right side of the page with no slider adjustment to read them. I have reset all default settings on I.E. which shows (no-add ons) but everything is enabled and also looked at security settings in WLM. I am really stumped.....any suggestions? Linda H I'm sure...

Event date problem with Entourage calendar
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop When trying to add an event in the calendar, the date defaults to January 5, 2010, and won't let you change it to the current date, or the date of the day that the event is being created on. Any suggestions? Same thing happened to me when I first used Entourage calendar. I fixed it by checking the date/time set up under Apple preferences. Sure enough, the date was set wrong. Hope this helps. ...

Capturing Date
I have tried writing a macro that would let me capture the date from my computer and place it in cell D2. The captured day will be moved intact at the end of the day Is there a command that will let me do that? Thanks for the help in advance. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200609/1 Hi, Why do you need a macro for this if Excel has a formula like this? In Cell D2, put: =TODAY() "Larry Fish via OfficeKB.com" wrote: > I have tried writing a macro that would let me capture the date from my > computer and place it in...

Date formatting in footer
How do I change the format of the date option in the footer of a worksheet? For example, currently it shows 8/9/06; how would I change it to Aug 8, 2006 or 9 Aug 2006 for example?. TIA You can make some changes in Windows Regional Settings to your short date format which is what Excel uses for the footer date. Gord Dibben MS Excel MVP On Thu, 10 Aug 2006 16:50:18 -0700, "computerguy" <computerguy394-news@yahoo.com> wrote: >How do I change the format of the date option in the footer of a worksheet? >For example, currently it shows 8/9/06; how would I change it to ...

Automatically changes cell to Arabic date!?!
I've come accross a problem with Excell 2002 runing SP2, if you type the following in a cell a3-5530 the cell will automatically be reformated in what looks like an Arabic date. Has anyone else seen this or know of any way to stop this happining?? Hi Dan, I get the same behaviour and cannot see any option setting to avoid it. So you are left with: a) typing an apostrophe before the value is entered. b) setting the format of the cell(s) to Type before entering the value Bernard "Dan" <demeade@hotmail.com> wrote in message news:079f01c388cc$87cbc450$a001280a@phx.gbl......

Problems with sharing a workbook
We've got a weekly accuracy check spreadsheet that's on another drive and accessible to me and another co-worker. Normally, we work on it at different times of the day, but this week we decided to make it accessible to both of us for simultaneous editing. The data was first filtered to hide duplicates, then sorted by Employee ID and Date. I then went to Tools>Share Workbook, checked the box for sharing, and saved the workbook to our "G" drive. Then, the trial run: we both accessed it, I had him color-fill the last row and save. I then saved it on my computer. ...

how do i format telephone numbers
I have a column of telephone numbers which I want formatted to the following ##### ### ###. I have formatted a blank column and tried to copy and paste the column of telephone numbers using paste special>Values, however this does not seem to work. Does anyone have any suggestions Thanks Your telephone numbers are probably not real numbers, but text. Custom formats only work on true numbers. You should convert them to numbers *before* you copy them to your pre-formatted column. Right click in a new, unused, empty cell, and choose "Copy". Select the column of telephone nu...

How do I set up formula to copy format from one worksheet to anoth
How do I set up a formula to copy both value and format from one worksheet to another? When I use the = sign, it copy the value only. How do I get the format be copied also? you can't "link" the formatting in the same way as you link the contents what you need to do is to copy all the cells in your sheet X A B C ... 1 2 click on the grey square which I have illustrated with the X this selects all the cells in the sheet press control-C (copy) go to your new sheet and select the same X square now do edit > paste special > format / column width. if you are doin...

Excel Object showing strange behavior
Hello, I have a sheet that I created that contains an Excel object (small spreadsheet) that when double clicked, will open up and allow it to be populated. After they populate it, and close it,...the data is automatically reflected in the object on the original sheet. This is shared with everyone and it's working great. BUT.....(always the but) Why have a few people sent the form back to me saying that when they double click the object it opens up a "Format Object" dialogue box rather than the editable sheet that it was supposed to do? They sent it back to me and sure enough...

How do I retrieve Emails stored in .pst format?
I cannot retrieve my Emails archived/stored in My Documents and Settings folder because it is a .pst format. I'm using outlook 2003. Any advise on our I can retrieve appreciated. On Sat, 23 Feb 2008 03:58:00 -0800, Anno <Anno@discussions.microsoft.com> wrote: >I cannot retrieve my Emails archived/stored in My Documents and Settings >folder because it is a .pst format. I'm using outlook 2003. Any advise on our >I can retrieve appreciated. Please repost your question in a newsgroup supporting Outlook. This group is for a different program, Microsoft Access. The webp...

vertical look up problem with date and time
hi, I'm trying to compare intraday stock volume data for 2 stocks. The stocks do no trade every minute. I have made a new table that consists every minute of the day. With the vertical look-up function I try to get the volume quotes in my new table for every minute that a trade has occurred. So, since not every minute the stocks trade I expect to see a zero or (N/B) in my new table. But instead of doing this my new table shows a volume from the trade that has happened 1 or 2 minutes ago. How can I overcome this problem?? (I've attached an example xls with some comments) Thanks, ap...

Exchange Permissions--Strange One
Hi by default, domain admins (and ent. admins) are denied "receive as" and "send as" permissions to the mailbox store. additionally, by default, on the exchange advanced tab under the mailbox rights button, domain admins are DENIED Full Mailbox Access. However, if you look at the security tab for each user object, by default domain admins are allowed "send as" and "receive as" permissions. now i know that the "receive as" right is ignored on the ad user object, but i ran a small test. i logged on as a domain admin, opened up the domain admin...

Number format #6
When copying a number from an email like 260016008390 and pasting into Excel 2003 it gets changed to 2.60016E+11 How can I prevent this from happening apart from using paste special and selecting text. Steve If the cell width is too small... widen the cell to show the whole number. If you're not calculalting with the number, format cells as text, and if the number starts with a zero, it will keep it... Beege "Steveqae" <steve.shipping@blueyonder.co.uk> wrote in message news:e4R07AltGHA.2224@TK2MSFTNGP06.phx.gbl... > When copying a number from an emai...

Dates in Formula
I want to create an IF statement that says if a cell value is equal to a set date then "True" otherwise "False" The formula used is: =IF(A12="01/04/2004", "TRUE", "FALSE") The value in A12 is 01/04/2004 (created by formatting cell to dd/mm/yyyy) This returns "False" regardless of what I put in A12 I am using Excel 2000 on an XP Pro O/S. Any help greatly appreciated. Thanks On Fri, 14 Jan 2005 03:09:03 -0800, John G <JohnG@discussions.microsoft.com> wrote: >I want to create an IF statement that says if a cell value is equ...

What's the detailed format of pMask Buffer of GPE::SetPointerShape method?
Hi All: I'm developing display driver with windows CE 6.0 R2 version. I'm confusing with the buffer format when debugging GPE::SetPointerShape method. As the document said: The pMask parameter points to a 1-bpp surface with a width of cx and a height of (2cy). The top half defines the AND mask for the pointer, and the lower half defines the XOR mask. but where is the 1st pixel bit located, the 1st bit of the 1st byte or the 7th bit of 1st byte? the document has no related words. And there is another problem, the prototype of this method is as the following: SC...

dates 07-22-07
Hi, Is there a tutorial about how to find certain records between 2 dates? On Jul 22, 12:36 pm, "Khalil Handal" <khhan...@stthom.edu> wrote: > Hi, > Is there a tutorial about how to find certain records between 2 dates? SELECT ... FROM WHERE MyTable.SomeDate BETWEEN Date1 and Date2; hi, I should put the dates manualy in this case. I was wondering if I can put the dates in an unbounded object in a form and see the records below? <pietlinden@hotmail.com> wrote in message news:1185127080.070066.152460@57g2000hsv.googlegroups.com... > On Jul 22, 12:36 pm, &...

Conditional formatting of checkbox?
In Access 2003, is conditional formatting possible on a Y/N checkbox? I have a fairly important checkbox that I would like to highlight in red if checked, to grab the user's attention. If it's not possible, any suggestions for other ways of doing this?? Many thanks CW On Fri, 18 Jan 2008 12:12:05 -0800, CW wrote: > In Access 2003, is conditional formatting possible on a Y/N checkbox? > I have a fairly important checkbox that I would like to highlight in red if > checked, to grab the user's attention. > If it's not possible, any suggestions for other ways of doi...

Chart Displaying Dates and Intervals.
Hi, I would like to create a chart that has: X-Axis: Time (Midnight to 23:59) Y-Axis: Days of the month e.g. 1 Apr 2006, 2 Apr 2006 ... 30 Apr 2006. The plotted data should represent the duration of telephone calls as horizontal bars or lines whose left hand edge represents the time a call was initiated and the bar ends when the call was terminated. Note that more than one call can be made in the same day. To complicate matters there should be two series: one for official calls, one for private calls. Can anyone advise me on the chart type I should use and the SQL source? The so...

Dates and Arrays Driving Me Nuts
This is a simple table and I should be able to figure this out, but....Chip and John, where are you!!! I posted a message earlier, but as I have gotten into the formulas more, I have found the post doesn't fully get to the root of my problem. Here's the scenario...I have a several tables that record budgeted sales for our products. Each table has rows of date ranges (ex. 1/1/03 to 3/31/03) and corresponding budgeted and actual sales. The tables continue to show date ranges until the end of the product life cycle (anywhere from 1 to 6 years). Example: Beg Date End Date Unit $...

macro programming problem
I use the regression tool to calculate calibration curves in my laboratory. In earlier versions of excel, I have created macros to update the regression calculation. The older macro would not work in excel 2002 (gives an illegal range error). When I try to recreate the macro in the new excel from scratch (by recording), it fails. When I run the macro, it says it lacks the Y range. I have tried defining the ranges by clicking and dragging with the mouse, and also by typing in the range. Neither worked. I originally sent the regression output to a different sheet, but it didnt like ...

IWebBrowser2 + Get Container Problem
Hello, I need to run a 32bit activex into a 64 bit application,I use IWebBrowser2 as Ole container. I create the IWebBrowser2 and add my activex with navigate2() ,but then I can't get a pointer to the Activex. the function Get_Container always returns NULL, although the activex is correctly embeded inside the webbrowser. Here is my code: IUnknownPtr poUnknown; CRect oRect(0,0,0,0); IWebBrowser2Ptr poWebBrowser; CWnd oWnd; //create an internet ole explorer oWnd.CreateControl( CLSID_WebBrowser, _T(""), WS...

Bulk Date Change on Activities
Am new to CRM 3.0 however I would like to find a way of changing the due date on mulitple activites. I would like to be able to select multiple activites then apply possibly a rule that asks for a new due date for all activities selected. Anyone got any ideas???? ...

Problem with sum across worksheets
Hi, I have several applications where I keep monthly data on tabbed worksheets, labled Jan07, Feb07, Mar07... I also add a worksheet I usually call RangeEnd that acts to stop sums for ranges. It has been my practice to make a year end worksheet that would have the same shape data structure and place in each cell the formula "=sum(Jan07:RangeEnd!A1)" and copy it into all the appropriate cells for which I want summed data. This seems to work just fine so long as the sells I am summing are not themselves sums. For example, if cell C1 holds a value, say 10. The C1 cell in th...

Excel
I have imported a list of birthdates that are formatted as a number suc as: 19730612 which represents June 12, 1973 Is there anyway to have Excel recognize this as a date in the for yyyymmdd and then convert it to a different format such as mm/dd/yyyy? Thanks, Be -- Message posted from http://www.ExcelForum.com Hi Select the range and then use Data / Text to Columns. This gives you the option of telling Excel it is a date and also the order of the day, month and year within it. -- Andy. "benallred99 >" <<benallred99.17wcg6@excelforum-nospam.com> wrote in messa...