negative dates or times are displayed as # # # #

I have two cells that carry two different times as follow
cell A1 has 00:05:00
cell A2 has 00:04:00
cell A3 should display the difference in negative way(-00:01:00). I only get 
##### instead of the answer in cell A3 and a message reads (negative dates or 
times are displayed as # # # #) Your help will be much appricated. thanks

0
11/8/2006 5:35:02 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
468 Views

Similar Articles

[PageSpeed] 1

That's what Excel does; it cannot work with negative time/dates
You could try =ABS(A2-A1) and have a flag in an adjacent cell 
=IF(A2<A1,"Neg","Pos")
best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Amr Shehata" <AmrShehata@discussions.microsoft.com> wrote in message 
news:10823894-1C01-42D3-8D06-360AEE05587D@microsoft.com...
>I have two cells that carry two different times as follow
> cell A1 has 00:05:00
> cell A2 has 00:04:00
> cell A3 should display the difference in negative way(-00:01:00). I only 
> get
> ##### instead of the answer in cell A3 and a message reads (negative dates 
> or
> times are displayed as # # # #) Your help will be much appricated. thanks
> 


0
bliengme5824 (3040)
11/8/2006 7:00:30 PM
This is normal.

Excel doesn't like negative dates and times.

For a workaround see John Walkenbach's site.

http://www.j-walk.com/ss/excel/usertips/tip051.htm


Gord Dibben  MS Excel MVP

On Wed, 8 Nov 2006 09:35:02 -0800, Amr Shehata
<AmrShehata@discussions.microsoft.com> wrote:

>I have two cells that carry two different times as follow
>cell A1 has 00:05:00
>cell A2 has 00:04:00
>cell A3 should display the difference in negative way(-00:01:00). I only get 
>##### instead of the answer in cell A3 and a message reads (negative dates or 
>times are displayed as # # # #) Your help will be much appricated. thanks

0
Gord
11/8/2006 9:14:47 PM
Thak you Bernard. Your idea has done the trick. Thaks for your help.
Amr

"Amr Shehata" wrote:

> I have two cells that carry two different times as follow
> cell A1 has 00:05:00
> cell A2 has 00:04:00
> cell A3 should display the difference in negative way(-00:01:00). I only get 
> ##### instead of the answer in cell A3 and a message reads (negative dates or 
> times are displayed as # # # #) Your help will be much appricated. thanks
> 
0
11/9/2006 12:34:02 PM
Reply:

Similar Artilces:

Displaying a single field or two fields together
I have a chemical database that I'm working on, and in a report on each chemical, I would like it to display the contents of two fields, like this: Label: [field one] & [field two]. However, not all records have the second field. So I'd like for the ones with only field one used to just display: Label: [field one], but the ones with both to display as I've shown above. Is this possible? thanks! JJ JJ1109 wrote: > I have a chemical database that I'm working on, and in a report on each > chemical, I would like it to display the contents of two fields...

sum between to dates from a pivot table...
I have this huge file with employment data. I want to make sure i have enough employees at the time when we have lots of customers, now when the vacations coming up. I have made a pivot table of the names and the work times of the employees every day, to the right of it i made a graphical presentation of their work times ciontaining formulas and custom formatting so i easily see where i need more people, i don't always have the same number of employees at work. I would like for my model to sum up the employees to though (as shown red text in the yellow field in the pic). The dates are fixe...

Date Format Problem
I have a worksheet with a column formatted as 022008 which is suppose to mean Feb 2008 or 02/2008. I can't seem to figure out how to change the cell format to reflect this. I want it to display 02/2008. In another cell enter: =LEFT(A1,2) & "/" & RIGHT(A1,4) or =DATE(RIGHT(A1,4),LEFT(A1,2),1) and format as mm/yyyy -- Gary''s Student - gsnu201003 "Alain" wrote: > I have a worksheet with a column formatted as 022008 which is suppose to mean > Feb 2008 or 02/2008. I can't seem to figure out how to change the cell format ...

Lines in Outlook Calendar boxes by Time
Can someone tell me how to show lines in each Calender box..., by Month, by Day, by 1 hour Increments, from 7:30 A.M to 5:30 P.M., Monday thru Friday? Thanks! -- Catfish "Catfish" <Catfish@discussions.microsoft.com> wrote in message news:FC1CE63B-E769-4541-88FD-E1C38CDCB574@microsoft.com... > Can someone tell me how to show lines in each Calender box..., by Month, by > Day, by 1 hour Increments, from 7:30 A.M to 5:30 P.M., Monday thru Friday? Some views are timed and some are not. Day and Week views will show the time scale. The Month view will not. ...

Formating Negative Figures in Brackets
Excel 2007 In earlier version of Excel, we could get a specific format for presenting the negative numbers in brackets and / or in red color. I could not find it in the Excel 2007. This can be done in the Custom Format. I do not have specifics for it. Can you give me the format for the same ? Why did the Excel remove this useful and oft needed feature from their new version, or am I missing something ? -- Shrikant The below might be useful http://support.microsoft.com/kb/832285 -- Jacob "Shrikant" wrote: > Excel 2007 > In earlier version of Exc...

Negative Allocation
I was attempting to post a batch that contained a transfer. The post failed and I recevied a message that "The serial number does not exist for this item and site". The items were moved from the source site to the destination, but the allcoated quantites in the source site are now negative. Any ideas how to fix this? Have you run the inventory reconcile process? (Utilities - Inventory - Reconcile) "Eric Capponi" wrote: > I was attempting to post a batch that contained a transfer. The post failed > and I recevied a message that "The serial number does not...

Epay transaction dates
Currently, I have epays that I manually submit before their due date, ie. the car payment due on the 17th needs to be submitted on the 12th. Is it possible to submit the epay and have the transaction date match the actual date the payment will be made? This happened automatically with Money2003, and was somehow turned off when I upgraded to Money2004. ...

How to run a rule only during specific time periods?
Hello, I have a server-based rule which redirects my office e-mail to my Blackberry. Due to carrier charges, I only want to send messages to it when I'm out of the office and right now I manually activate/deactivate the rule when I leave/arrive in the office respectively. How can I specify in Outlook that the rule should only be run during certain periods, e.g., from 18:00-08:00 Mon-Fri and 24/24 Sat-Sun? Unfortunately, the Blackberry site doesn't provide any way to limit forwarding messages to the handheld to specific time periods. Thanks, - Alan. Alan <bruguy@gmail.com> ...

making a negative effect
I am trying to switch the black and white color of a tempalte for an invitation from black background white lettering to opposite. any help would be appreciated dctakj <dctakj@discussions.microsoft.com> was very recently heard to utter: > I am trying to switch the black and white color of a tempalte for an > invitation from black background white lettering to opposite. any > help would be appreciated Printing this on white paper will be expensive because of the amount of ink used. Printing this in black paper will be (virtually) impossible. Now that's under your belt,...

Changing number to dates
When ever I try to paste the characters 6-16 or 4-14 for example it is displayed as a date. I have tried formatting the cells to read this information as text. it is always overridden and converted to a date. How do I paste this info and have it appear the way that I want? Your help will be greatly appreciated. Hi Craig format the target cells as 'Text' or insert the values with a leading apostrophe (') Frank Craig wrote: > When ever I try to paste the characters 6-16 or 4-14 for example it > is displayed as a date. I have tried formatting the cells to read > this info...

Return a date range based on today's date
I need to build a query (to be used for eternity) to return records for dates between 01 July (the year prior) and 30 Jun (the current year) based on the date when the query is run. Is this possible? Take a look at the DateSerial() function in Access HELP. It should provide you the mechanism for doing what you're describing. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code o...

how to date
In one column cell A1 I have date (eg Feb -01-2009) in another(B1) I have 25( which represents number of days).How do get the date in the third column cell (C1) which will be equal to A1+B1. The answer should be in date (eg Feb-26-2009). I am repeating this post as the solution offered by using A1+B1 Format as Date, if necessary. results in #value error. Please shed more light as I want the result cell C1 to display date as feb-26-2009 Thanking you Afd If you are getting #VALUE this implies that one of your values is text, as you can't add text in the same way as numbers. The mos...

enforce retention time
Hi, we have one information store which doesn't delete deleted items after the retention time. The server is ex2k/sp3/sp3 sept. rollup In the policy for this information store we have: (tab limits) keep deleted items: 28 keep deleted mailboxes: 7 do not permanently delete .... cleared We removed the store form the system policy, same settings on the store self, same result. Other stores on the same server don't have this problem. Does anybody known how to get this working ? Thanks in advance, Peter Is this store being backed up correctly? Also, check your overnight maintenan...

Date Format #11
Every time I type in the date it uses it for the year. I have my cells formatted for US 10-Nov-2007 but if I type Nov 10 it puts 1-Nov-2010 and if I type Nov 20 it puts 1-Nov-2020 Any ideas how to get a simple Nov 10 to insert Nov 10-2007 Hi, Are you in the US and is your country setting US? On my machine typing Nov 10 works to enter 10-Nov and if I then format it to your format it is correct. Try typing 11/10. or 11-10 Nov-10 Sincerely, Shane Devenshire Excel MVP "Martin Knight" <martinknight@hotmail.com> wrote in message news:sRG9i.358$NV3.11@pd7urf2no... > E...

Negative Expenses
I'm using Money 2007 Premium but have had this problem in the last several versions as well. Why is it that if I record a negative expensive, it is not reflected in the reports? An example: Let's say I go out to dinner with a friend. We receive the check and I pay by credit card and my friend pays me with cash to cover his/her part. I record the restaurant in the Food: Dining Out category in my credit card account and then record that I received the cash in my Cash account and classify it as a negative expense to the same category. However, when I view the reports, only the pos...

Free/busy info unavailable across time zones
We are running Exchange 5.5 on NT 4.0, with 3 sites (NY, DC and London). Everyone has access to view everyone's calendars, and rely on the free/busy info when scheduling meetings. However, for som reason users in NY and DC are unable to see free/busy info for our users in London, the field is simply 'greyed out'. Meanwhile they are able to get to these users' calendars individually by going through the 'open other users folder' function. Any suggestions on this one? 1. Is there a replica of the London Free/busy folder in the NY and DC site? 2. Verify that t...

How can I get Month-To-Date range with only selecting current date
I would like to be able to pull a report using today's day and have it query the current months data. For example, I'm creating an overall head count report, which includes several subreports references headcount numbers, however one subreport is for Attrition and I only want to reference the current MTD attrition. How can I have it reference's today's date and provide the current MTD data, or use a form to reference a date and have it return that current MTD data? I know the basic concept of using this expression in the query [Forms]![DailyReportDateSelector]![txtStart...

Average Time...
I've got the cells formatted with "Custom" and m:ss. I'm using the formula: =AVERAGE(F10:F15) and it gives #DIV/0 Is there any way to keep the times in minutes:seconds format and still get the average? Sounds like your times are actually entered as Text. Try this: Select an empty cell. Copy it. Select F10:F15. Choose Edit/Paste Special, selecting the Add and Values radio buttons. As long as you have at least one non-zero value in the range, that should give you an average. In article <gb1vo0131fr2egpa6q8hrdls9inpbsj40k@4ax.com>, Kevin <joe@upyours.com> wro...

Wrong Due Date on APAYs
My APAYs show the wrong due date on the Bills Summary page. For example, a monthly mortgage payment shows the next due date being Mar 2007. A monthly car payment, with only five payments left, shows the next due date being in 2011! The next payment on the Bill Calendar is also missing -- sometimes one or more -- not necessarily related to the missing payment dates from looking at the Bill Summary. This was a problem in Money 2003 before I upgr^H^H^Hchanged to Money 2007. After I installed M2007, I did the File Repair and also the Remove All Bills Data options. This seemed to correct the p...

XML validating and writing at the same time
I am receiving XML data that I would like to validate against my schema and write at the same time. Some of the data is optional, if the data in the optional elements is incorrect, I would like to write blank data to that element, and then continue validating against my schema. Any ideas? -={Brian Kedersha}=- kedershab@cox.net Brian, Interesting problem. I don't think there is an easy way to do this. Did you think of a way to approach it? -- Kathleen Dollard Microsoft MVP Author "Code Generation in Microsoft .NET" "Brian Kedersha" <bkedersha@aol.com> wr...

Negative Zero?
Sometimes when I subtract a number from itself, I get a negative zero, rather than a simple zero. Why is this? probably a small diff somewhere in the 15 digits of the number -- Don Guillett SalesAid Software donaldb@281.com "Herbert" <Herbert@discussions.microsoft.com> wrote in message news:9517F5D9-3A15-4A97-93E1-B442A948E3E0@microsoft.com... > Sometimes when I subtract a number from itself, I get a negative zero, rather > than a simple zero. Why is this? It means that your result is less than zero, but not within the precision you are displaying. For example, ...

run time error '-2147023170 (800706be)
I am getting this error when I never received it before in office 03. My local client was upgraded to office 07 and Win7 and now I get the above error or runtime 462. The error occurs at B.CloseCurrentDatabase. The other code works however I would like to close the database at the end of performing the tasks. This worked fine and faster in office 03. Sub DMRList() Dim B As Object Set B = CreateObject("Access.Application") B.Visible = False B.OpenCurrentDatabase ("G:\Robert\Compliance Shell\QADBASE_MASTER_20091207....

dates not recognised
Hi, From a third party source, I receive data in ".csv" form. When I open the file in excel, everything would work great on my old computer (with vista). With my new computer (Windows 7) something interesting happens. Some of the dates don't get recognised for some purposes. The formula countif(all the dates that equal today()) works, but the formula match(dates that equal today()) returns error, when there are some entries that should match. Further, conditional formatting the dates that equal today() also does not work. The problem seems to be that so...

Downloading dates to outlook
I am relatively new to microsoft program interaction so if someone ca help........please???? I have created an excel spreadsheet of memberships my organisation ha inc costs and renewal dates. I would like the renewal dates uploade to my outlook diary as a reminder for when these have to occur - an hlep with this would be greatly appreciated. I have search the norma places to no avail. thanks folks:confused -- Message posted from http://www.ExcelForum.com Hi I think this is more an Outlook question (think Outlook has an importing feature). You may ask this question in the Outlook NG -...

instead of two rows, one row is to be displayed
I am executing a query the following query. SELECT Salary_Details.Name, Salary_Details.SSN, Salary_Details.Employee_code, Monthly_Details.OT_Hours, Monthly_Details.OT_Payment, Monthly_Details.Other_Amount, Loan_Details.Loan_Number, Loan_Details.Loan_Amount, Loan_Details.Sal_Perdiem_Ind, Loan_Repayment.Repay_Amount FROM Salary_Details, Monthly_Details, Loan_Details, Loan_Repayment WHERE Salary_Details.Name = Monthly_Details.Name and Salary_Details.SSN = Monthly_Details.SSN and Monthly_Details.Name = Loan_Details.Name and Monthly_Details.SSN = Loan_Details.SSN and Loan_Repayme...