Why does the change change to a number?

Hi all,

I've noticed something wierd and always wondered WHY it happens.  When you 
type a date into a cell, and then change the Formatting of it to a 'general' 
cell, it turns into a number.  How does it come up with that number?  What is 
the significance?

i.e. type today's date of "12/7/2007" - change it to a 'General' format, and 
it then says "39423".

I'm a trainer of Excel and this question always comes up.  I'm curious 
myself too.  Thanks!

Joe
0
AccessJoe (17)
12/7/2007 5:48:01 PM
excel 39879 articles. 2 followers. Follow

3 Replies
4046 Views

Similar Articles

[PageSpeed] 28

It's the number of days since January zero 1900 using Excel default for 
windows (Mac uses 1904), they copied that including the leap year bug from 
Lotus when Lotus was the main contender in spreadsheets.


-- 


Regards,


Peo Sjoblom



"Access Joe" <AccessJoe@discussions.microsoft.com> wrote in message 
news:77259A37-1514-4155-BDF9-06194EC6725E@microsoft.com...
> Hi all,
>
> I've noticed something wierd and always wondered WHY it happens.  When you
> type a date into a cell, and then change the Formatting of it to a 
> 'general'
> cell, it turns into a number.  How does it come up with that number?  What 
> is
> the significance?
>
> i.e. type today's date of "12/7/2007" - change it to a 'General' format, 
> and
> it then says "39423".
>
> I'm a trainer of Excel and this question always comes up.  I'm curious
> myself too.  Thanks!
>
> Joe 


0
terre081 (3244)
12/7/2007 5:54:54 PM
I had a feeling it was something like that.  Thanks Peo!

"Peo Sjoblom" wrote:

> It's the number of days since January zero 1900 using Excel default for 
> windows (Mac uses 1904), they copied that including the leap year bug from 
> Lotus when Lotus was the main contender in spreadsheets.
> 
> 
> -- 
> 
> 
> Regards,
> 
> 
> Peo Sjoblom
> 
> 
> 
> "Access Joe" <AccessJoe@discussions.microsoft.com> wrote in message 
> news:77259A37-1514-4155-BDF9-06194EC6725E@microsoft.com...
> > Hi all,
> >
> > I've noticed something wierd and always wondered WHY it happens.  When you
> > type a date into a cell, and then change the Formatting of it to a 
> > 'general'
> > cell, it turns into a number.  How does it come up with that number?  What 
> > is
> > the significance?
> >
> > i.e. type today's date of "12/7/2007" - change it to a 'General' format, 
> > and
> > it then says "39423".
> >
> > I'm a trainer of Excel and this question always comes up.  I'm curious
> > myself too.  Thanks!
> >
> > Joe 
> 
> 
> 
0
AccessJoe (17)
12/7/2007 6:05:00 PM
And that will explain why =A2-A1 will give the number of days between two 
dates
-- 
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Access Joe" <AccessJoe@discussions.microsoft.com> wrote in message 
news:13B84C25-32ED-4564-91EF-C29353E3CEAC@microsoft.com...
>I had a feeling it was something like that.  Thanks Peo!
>
> "Peo Sjoblom" wrote:
>
>> It's the number of days since January zero 1900 using Excel default for
>> windows (Mac uses 1904), they copied that including the leap year bug 
>> from
>> Lotus when Lotus was the main contender in spreadsheets.
>>
>>
>> -- 
>>
>>
>> Regards,
>>
>>
>> Peo Sjoblom
>>
>>
>>
>> "Access Joe" <AccessJoe@discussions.microsoft.com> wrote in message
>> news:77259A37-1514-4155-BDF9-06194EC6725E@microsoft.com...
>> > Hi all,
>> >
>> > I've noticed something wierd and always wondered WHY it happens.  When 
>> > you
>> > type a date into a cell, and then change the Formatting of it to a
>> > 'general'
>> > cell, it turns into a number.  How does it come up with that number? 
>> > What
>> > is
>> > the significance?
>> >
>> > i.e. type today's date of "12/7/2007" - change it to a 'General' 
>> > format,
>> > and
>> > it then says "39423".
>> >
>> > I'm a trainer of Excel and this question always comes up.  I'm curious
>> > myself too.  Thanks!
>> >
>> > Joe
>>
>>
>> 


0
bliengme5824 (3040)
12/7/2007 8:20:18 PM
Reply:

Similar Artilces:

Change Private Sub to module code??
Hello. I have a code that grays out text boxes in my form. I use it in several forms and use it in OnCurrent, AfterUpdate, etc. I want to change it to a Public Function in a module and call it from the forms. Is there a way I need to change this? When I try to do it, I get an error on the 7th line. One problem, I think, is that when I open some of the forms, the Expense_Type is null. For that matter, when I open one of the forms, all the fields are null, so I think I need to add an IsNull - Do nothing code. Private Sub Expense_Type_AfterUpdate() If Me.Expense_Type = "Mileage"...

file size sometimes doubles when changing one or two cells
Hi, i have a very strange problem with excel2000. When i change one or two cells in a sheet containing links, the file size doubles or triples in size? This is only the case when working in a terminal server environment. When working with the same files in a normal environment (pc + network drive), this is not the case. Can this be a wrong setting in excel2000 on the terminal server environment? Any help or hints are very welcome. Thanks Wim ...

Sum, when data in adjoining column changes
I have spreadsheet with thousand of lines. Column A lists a name, and Column lists a percentage. When the name changes, I need to sum the percentages (they are supposed to add up to 100%) in Column C. Example: Column A Column B Column C AAA .50 AAA .25 AAA .20 AAA .05 1.00 BBB .10 BBB .10 BBB .75 BBB .04 BBB .01 1.00 (Sometimes the data in Column could be a number, such as 99-9999999.) Doing this by hand will take many hours, and I have to periodically repe...

Can onmouseover change row borders?
I have a datagrid with alternating bg colors, and I added an onmouseover event to shade them yet another color. Here's the way Google taught me to do it: If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then e.Item.Attributes.Add("onmouseover", "this.style.backgroundColor='#DFCBFF'") End If If e.Item.ItemType = ListItemType.Item Then e.Item.Attributes.Add("onmouseout", "this.style.backgroundColor='White'") Elseif e.Item.ItemType = ListItemType.AlternatingItem Then e.Item.Attribu...

Find Missing Number?
Ok probably a easy answer here, but I'm a excel Novice. what I have is a series of numbers and what I want to find is what Number are missing in between all the different numbers to complete a whole set of number. Example I have: 1 2 6 7 9 13 How could I find whats missing as to make it straight count up from 1 to 13 And return from this example 3 4 5 8 10 11 12 Thanks for any help Hi there, one possibility is in an additional column or sheet, i would create the whole series (type A1: 1, A2: = A1+1, then copy downwards until you have the required series) then, next to this, so...

IF function turning text to number
Hello, I'm trying to do something in an Excel worksheet and I'm not entirely sure if it's possible and if it is, am pulling my hair out as to how to do it! I'm basically working on a credit card cover sheet and want to show the NET and VAT of the total amount input on a row (so I'll be doing a SUM across five columns) whenever someone puts 'Yes' in a 'VAT receipt?' column. When they leave blank or put 'No' I don't want the formula to apply. I've spent a considerable amount of time trying to figure this out and was wondering ...

Line Numbers
How do I update the font for line numbers? I would like to change from Calibri to Times Roman. Go to the Styles panel (Ctrl-Alt-Shift-S), click the Options button at the lower right, set the dropdowns to "All Styles" and "Alphabetical," ok, locate the character style called Line Number, right-click, choose Modify, and change the font. You'll probably want to change "All Styles" back to "In Use" or "In This Document" before you go back to work! On May 28, 12:26=A0pm, SS <S...@discussions.microsoft.com> wrote: > How d...

Separate letters and numbers in postcode
Hi, hopefully this one will be an easy one for the excel masters! Ok - I have a postcode portion eg. WA1 2TT eg. W1 eg. N22 eg. RM11 And I want just to return WATT, W, N, RM for e.g. above Must be easy - surely -- Message posted from http://www.ExcelForum.com Maybe someone else can give you a standard worksheet solution, but cannot thing of one. You can however do it with a User Define Function (UDF). - Open up the Visual Basic Editor (Tools | Macros | Visual Basi Editor). - Right click on your workbook in the Project explorer and selec Insert | Module. - Paste the following code...

Dates show up as a 5 digit number in Excel 2007
I have this spreadsheet in which column A contains dates entered in the format 11/16 which, heretofore, has resulted in a cell readout of 16 Nov, or perhaps 16-Nov. Now, all of a sudden, the cells in column A have 5 digit numbers. Oddly enough, the value in the formula bar still shows a valid date readout. Also, when you place your cursor over a cell in column A, it displays a valid date. Example, cell A1 displays a value of 40127 while the formula bar displays 11/10/2009. I have tried highlighting the column and changing the format without success. I was unable to fig...

remove track changes
How do you remove "track changes" option from emailed documents? I am receiving documents by email with track changes. When I print, the document prints with the track changes. Help <Help@discussions.microsoft.com> wrote: > How do you remove "track changes" option from emailed documents? I am > receiving documents by email with track changes. When I print, the > document prints with the track changes. Click Tools>Options>E-mail Options. Uncheck "Mark my comments with". Or, if you're using Word as the editor, click Tools and un...

change colors in DialogBars
hi, How is it possible to change the colors in DialogBars, I mean something like OnCtlColor in simple dialogs, thanks, Behzad > How is it possible to change the colors in DialogBars, I mean something like > OnCtlColor in simple dialogs, Tried returning your own brush when nCtlColor argument in CDialog::OnCtlColor is CTLCOLOR_DLG ?, or am I misinterpreting your problem? Fahad Ashfaque hi, There is no problem in dialogs, I mean what to do if u want to change the colors in a DialogBar ? "Fahad Ashfaque" <FahadAshfaque@discussions.microsoft.com> wrote in message n...

Problems comming with change of Windows OS versions
Hi all, I work on MFC ,Dialog based applications........ when I develop an application and test it ,frequently I am facing one problem...It is the applications are working fine in one Windows OS version( consider XP) but if I test it another Windows OS version like( 98,2k,XPSP2)..there are some problems(like not underlining the shortcut keys,images not getting displayed..etc ) which are comming in that particular OS version only.... I want to know the reason behind this.....why is it working fine in one OS & not in other...?? I have clearly checked that the commands are g...

How do I change the order that my records appear in in Forms
I've entered all my data, but now I want it to be displayed in Forms in order by a specific field. How can I do that? Create a query. Use the Sort row in query design to specify the sort order. Choose this query in the RecordSource property of your form. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Bethany.Ryan" <BethanyRyan@discussions.microsoft.com> wrote in message news:69CAB1BA-4E89-4569-AC25-38CDFBFBE806@microsoft.com......

How do I use week numbers for sub-totalling?
I can obviously set-up week numbers manually on the spreadsheet, but was wondering if there was a function I could use that could work straight from the date field. Try a Pivot Table. That will allow you to group by week, month, quarter, etc.. http://www.contextures.com/xlPivot07.html In article <66474ADA-35F3-49E6-8CE5-490D1CE45F6E@microsoft.com>, Tinpot <Tinpot@discussions.microsoft.com> wrote: > I can obviously set-up week numbers manually on the spreadsheet, but was > wondering if there was a function I could use that could work straight from > the date ...

How do I change the pre-set font for email?
I noticed my font for writing a response to an email cahnged to Helvetica with a pale colour. How can I change this back? Note that this newsgroup is for Word, so you won't be able to get a specific answer here. Generally, though, if you are composing messages in HTML or RTF, you should be able to select the text and change the font and color. If you are trying to change the defaults, there is usually an Options dialog for that purpose. -- Stefan Blom Microsoft Word MVP "CaymanRN" <CaymanRN@discussions.microsoft.com> wrote in message news:61FB51DB-3E0B-4FBE...

Dynamically changing List lengths for Userform
Hello All, Ive got a User form that I would like to restrict response to a list from the worksheet. I would also like to change / remove some of the options available depending on the response to the previous ComboBox. Any ideas? Thanks Eddie(Universal) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ I'm not quite sure what you want to check to restrict values, but you might find an idea in here: Option Explicit Dim stopProc As Boolean Private Sub ComboBox1_...

How to automatically number a new document(Invoice) when opening
I am trying to set up a template in Excel 2000 and would like to be able to automatically number each invoice as the template is 63opeed to a new document, can anyone tell me in basic terms if this is possible and how to do it please? take a look here: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html In article <B94119A1-E4D8-4214-B5B9-3663E003E52F@microsoft.com>, "Gilly" <Gilly@discussions.microsoft.com> wrote: > I am trying to set up a template in Excel 2000 and would like to be able to > automatically number each invoice as the template is 63...

Custom number format "m"
I'm trying to define a custom number format "#,##0.0 cfm" but for some reason I am not able to put the letter "m" in the definition for my format. The "cfm" stands for cubic feet per minute. Anyone have any suggestions on how to get around this? It seems strange that I am able to define "#,##0.0 ft^3" but not cfm. Why would Excel (I'm using Excel 2000 9.0.4402 SR-1) not allow the use of the letter "m"?!? Is there anyway to accomplish what I am trying to do? Thanks, Paul B Paul, #,##0.0 "cfm" seems to work. Regards, ...

How to Change Language of Dates in Outlook Express
I work on the Occupied West Bank, but for some reason my computer thinks it's in South Africa and uses Afrikaans for the outgoing date in OE. I would like to change the language of OE to English, but I can't figure out how. The Help says to get a language encoding download from the Explorer homepage under international, but I can't find it. In any case, that doesn't seem to make a lot of sense, since all I am asking for is Microsoft's native language. I am just a regular user, so please be as clear as possible if you have any ideas. Thanks much, Katherine F...

Why can't I change the print quality in Excel?
See the subject. For some reason the "Print Quality" field in the "Page Setup" option is greyed out, I can't do anything with it and therefor I cannot use Adobe Acrobat to convert an entire workbook into PDF because it says the worksheets have different print qualities. Anyone who can help would be much appreciated ...

Random Number between two fields
I need to know if this is possible i want to get a random number generated between two figures. Example. A1 = 20 B1 = 30 C1 = i want this figure to be a random number between cell A1 and B1 i.e 24 Thank you. --------- www.coffeecozy.com Use your Bodum and give up cold coffee for good! If you want an integer: =MIN($A$1,$B$1)+INT(RAND()*(MAX($A$1,$B$1)-MIN($A$1,$B$1)+1)) HTH, Bernd Another way is to use RANDBETWEEN() For example: =RANDBETWEEN(A1,B1) would generate random integers between and inclusive of the values in A1 and B1 Note that RANDBETWEEN requires the Analysis Toolpak* be ...

Mobile number from 3 fields
I have a customer table that has 3 field for contact numbers, so are a mixture of mobile number and Land line number I would like a way to find out if any of the fields have a mobile number so any number starting with 07 and remove any space and dispay it in a text feild on a form I need the mobile number for sending auto text messages Thanks veyr much for your help On Mon, 12 Apr 2010 14:21:45 -0700 (PDT), Simon <S.Dickson@shos.co.uk> wrote: >I have a customer table that has 3 field for contact numbers, so are a >mixture of mobile number and Land line number >...

How to put continuos page numbers in document with more sections
I have a document with several sections and would want the page numbers to be continuous from the beginning to the end of the whole document. You don't mention what version of Word you are using, this will determine exactly who to do what you want. Basically though you need to check the box "Continue from previous section" in the Page Number format dialog box. Tell us the Word version for more exact details, but hopefully this will point you in the right direction. DeanH "Jen" wrote: > I have a document with several sections and would want the pa...

Changing the module associated with a custom report
Hi, we had our consultant create a customized report for us using Crystal reports, but he assigned it to the Sales Module. Is there a way to reassign that Report to the Service module? thanks! nasu Yes, it is called the Microsoft CRM v1.2 Report Manager. It allows add, delete, and rename reports even if you do not have Crystal Reports 9 installed. Here is the link to the download: http://www.microsoft.com/downloads/details.aspx?FamilyID=81517729-f640-4f46-ba30-6b9e6f451526&DisplayLang=en Good luck. Frank Lee Workopia, Inc. www.workopia.com "nasu" wrote: > Hi, we ...

How to Identify Changes In Two Work Sheets
I have two different dated inventory lists with 3,074 of identical line items. I want to determine the "items" that have "sold units" from two lists since they reflect ending inventory on separate dates. I want to create a new work sheet with those "items" only whose ending units are different from the units on the first ending report. The items are in the Desc 1 column in both work sheets along with their vendor code, size & quantity. Assume worksheet 1 is dated 10-18-06 and worksheet 2 is dated 10-22-06. Worksheet 1: Columns: C = VC (vendor code) , D...