Excel Date Format of MMM/YY

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel

When I enter a date in the format of MMM/YY it saves ok but if you then look in tools and calculator I have found that it creates a formula of 2004 and when you copy this over to a Windows based PC it displays incorrect dates. How do I turn off this apparent formula creation. I live in UK and have settings to UK
0
Dave67
4/21/2010 2:24:35 PM
mac.office.excel 1146 articles. 0 followers. Follow

2 Replies
2842 Views

Similar Articles

[PageSpeed] 11

Don't let the info in the Calculator mislead you. It's simply confirming
that the workbook you're in is employing the 1904 Date System which is the
default in Mac Excel. Dates in Mac Excel [as well as most other Mac data
management apps] actually are serial values offset from January 1, 1904.

The the default in the Windows OS, however, is the 1900 Date System which
offsets from January 1, 1900. The date system used in a workbook file is
specific to that workbook, though, so regardless of what version of Excel
you're using when you *open* a file created by any version of Excel the
dates & calculations involving dates will be correct.

What you've run into, however, is because of *copying* from a file based on
one date system & pasting into a file based on the other date system. What
actually gets copied is the serial value of the date. When that value gets
pasted into a book using the other date system the dates rendered will be 4
years & 1 day off � in what direction determined by the values copied.

Volumes describing the issue as well as how to deal with the conflict
readily can be found if you search the Web or the Microsoft site for 'Date
System'... Or just start here:

http://support.microsoft.com/kb/214330/en-us?spid=2513&sid=global

HTH |:>) 
Bob Jones 
[MVP] Office:Mac




On 4/21/10 10:24 AM, in article 59bb73ae.-1@webcrossing.JaKIaxP2ac0,
"Dave67@officeformac.com" <Dave67@officeformac.com> wrote:

> Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
> When I enter a date in the format of MMM/YY it saves ok but if you then look
> in tools and calculator I have found that it creates a formula of 2004 and
> when you copy this over to a Windows based PC it displays incorrect dates. How
> do I turn off this apparent formula creation. I live in UK and have settings
> to UK

0
CyberTaz
4/22/2010 8:29:17 PM
> Don't let the info in the Calculator mislead you. It's simply confirming <br>
> that the workbook you're in is employing the 1904 Date System which is the <br>
> default in Mac Excel. Dates in Mac Excel [as well as most other Mac data <br>
> management apps] actually are serial values offset from January 1, 1904. <br>
>  <br>
> The the default in the Windows OS, however, is the 1900 Date System which <br>
> offsets from January 1, 1900. The date system used in a workbook file is <br>
> specific to that workbook, though, so regardless of what version of Excel <br>
> you're using when you *open* a file created by any version of Excel the <br>
> dates &amp; calculations involving dates will be correct. <br>
>  <br>
> What you've run into, however, is because of *copying* from a file based on <br>
> one date system &amp; pasting into a file based on the other date system. What <br>
> actually gets copied is the serial value of the date. When that value gets <br>
> pasted into a book using the other date system the dates rendered will be 4 <br>
> years &amp; 1 day off &amp;#65533; in what direction determined by the values copied. <br>
>  <br>
> Volumes describing the issue as well as how to deal with the conflict <br>
> readily can be found if you search the Web or the Microsoft site for 'Date <br>
> System'... Or just start here: <br>
>  <br>
> <a href="http://support.microsoft.com/kb/214330/en-us?spid=2513&sid=global">http://support.microsoft.com/kb/214330/en-us?spid=2513&amp;sid=global</a> <br>
>  <br>
> HTH |:>)  <br>
> Bob Jones  <br>
> [MVP] Office:Mac <br>
>  <br>
>  <br>
>  <br>
>  <br>
> On 4/21/10 10:24 AM, in article 59bb73ae.-1@webcrossing.JaKIaxP2ac0, <br>
> &quot;Dave67@officeformac.com&quot;  wrote: <br>
>  <br>
> > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel <br>
> > When I enter a date in the format of MMM/YY it saves ok but if you then look <br>
> > in tools and calculator I have found that it creates a formula of 2004 and <br>
> > when you copy this over to a Windows based PC it displays incorrect dates. How <br>
> > do I turn off this apparent formula creation. I live in UK and have settings <br>
> > to UK <br>
> Excellent thank you for such a superb solution. <br>
>
0
Dave67
4/22/2010 9:08:32 PM
Reply:

Similar Artilces:

Address format
Is it possible to save email addresses in the format? "Firstname Surname" securemail@domain.com This is required for a gateway certificate server. Microsoft <peter@4dt.co.za> wrote: > Is it possible to save email addresses in the format? > > "Firstname Surname" securemail@domain.com > > This is required for a gateway certificate server. Save them where? -- Brian Tillman [MVP-Outlook] ...

how do i import into outlook from excel
have a file which has the names, address etc on each row. For example row 1, name, row 2 address, row 3 city and state, row 4, phone. Can this somehow be imported into outlook contacts? It is a large file and would take quite a lot of work to drag each record from its row, into a column format. But i do want to get it into the OUtlook contact list, for sales calls. Thanks Have you tried File-->Import from Outlook's menus? ******************* ~Anne Troy www.OfficeArticles.com "RF" <RF@discussions.microsoft.com> wrote in message news:CAC9A4FB-C21F-4512-93EF-3...

Excel 2010 to be able to embed like Google Apps?
I am using Google Apps, but with Office making a move to web apps I want to start using it when its released. With Google apps, I am able to use your spreadsheet app, then publish it as an public HTML file. When I want to update that HMTL file, I open the related spreadsheet and update it. I click save and the public HTML file is updated within a few min. Will Excel 2010 do that? I assume something like that will be ready on SharePoint 2010, but how about Skydrive? ...

How do you format bullets in Publisher?
How do you format bullets in Publisher? Do what I just did.. open Publisher, click on Help.. enter 'Bullets' as your search, then look under the heading 'Change bullets or numbers in a list' when it appears alongside all of the other headings.. -- Mike Hall MVP - Windows Shell/User "dmc0409" <dmc0409@discussions.microsoft.com> wrote in message news:F49DB659-3CBF-4700-80A1-0B50F9553024@microsoft.com... > How do you format bullets in Publisher? Format, bullets & numbering. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps....

Auto format sheet
I have a spreadsheet that I continously have to format. It is a data dump from another application. It could be any number of rows and any number of columns. I need a macro that will look at column A and format B based on the number in Column A. So if Column A is 2 then indent Col B 5 spaces, if Col A is 3 then indent Col B 10 spaces, if Col A is 4 then indent Col B 15 spaces. Also if Col B is Bold then shade row Gray. Thanks in advance for your help. clv The following code should work: Code: -------------------- Sub reformat() Dim i As Long For ...

Excel 2000 crashes Win98 when I save. All reloads failed.
I'm running Office 2000 and Windows 98. Excel crashes badly when I save. Two Ctrl-Alt-Deletes get me as far as "exit excel" but that doesn't work. I always have to reboot. I have reloaded Windows 98 twice. I have removed Office 2000 completely and then reloaded it, again twice. I don't have antivirus software on this machine. Other machines with antivirus aren't detecting any virus activity on my zip disk files. I at least have a router and I don't use MS Outlook, which cuts down on viral attacks. Yahoo mail screens incoming email files. Is there s...

Exporting to Excel files
I am currently working on an Access97 database application, and when I export a table to an Excel spreadsheet, I find that my dates turn out as other than I would expect or want. For instance, "00/01/01" will sometimes be displayed "00/01/01", sometimes as "01-Jan-00" or something similar. What I need is a way to force ALL dates to be exported to spreadsheet format in the format "YYYY/MM/DD". Is there a way to do this, efficiently? Or perhaps I will have to recreate my table with the date fields as text and put the dates in there as I expect them? (a r...

Excel Shortcuts
Hi, Is there any place I could go to find all the short cuts on Excel. Thanks! You will find them in the HELP menus if you search for SHORTCUTS. Either that or you will see the short cut for individual actions in the drop down menus. -- Brod "Peterite" <benfernando@bellsouth.net> wrote in message news:OGlO8zd0EHA.2676@TK2MSFTNGP12.phx.gbl... > Hi, > > Is there any place I could go to find all the short cuts on Excel. > > Thanks! > > Hi http://www.mvps.org/dmcritchie/excel/shortx2k.htm -- Regards Frank Kabel Frankfurt, Germany "Peterit...

how do I format cells to change date and time to just date
I want to format a column that contains date and time and I want it to show just the date and not the time. Going into format and clicking on the date and changing doesnt work. Probably the cells aren't really dates, but text. You can check with the ISTEXT() function. If they are text, formatting doesn't have any effect. -- Kind regards, Niek Otten Microsoft MVP - Excel "bondam" <bondam@discussions.microsoft.com> wrote in message news:F4D61EC1-30E2-4723-89E1-F47B545818EE@microsoft.com... >I want to format a column that contains date and time and I want it t...

Export PDF information into Microsoft Excel
What do you do with your data that's stuck in your PDF forms? FDF Decoder from Spotted Dingo Inc., gives you the ability to quickly turn your useless PDF information into a data mine of valuable information. FDF Decoder is a desktop application which creates csv files from your pdf interactive forms (acroforms) in individual or batch file mode. You then can easily import your information into Microsoft Excel to create intelligent spread sheets which can then be entered into databases and aid you in your statistical analysis. FDF Decoder works with the PDF's exported Form Data (FDF...

plz help in to creat formula of excel
hello to all i want to discuss my problem with you related to MS excel. i want to know or find out formula of excel which can help me in such way.... i want to make grade point of my college students in such a way.. for example.... marks grade point average 50 1 53 1.3 60 2 68 2.8 70 3 76 3.6 80 4 84 4 89 4 90 4 98 4 100 4 this is example the student who get 50 marks the GPA will be 1 and who get 80 marks or above 80 the GPA should be 4 GPA should not less than 1 or grator than 4 please tell me the formula so that i can make this GPA thanks a lo -- khushe --------------------------...

How do I work in excel and then save it as a doc or docx?
I need to be able to work in excell and then save it in doc or docx format. If I cannot do this, can I make doc or docx so that I can use the automatic sum? In Word, you can use AutoSum of the Tables and Borders toolbar to sum the values in table. But when you change the values, you need to right-click the total value to refresh it. It can not update auto. Lee Li Fong http://smartusemicrosoftoffice.blogspot.com/ Hi Nadine, You can open a Word document and Insert -> Object -> Microsoft Excel Worksheet Ed Ferrero www.edferrero.com ...

Counting Cells with Conditional Formatting
Is there a way to look at a row of data that is either highlighted yellow or red and to count only the data that is highlighted yellow and give me that number? None of my data is numerical....just one letter data, i.e.: "S" or "U" I just want to count how many of the cells are highlighted yellow in a specific row, if that makes more sense. Here's a UDF (Put in a Standard module) and use it in cell A1, like =CountByColor(A8:H8,6) << where 6 (at present) = Yellow = change to =CountByColor(A8:H8,3) to get the reds HTH Function CountByColor(InRa...

Saving a scrollable image in bitmap and jpeg format
I have a picture control in which i am diaplaying images of various sizes if size go beyond the size of the dialog then scrollbars becomes active so view the hidden image. But when wriiten two functions to save that bitmap following problems occured: 1> For the first function it was saving only viewable area of teh bitmap 2> for second if i have specified the dimensions as 800 X 600 and picture control diamensions is 345 X 330 then it is taking area outside dialog to get image of the 800 X 600 How can i save the image with same dimensions with which i am displaying it ? Thanking in ant...

Can I embed or link multiple .pdf files into or to an excel file?
I would like to link .pdf files to an excel file. Within my excel file I would like to have a column that has file names in it. Then have excel link those files to the excel file so when I print the excel file all the linked files print along with it. use hyper link "GrubbyG" wrote: > I would like to link .pdf files to an excel file. Within my excel file I > would like to have a column that has file names in it. Then have excel link > those files to the excel file so when I print the excel file all the linked > files print along with it. ...

Saving a file as the "date" it was created
I have a load of spreadsheets that I need to file in the same folder. The problem is each one has the same filename. Does anyone know how to save a file as the date that it was created on thus saving me having to go through 100's of files and do it manually. Thanks Dave Woodgate Activeworkbook.SaveAs Filename:=Format(Now,"yyyymmdd hhmmss")&" "&Activeworkbook.Name -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Dave" <dave.woodgate@gmail.com> wrote in message news:1147856944.197931.244350@i40g2000cwc.googlegroups....

How do i secure my excel with a password?
I need to secure my excel with a password. How do i do that? Password for the worksheet: Tools|Protection|protect sheet Password to protect the windows structure/windows: Tools|protection|protect workbook Password to protect it from being opened: File|saveas|tools|General options|Password to Open (Password to modify will allow the user to open it, but not save their changes over your original file) Password to protect the project: Inside the VBE, Tools|VBAProject Properties|Protection tab But security isn't much for any of these. There is software that y...

Excel 2000 and collate option
Can someone tell me how to make collate off by default in all my documents? Every document I do has that box checked and I need that unchecked without having to do it manually each time. thanks Anyone? >-----Original Message----- >Can someone tell me how to make collate off by default in >all my documents? Every document I do has that box >checked and I need that unchecked without having to do it >manually each time. > >thanks >. > ...

insert row at each change of surname in excel
I have a list of 650 entries using 40 surnames - sometimes only appearing once, others 7 times. At every change in surname, I would need to insert a row. Thanks -- De something like this sub insertrows() for i=cells(rows.count,"a").end(xlup).row to 2 step -1 if cells(i-1,"a")<>cells(i,"a") then rows(i).insert next i end sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "De" <De@discussions.microsoft.com> wrote in message news:9A3842A9-B2E7-4BCA-97B5-2D05917A2454@microsoft.com... >I have a list of 6...

due dates #5
thanks alot guys I have it working now this will save me a ton of scanning over dates with a visual que I have 8 pages with 6 rows of due dates on each pag -- canma ----------------------------------------------------------------------- canman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1459 View this thread: http://www.excelforum.com/showthread.php?threadid=26223 Glad to hear that, canman ! Thanks for the feedback -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "canman" <canman.1czh20@excelforum-n...

problems switching speadsheets in excel 2000 using windows xp
I have a pc running XP Pro and Excel 2000 (office). The user has two or three speadsheet open at once. They are having problem using the alt tab to switch between sheets. If the use the windows they see them open and can click to change but would like to use alt tab ...

What If or conditional formatting?
I am trying to create a spread sheet for company shirt orders. I don't know if this is possible, but I hope that it is. I have two cells. In the first cell I have a list of available shirts that a person can choose from. What I am looking to do is, when an individual selects a shirt, I want the adjacent cell to generate a list of available shirt colors based on the "Shirt" selection. Is this possible? Take a look at this: http://www.contextures.com/xlDataVal02.html -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes...

Check if data has already been formatted.
Hello, I have a sub() as part of a larger set of macros. This particular sub() will take the raw data pulled from an in-house reporting system and format it to suit our needs (adding a couple of columns with formulas to facilitate pivot table creation). This is the code as I have it so far: Sub FormatData() Application.ScreenUpdating = False ActiveWindow.DisplayGridlines = False ' Declarations Dim DataWks As Worksheet Dim LastRow As Long Dim LastCol As Long ' Set the DataWks variable Set DataWks = Worksheets("Data") ...

Add Actual End Date to Resolved Cases view
Hi We would like to add the actual end date to the Case General Tab or possibly to the Resolved Cases and/or My Resolved Cases view. I know that the Actual End Date is available on the Service Activity and Case Resolution but this field is not available for the Case. Any workaround for this? Thanks Mark ...

excel worksheet #4
can you set all math funtions on a worksheet to round up without doing each one individually? Hi you may try 'Tools - Options - Calculate' And check 'Precision as displayed'. Now formating to two decimals for example will also make the value to round to only two decimals. Note: After applying this no way to restore the old values -- Regards Frank Kabel Frankfurt, Germany "ollieboy" <ollieboy@discussions.microsoft.com> schrieb im Newsbeitrag news:2A65297E-6F74-4BF5-9AAF-295B6E26E00E@microsoft.com... > can you set all math funtions on a worksheet to rou...