#### Subtracting dates?? AGRRRRRRRRR!!!

```I am subtracting 2 dates. To keep in simple, I am just subtracting th
dates in 2 cells as I am having trouble with datedif.  Anyways, when
enter the formula, =sum(a1-a2), it does the trick, but, before I hav
entered the dates I want the difference in, it put a number like -3820
in the cell.  How can I get Excel to just leave the cell blank until
enter a date?  Once again, thanks in advance.

Miles Ree

--
mcr
-----------------------------------------------------------------------
mcr1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1549
View this thread: http://www.excelforum.com/showthread.php?threadid=27477

```
 0
11/3/2004 5:28:51 AM
excel.misc 78881 articles. 5 followers.

1 Replies
580 Views

Similar Articles

[PageSpeed] 47

```Hi Miles,
=IF(AND(A1>0,A2>0),A2-A1,"")
Assuming the second date is the later date.
Note the above will be an empty string, it will not be an empty cell.
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"mcr1" <mcr1.1f4qzy@excelforum-nospam.com> wrote in message news:mcr1.1f4qzy@excelforum-nospam.com...
>
> I am subtracting 2 dates. To keep in simple, I am just subtracting the
> dates in 2 cells as I am having trouble with datedif.  Anyways, when I
> enter the formula, =sum(a1-a2), it does the trick, but, before I have
> entered the dates I want the difference in, it put a number like -38204
> in the cell.  How can I get Excel to just leave the cell blank until i
> enter a date?  Once again, thanks in advance.
>
> Miles Reed
>
>
> --
> mcr1
> ------------------------------------------------------------------------
> mcr1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15496
> View this thread: http://www.excelforum.com/showthread.php?threadid=274779
>

```
 0
dmcritchie (2586)
11/3/2004 5:46:00 AM
 Reply:

Similar Artilces:

a running sum (by date) in a query?
Is this possible? I have dates (some of which are the same) and I would like to use them to create a running sum of another column in my query. Is this possible? On Jul 24, 4:54 pm, grantschnei...@gmail.com wrote: > Is this possible? I have dates (some of which are the same) and I > would like to use them to create a running sum of another column in my > query. Is this possible? Sorry, not only do i need to create a running sum by date but it also has to be PER sales person. So even though all the sales people are in one table and I am querying the table different ways, I need to m...

Text date to date
Is there a function that will convert text dates (month, year) JULY, 2009 AUGUST, 2009 SEPTEMBER, 2009 to this: 7/1/2009 8/1/2009 9/1/2009 Thanks. You can use: =--SUBSTITUTE(A1,","," 1,") And then format the cell the way you like M/D/YYYY (and even convert the formula to values if you want to delete the original data) T2B wrote: > > Is there a function that will convert text dates (month, year) > > JULY, 2009 > AUGUST, 2009 > SEPTEMBER, 2009 > > to this: > > 7/1/2009 > 8/1/2009 > 9/1/2009 > > Thanks. -- Dave Peterson ...

Chart data scrunches to left when adding X axis date series
I have a combination chart that has stacked bars and a line series. I have a Y axis with a secondary axis. I'm attempting to add a X axis with a date series. As soon as I do that, Excel starts thinking for me and all my data scrunches to the left side of the chart. I don't' have any idea how to fix this. ...

date subtraction difficulty
I'm trying to subtract dates, to calculate time duration. I'm subtracting column A from column B into column C. Column C is formatted 'm "mos", dd "days"' and columns A and B are formatted 'mm/dd/yy/@' Unfortunately each of the durations is 1 month too long. That is, if the real duration is 4 days, column C will display '1 mos, 4 days' Any clue what I'm doing here? Thanks much. On Tue, 18 Jul 2006 11:58:10 -0500, Peter <facetious_nickname@hotmail.com> wrote: >I'm trying to subtract dates, to calculate time durati...

Remove time from date upon export to csv
I cannot remove the time from the date when exporting invoice data to a csv file. I have followed previous advice and created a query based on my tblInvoices table, in which I have formatted the InvDate field in the Design Grid as follows: Date: Format([tblInvoices.InvDate], "dd/mm/yyyy") (UK format) In the Query, this is fine - just the date, no time shown. I saved an export spec in the Advanced bit of the File/Export wizard. But in the resulting csv, there are six zeroes representing the time! However if I do a manual export by following exactly the same process bu...

Excel template for the date of Easter
Does anyone have access to an Excel template for finding the date of Easter in any given year in the past or in the future? Hi see: http://tinyurl.com/3lttf -- Regards Frank Kabel Frankfurt, Germany Dadgum wrote: > Does anyone have access to an Excel template for finding the date of > Easter in any given year in the past or in the future? There was a competition in Germany to find easter date in excel, I believe this won =DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6 however it won't work for Mac date system (1904), this formula by Gerhard Somitsch w...

Dates as text
using a formula, I am combining a column heading and the date in that row, so that the cell will read "Column Heading Date", but the date is showing as a number rather than a date. I want the cell to contain both txt (column heading), and a date. How do I format it to do so? assuming your date is in cell A1: ="heading text "&DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1) -- hope to help, cm "lightbulb" wrote: > using a formula, I am combining a column heading and the date in that row, so > that the ce...

Date Function?
I want a criteria that will return a list of records within a specified month. The "Month" and the "Datepart" Function don't seem to be working for me. Any advice? "Caravatis" <Caravatis@discussions.microsoft.com> wrote in message news:B3B4C87B-24EA-4B27-A454-17CDAF31751A@microsoft.com... >I want a criteria that will return a list of records within a specified > month. The "Month" and the "Datepart" Function don't seem to be working > for > me. Any advice? SELECT tblMyData.* FROM tblMyData WHERE (((Month([Dat...

Subtracting Cells formatted with Date AND Time to get hours?
Thanks in advance for the help. Is it possible to subtract 2 cell that are both formatted as mm/dd/yyyy h:mm to just have it subtrac the times to get a total # of hours between the 2. For example, A is 1/1/05 13:00 and B1 is 1/2/05 14:30. I'd like C1 to give me th total of 25:30. This forum is always a great place for help. Thank again in advance for your help. mcr =B1-A1 and format as [hh]:mm -- HTH Bob Phillips "mcr1 - ExcelForums.com" <miles@milesreed-dot-com.no-spam.invalid> wrote in message news:Oy629ATgFHA.4032@TK2MSFTNGP10.phx.gbl... > Thanks in advan...

Date and Time 05-14-10
I have cells in a worksheet that are formatted as date but still show as date and time. How do I pull out the time. E.g. 04/07/2009 1:00:00 AM I have tried the =left() and also =right () formulas with no luck. Date and time are just numbers underneath the formatting. 04/07/2009 1:00:00 AM is 39910.04167 Format the cell to General to see that number. Use Data>Text to Columns and delimit by decimal point. In third step choose the right hand column and "Skip" Finish Re-format the cell. Gord Dibben MS Excel MVP On Fri, 14 May 2010 14:30:0...

Compare two dates
Hi,. I would like to compare two dates in the same row and return a true or false statement. How do I do this? Could you please elaborate your query. Do you mean? With dates in A1 and B1 =IF(A1=B1,"dates are same","dates are not the same") If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: > Hi,. I would like to compare two dates in the same row and return a true or > false statement. > > How do I do this? =A1=B1 -- David Biddulph "Dave" <Dave@discussions.microsoft.com> wrote in...

Form and default dates
Related to my question below.... I'm setting up a form to filter results of a query, I'm trying to set default dates (at the least so when you open the form nothing will be filtered by default, at the best I'd like to be able to open the form directly without it prompting me to enter values for the filters and just show everything by default) At any rate I have 2 issues now. First no matter what I put in Default Value for my date entry boxes on my filtering form, 12/30/1899 shows up in both. Second as soon as I click in one of the boxes to enter a date it changes ...

Select Date based on date value
Is there a formula where I could select a date based on a condition? here is a scenario of what I want the formula to do: I have 3 dates: start date,end date, and invoice date, let's assume that the start dat is 9/7/2005, end date is 10/6/2005 and invoice date is 10/8/2005. no even though the invoice was sent on 10/8/2005, I would like to identif this month as september because the bulk of the month fell on september I guess the rule would be(per example above): per example above: if starting month >= 09/20 then select 10/2005 else month < 9/20 then select 9/2005 so if the 20th...

subtracting units of time
Hi there, I am trying to keep track of my hours at work, as I need to make up a day I spend at hospital during the course a month. I have put together a sheet (sort of a cut-down version of my department's timesheet), with day, month, in and out. What I want to do is subtract 30 minutes from each standard work day, so I end up with 8 (or however many hours worked) minus my half hour lunch break. Any help would be greatly appreciated. Regards, Glenn Auckland, new Zealand Glenn Stick 0:30 in a cell and name it lunch_break. Then use = A1-lunch_break, where A1 contains your times be...

date format #9
Hi everyone Is there any way to add automatically "rd" or "st" or "th" etc after the date. I have a date in A1 as "30-01-2001" and I need to show in B1 as "January 30th" Hope someone has an idea Thanks Toms --- Message posted from http://www.ExcelForum.com/ Hi SMILE! One Way. Use: =DAY(A1)&IF(AND(MOD(DAY(A1),100)>=10,MOD(DAY(A1),100)<=14),"th",CHOOSE (MOD(DAY(A1),10)+1,"th","st","nd","rd","th","th","th","th","th","th")...

Date Range
Using SQL 2005 Is there a way to query a date range with only one date provided? The date field is a timestamp with mm/dd/yyyy mm:ss. User provides date from a dropdown list on a form - example 12/01/2009. We want to provide all records that fall in the month of Dec 2009. SELECT [DATE] FROM tbl WHERE [DATE] >= '12/01/2009' ??? How to get just the dates for that month? Your help is greatly appreciated -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201002/1 You can calculate range from first of the mont...

Date sent items are 2 years earlier to the system date
When I sent mail using Outlook 2000 on a Windows 2000 svpack 2, the sent date is 2002. The receiver also sees this date. The system date of my PC is correct, as is the system date of the server running Exchange server 5.5 Other users do not have this problem. Please help! ...

Subtract
On sheet A, I have column: A1(product name) A2(quantity). When I copy a row with the data in above columns to sheet 2, it must automatically deduct 1(one) from A2. Can you please help? Thanks. --- Message posted from http://www.ExcelForum.com/ Hi this would require VBA (using event procedures) Do you really want to go this way. Note: excel is IMHO not well suited for maintaining an inventory -- Regards Frank Kabel Frankfurt, Germany > On sheet A, I have column: A1(product name) A2(quantity). > > When I copy a row with the data in above columns to sheet 2, it must > auto...

Sorting by Date (With a Twist)
A coworker gave me a list of data. One of the columns of this data was a date field formated custom in mm/dd/yyyy format. The problem was that when my coworker sorted the date field it was sorting by the day. No problem I thought, so I created a new column typed in =year(e2) which was the cell of the first date. To my suprise the data came back as 1905. The date in E2 was 5/16/2003. If I did =month(e2), I got January (in mmmm) format. What gives? -- B. Baumgartner ------------------------------------------------------------------------ B. Baumgartner's Profile: http://www.excelf...

Subtracting figures
If i was to sell a product that i had bought and entered into Microsoft Money how do i tell money to minus the balance of what i sold it for? For example, i buy a phone for \$50 and this goes under the Phone expenses category. Later on i decide to sell it and i manage to get \$20 for it. How do i tell money to minus the \$20 from the original \$50 i paid? Thanks In microsoft.public.money, Stan wrote: >If i was to sell a product that i had bought and entered >into Microsoft Money how do i tell money to minus the >balance of what i sold it for? For example, i buy a phone >for ...

Making a graph with easily changed dates
My boss asked me to make a graph on one of his spreadsheets more user friendly...so that you could put a start and end date in two ajacent cells and the graph will automatically change to show the time between those dates. I didn't think it was going to be too hard but now i'm completely stumped...I've got the dates on the X-axis and 3 other lines that come from data on another sheet...and i can't even get the X-axis to easily adjust to two changeable dates (it will just show those two days rather than all the days inbetween).....if anyone has any suggestions i'd gre...

deleting wrong dates
Hello... i have an access 2007 database. I have a date field ([Sales-Date Install #9]) with incorrect data in it. I would like to update those fields to Null value. here's the sql i'm using: update STR_CRM_1_Contacts set STR_CRM_1_Contacts.[Sales-Date Install #9] = Null WHERE not isdate(STR_CRM_1_Contacts.[Sales-Date Install #9]); unfortunately this doesn't work. I have data in this field, like: 11/30/122 or 11/30/134... I would like to get rid of those incorrect values. so what's wrong with my query??? Any help will be appreciated. Thanks Eduardo Those are valid...

Activity modified date
Is it possible to show the activity modified date on the Accounts view? I am able to add the modified date to that view - but that just changes when the actual account is updated - not when an acitity is added/changed. the modified ondate is tied specifically to an object such as account etc Also the views will only show data from each object ie you could not have a view showing fields from both an activity and account on the same view. You may have to create a custom view using the SDK to achieve this "Kelly Hunt" <anonymous@discussions.microsoft.com> wrote in message n...

Time Subtraction...
Hello all... I been trying this for hours.... Is there a way to subtract / add TIME on a timesheet? I have an excel document that the user puts in time in and time out (format hh:mm).. Then they have to go to the ttl hours field and do the subtraction/addition themselves... Is there a way to do this? Thanx!2 cZ Yes, use =1time+2time or =sum(1time,2time) or =sum(timerange) for adding and use minus for subtracting =end_time-start_time -- Regards, Peo Sjoblom "George Czarnonycz" <czar_g@charter.net> wrote in message news:vsmuoa54o833bf@corp.supernews.com... > Hell...

Formula needed to calculate difference between Two dates and times
I want to know the formula for calculating the below mentioned problem col A B C 1 10.8.08 11 AM 11.8.08 3PM 2days ( i need formula here) We have a small inn and i need to calculate the duration of stay by a guest. Checkout time is 24 Hrs. The Column C should display 1 if the duration is less than 24 Hrs and it should display 2 if its more than 24 and less than 48 and so on. PLEASE HELP Put this in C1 =ROUNDUP(B1-A1,0) and copy down. Format column C as Number with no decimal place. Col A & B should have dates with time. Le...