Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date Format Squirrelly
This must be simple, but it's driving me crazy. I have a date in one
spreadsheet -- 07/21/05 for example. When I copy the cell and paste it into a second spreadsheet it shows as 07/20/01. Hmmm... Ok, I played around with pasting it various ways (e.g. value only, format only, etc.) and by manually telling the second sheet to format it this way and that, but I still get two different dates. So then I go in and tell both spreadsheets to format the date as a number. They both display the same number 37092. So then I go back and tell both spreadsheets to reformat the cell as mm/dd/yy and once again they display the two different dates. Hmm... So then I type the date 7/21/05 into the second sheet and it displays properly. When I ask it to format as a number though it shows 38554 *not* 37092. So then I got type the same date into the first spreadsheet. It displays properly and when I ask to see it formatted as a number it shows 37092. What's going on? How can these two spreadsheets, open in Excel at the same time side-by-side be interpreting the same number as two different dates? I'm confused ... *really* confused. Bill |
#2
|
|||
|
|||
Bill, You say 'a second spreadsheet', but if you mean 'a second Workbook' then please, in Tools, Options, Calculation, check which has the 1904 date system ticked. Bill Martin -- (Remove NOSPAM from address) Wrote: This must be simple, but it's driving me crazy. I have a date in one spreadsheet -- 07/21/05 for example. When I copy the cell and paste it into a second spreadsheet it shows as 07/20/01. Hmmm... Ok, I played around with pasting it various ways (e.g. value only, format only, etc.) and by manually telling the second sheet to format it this way and that, but I still get two different dates. So then I go in and tell both spreadsheets to format the date as a number. They both display the same number 37092. So then I go back and tell both spreadsheets to reformat the cell as mm/dd/yy and once again they display the two different dates. Hmm... So then I type the date 7/21/05 into the second sheet and it displays properly. When I ask it to format as a number though it shows 38554 *not* 37092. So then I got type the same date into the first spreadsheet. It displays properly and when I ask to see it formatted as a number it shows 37092. What's going on? How can these two spreadsheets, open in Excel at the same time side-by-side be interpreting the same number as two different dates? I'm confused ... *really* confused. Bill -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=396955 |
#3
|
|||
|
|||
You mean as was suggested yesterday, and as the OP posted back that it
worked for him? -- HTH RP (remove nothere from the email address if mailing direct) "Bryan Hessey" wrote in message news:Bryan.Hessey.1u07mc_1124442306.9571@excelforu m-nospam.com... Bill, You say 'a second spreadsheet', but if you mean 'a second Workbook' then please, in Tools, Options, Calculation, check which has the 1904 date system ticked. Bill Martin -- (Remove NOSPAM from address) Wrote: This must be simple, but it's driving me crazy. I have a date in one spreadsheet -- 07/21/05 for example. When I copy the cell and paste it into a second spreadsheet it shows as 07/20/01. Hmmm... Ok, I played around with pasting it various ways (e.g. value only, format only, etc.) and by manually telling the second sheet to format it this way and that, but I still get two different dates. So then I go in and tell both spreadsheets to format the date as a number. They both display the same number 37092. So then I go back and tell both spreadsheets to reformat the cell as mm/dd/yy and once again they display the two different dates. Hmm... So then I type the date 7/21/05 into the second sheet and it displays properly. When I ask it to format as a number though it shows 38554 *not* 37092. So then I got type the same date into the first spreadsheet. It displays properly and when I ask to see it formatted as a number it shows 37092. What's going on? How can these two spreadsheets, open in Excel at the same time side-by-side be interpreting the same number as two different dates? I'm confused ... *really* confused. Bill -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=396955 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help - Date Format | Excel Worksheet Functions | |||
Compare dates (one cell not in date format) | Excel Discussion (Misc queries) | |||
How do I keep the date from changing format in a mail merge? | Excel Discussion (Misc queries) | |||
USING THE DATE FORMAT IN EXCEL | Excel Discussion (Misc queries) | |||
Date format collapses diagram | Excel Discussion (Misc queries) |