Formula reads date as number

I have a formula that reads a list of dates in cells K5 through K20 an
compares these dates to 1 date in cell M3. Cell M3 shows a date bu
when I run the evaluate formula auditing tool on the formula, M3 date
shows as a number, thus the formula fails.

Any hel

--
pete576
-----------------------------------------------------------------------
pete5761's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2961
View this thread: http://www.excelforum.com/showthread.php?threadid=49423

0
12/16/2005 8:11:35 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
420 Views

Similar Articles

[PageSpeed] 36

Dates are stored as number of days since 1-1-1900. So today (dec 16,2005) is 
stored as 38702.
That's the value you see in the formula evaluator, not how it was formatted.

-- 
Kind regards,

Niek Otten

"pete5761" <pete5761.205fyy_1134764101.9245@excelforum-nospam.com> wrote in 
message news:pete5761.205fyy_1134764101.9245@excelforum-nospam.com...
>
> I have a formula that reads a list of dates in cells K5 through K20 and
> compares these dates to 1 date in cell M3. Cell M3 shows a date but
> when I run the evaluate formula auditing tool on the formula, M3 dates
> shows as a number, thus the formula fails.
>
> Any help
>
>
> -- 
> pete5761
> ------------------------------------------------------------------------
> pete5761's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=29610
> View this thread: http://www.excelforum.com/showthread.php?threadid=494237
> 


0
nicolaus (2022)
12/16/2005 9:50:16 PM
Reply:

Similar Artilces:

Creating formula for timeline
I am trying to find a formula in Excel that will help me manage the following report: I have 2 columns...the first gives a list of months...ex. Jan, Feb, March, etc. I want a formula that will automatically populate the second column with a month that is 3 months past the first column...ex. 1st column is Jan 2nd is April. 1st column is Feb 2nd is May... Is this possible. I don't want specific dates in the month...just the month itself. Lynne This should do the trick for you. 'Borrowed' from a post a month or so back Remember they are text so no date calculations can be...

Mails deleted when read
Hi This is a strange one if I hadn't seen it myself I wouldn't have believed it. When some messages are read the messages then disappears after closing. It is not a meeting it is just a normal mail messages there are no Rules or Alerts running. It's not even in the deleted items or the recovery of deleted items. If anyone knows of the answer to this that would be great Thanks Shona ...

Optional Nttp Server
Just trying to gage some interest... Some of you may recall a few years ago Msft did away with the MSN news servre(msnnews) and myself and a few others Msn folks migrated over to a private server(AnnexCafe) to keep the group intact. That group eventually evolved in time into a user to user technical support group covering a diverse area for Q and A on all things Windows or related. I've investigated the ability to create a Windows Live Mail nntp newsgroup on that same private Nttp server(username/pw required, and its own TOS)... How many of you might be interested ? -- .......

How do I advance cell numbers?
Hi! In one worksheet, I have two columns: 1 column of dates and one column of numbers. On this worksheet, I sum the column of numbers based on the previous 90 days. Is there a way that I can enter a new date and number in one place on a different worksheet, and have it load that date and number in the next blank spaces on the worksheet I describe in the above paragraph? I would then want the next date and number I load to advance to the next blank spaces on the first worksheet, etc., etc. It is important to keep the 90-day total current. Thanks in advance... Sincere...

How to arrange alphabet and numbers in a cell
Hi, In one cell I have numbers and alphabets and how to arrange ascending or descending for example before Cell A1 = 6532ADC After Cell A1 = ACD2356 what formula I have to use? VBA code is preferable. Thanks Try the code below, used like =SORTCELL(A1, TRUE) HTH, Bernie MS Excel MVP Function SortCell(myR As Range, OrdAsc As Boolean) As String Dim myArr() As String Dim myTemp As Variant Dim i As Integer Dim j As Integer 'Split the string into characters ReDim myArr(1 To Len(myR.Value)) For i = 1 To Len(myR.Value) myArr(i) = Mid(myR.Value, i, 1) Next i 'Do the sort For i = LBou...

calculate date of birth with an end date and age
I have a sheet with a retirement date which will be the x birthday, and the age (x) of the client at that date. I need to get a date of birth for each of these people. Anyone know a formula that will achieve this?? Thanks Worker Retires Age DOB Fred 15-Mar-10 65 15-Mar-45 "Fred" is in A2, retirement date in B2, age in C2, DOB in D2 In D2: =DATE(YEAR(B2)-C2,MONTH(B2),DAY(B2)) This assume the retirement date exactly corresponds (month & day) to Fred's DOB I have used a dd-mmm-yy date format to save confusion (international vs USA) but the dat...

Excel 2000 formula recalc problem
Hi everybody, I have a problem making sure all the formulas in the spreadsheet recalculate, when recalc is called from within a macro. I have a complicated spreadsheet model that takes a second or two to recalculate. I run a macro that: 1. Sets up parameters 2. Forces recalc 3. Grabs the output 4. Cycles back to step 1 with different parameters, etc. My code looks something like this: For j = pageFirst To pageLast Step pageStep <set up parameters of the j-th scenario> Application.CalculateFull <save the output, which is just a rect...

Date entry
How can I enter a date without having to type a "/" between the month, day, and year? magstate, have a look here http://www.cpearson.com/excel/DateTimeEntry.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "magstate" <magstate@discussions.microsoft.com> wrote in message news:41CD9BD7-1836-447E-8811-32EE53D8EA8C@microsoft.com... > How can I enter a date without having to type a "/" between the...

Hyperlink to today's date #2
Sweet! Works like a charm, but... I have to share this sheet with othe and I think that the double-clicking-to-the-date may confuse them (i they try to edit a cell). Is there a way to map it to a hot key? Or make it so if I click in A it jumps to the date? (the scrolling to three rows up was a nice touch!) Thanks for the help again -- Grim/Do ----------------------------------------------------------------------- Grim/Don's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1445 View this thread: http://www.excelforum.com/showthread.php?threadid=26078 You could...

apply the formula sign
Hi, I=B4ve imported data from other application and some cells have some formulas like 10*12 but without the =3D sign. How can I apply the =3D sign to all the cells quickly without going one by one so that it calculates the formula. Example, =3D10*12, should be 120. Thanks!! Copy this UDF to a general module in your workbook. Function EvalCell(RefCell As String) Application.Volatile EvalCell = Evaluate(RefCell) End Function In an adjacent cell enter =EvalCell(cellref) Gord Dibben MS Excel MVP On Thu, 28 Jan 2010 07:12:14 -0800 (PST), canvas <spyele123@g...

Auto number new vendors
I have a client that adds vendors on a regular basis and they use numeric vendor numbers. Will there ever be a way for the system to offer an option to auto number the next new vendor ID? -- Debbie Bergman GP Consultant RSM McGladrey | Phoenix, AZ Debbie, Ethotech's Next Numeric Collection can do this: http://ethotech.com/nextnum.html -- Victoria Yudin Dynamics GP MVP Flexible Solutions - home of GP Reports http://www.flex-solutions.com/gpreports.html blog: www.victoriayudin.com "Debbie Bergman" <DebbieBergman@discussions.microsoft.com> wrote in message news:13E5...

Statements: Numbering and then being able to apply based on state
Have a client who can have 20,000 invoices for a customer in a month. The customer only gets a statement. Would like to be able to number statements and then if the customer pays the full amount be able to pay based on the statement number. Thought about using lockbox processing to try and set up a format based on the electronic data that comes back with the check as well. But need to find if anyone has a solution for the statement numbering We created a customization for a customer about 5-6 years ago that does something like this...it was pretty involved. From what I recall, ther...

Open read only but update when changes are made / refresh
Hi, If anyone can point in the right direction to answering this question, I would be very thankful. I was wondering if it's possible to lock an excel file and have other people open a read-only copy but have them recieve notifications when the locked file is updated. Is this possible? If you need any clarification, please ask. -Watson ...

EFT Prenote should read Checkbook identified in customer master
Client has EFT for RM and has multiple checkbooks - one US and one Canadian. The checkbook is selected accordingly on the customer master record. When Prenotes are generated the checkbook specified is not looked at and all of the prenotes are grouped together in a single file. They do not need Multicurrency nor do they want to add that complexity. There needs to be logic to look at the checkbook when the prenote is generated and currently that functionality is not built in. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the...

want parentheses, not negative numbers
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hello, <br> I'm trying to make an aside comment at the bottom of a 2008 excel table and need my information for one cell to appear in parentheses, but the program keeps autopopulating it to be negative number. <br><br>Thanks for your help. Parens are standard formatting for negative values, they can't be used as "literal" text characters in conjunction with numerical values. If you want them to display rather than the minus sign use Format> Cells - Number & select the...

Use a Date range to sum a column
What is the formula to sum Column F (the price) if the date is between 1/1/04 and 1/31/04 Date Pric 1/1/04 1073 2-A Druid Hills John Willis Homes Roger Swims $18,000.0 2/2/04 1074 3-A Druid Hills John Willis Homes Roger Swims $18,000.0 1/1/04 1075 1/7/04 1076 29 Darthmouth John Willis Homes Kevin Aycock $21,000.0 Thank you for your help. Looks like you are totaling per month =SUMPRODUCT(--(MONTH($A$2:$A$200)=1),--($A$2:$A$200<>""),$F$2:$F$200) if you r...

formula switches to value ?!?
what toggle, got switched where ? if A7 contains the value: "Christopher" and in B9 I enter: "=A7", it automatically switches the formula to the resulting value (both in the cell, and in the formula bar; the value of B9 is now not "=A7", but: "Christopher") thanks in advance, -mark mark kubicki wrote: > what toggle, got switched where ? > > if A7 contains the value: "Christopher" > and in B9 I enter: "=A7", it automatically switches the formula to the > resulting value (both in the cell, and in the formula bar; ...

plotting "empty" cells that contain a formula that has returned a blank
Cells that appear empty, but contain a formula that has returned a blank, when plotted on a line chart, plot as ZEROS. This is in spite of setting tools, options, charts to "zero values not plotted". Does anyone know a way round this, please. thanks Bob Farey Instead of return a blank (" " or "") result, return a NA(). If you need a null string ("") for subsequent calculations or for aesthetic purposes, create a 2nd dummy range that has NA() instead of "". Plot the range with the NA()s -- Regards, Tushar Mehta www.tushar-mehta.com ...

column headings have changed to numbers.
column headings have changed to numbers. My cells now read (1,1) Any suggestions on how to change back.. Hi Brenda tools / options / general - untick R1C1 reference style. Cheers JulieD "Brenda" <Brenda@discussions.microsoft.com> wrote in message news:688B1077-8C36-4649-B0B5-1ED2B98BAC50@microsoft.com... > column headings have changed to numbers. > My cells now read (1,1) > Any suggestions on how to change back.. ...

how do i increase the number of digits displayed
I am a person dealing with large numericals with digits more than 20 in number(for eg. visa no:s and phone card no:s)..when I type in a no: such as 12345678912345678912 and enter the no: it is displayed as 1.23457E-27 how do i enter such large no:s and have them displayed as such i.e without the aforementioned formatting Excel goes to 15 max so format as text or 'at the start of the number -- Don Guillett SalesAid Software donaldb@281.com "ameen" <ameen@discussions.microsoft.com> wrote in message news:36BF725E-CFDB-42A1-8F25-9020C7C82681@microsoft.com... > I am ...

Mail merge
We are attempting to create a mail merge in Publisher using data from an excel file. Several of the merge fields contain numbers which we would like to display as only two decimal places. I tried using the switch (\# $#,##0.00 ), but this didn't work in Publisher. Can anyone offer assistance? Format the fields as text... -- Mary Sauer http://msauer.mvps.org/ "Mwiegert" <Mwiegert@discussions.microsoft.com> wrote in message news:78545D0B-F736-4488-BE97-A5739A301754@microsoft.com... > We are attempting to create a mail merge in Publisher using data from an > e...

Format a cell with numbers and user defined text
I want to format a cell that will take user defined numbers and text in the following format: 12345678-A Another example would be: 23454368-X The numbers, dash, and text refer to Medicaid identification numbers and I want to be able to have a constant format when these values are entered. ...

Date format on a drop down box
I have a dropdown box that is linked to a cell where a date is put that is used for other things. The drop down box is filling itself from a range, which contains a sequential list of dates. I have verified that the dates are indeed dates and not text values. However, whenever I select a date using the drop down list, the link cell reverts to a serial format. I have tried to reformat the cell to be mm/dd/yyyy manually but without any success. Should be a simple reason but I am stumped here. Hi You have to format the cell' where you select the date, too. Formatting the data va...

Problem with limiting a report to a date range when dates are the same
Hi I used Allen Brown's tip for limiting a report to a date range. http://allenbrowne.com/casu-08.html It works great and is elegant and flexible. However, I just found that if I want to limit the report to a single date and enter the same date in the start and end date, I get no records even though records exist for this date. Please let me know how I can limit the report to just a single date. Thanks in advance Mark If the start date and end date are the same, the code should return the records for just that one date. If it is not doing that, chances are that your field contains...

Negative numbers #4
I need to be able to import negative numbers, but when they have right hyphens, they are considered text not negative numbers. I've tried to change the "regional settings" for negative signs, but it's made no difference. What can I do? Thanks, Blake Blake During the import, on the last panel there is an "advanced" button. Click that and you'll see a check box for trailing minus signs Ar ----- Blake Witten wrote: ---- I need to be able to import negative numbers, but when they have right hyphens, they are consid...