Summing dates

I have table like this
Date 1        Date2        Diff Date2-Date1
      19.12.05 10.01.06 0Y 0M 22D

      14.01.05 26.02.05 0Y 1M 13D



Which formula to use to sum dates in column 3.
For easyer calculations assume that month have 30 days.

Tahnx 


0
7/6/2006 1:15:24 PM
excel 39879 articles. 2 followers. Follow

2 Replies
593 Views

Similar Articles

[PageSpeed] 54

See:

http://www.excelforum.com/showthread.php?t=558873


-- 
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29182
View this thread: http://www.excelforum.com/showthread.php?threadid=558872

0
7/6/2006 1:40:59 PM
Just subtract

=B2-A2

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Snergle" <snergle2002@yahoo.com> wrote in message
news:e8j2jk$jo7$1@sunce.iskon.hr...
> I have table like this
> Date 1        Date2        Diff Date2-Date1
>       19.12.05 10.01.06 0Y 0M 22D
>
>       14.01.05 26.02.05 0Y 1M 13D
>
>
>
> Which formula to use to sum dates in column 3.
> For easyer calculations assume that month have 30 days.
>
> Tahnx
>
>


0
bob.NGs1 (1661)
7/6/2006 2:53:55 PM
Reply:

Similar Artilces:

Date changes from copy to paste
I copy a date cell (let's say 12/9/09) from one spreadsheet and when I paste it into another spreadsheet, it goes to 12/9/13. What!? Any clue as to why it would be adding 4 years to the date? Compare the workbook options of both workbooks under Tools>Options>Calculation> '1904 date system' -- Jacob "Nathan" wrote: > I copy a date cell (let's say 12/9/09) from one spreadsheet and when I paste > it into another spreadsheet, it goes to 12/9/13. What!? Any clue as to why it > would be adding 4 years to the date? ...

Date Format 12-10-03
Hello all... trying to create a phone call activity and set the 'ActualStart' datetime value, but CRM is complaining that it's not in a correct format. I'm passing it in standard .NET format, "MM/dd/yyyy hh:mm:ss am/pm". What format is it expecting?? Thanks! -mdb assume you have a string containing your date.. called start - format crm needs is shown below ======================== DateTime MyDate = System.Convert.ToDateTime(start); String mydate = MyDate.ToString("yyyy-MM-dd HH:mm:ss"); strXml += "<scheduledstart><![CDATA[&qu...

Help with date format
Hello ! When I use this function =now()-1 it returns yesterdays date and time less one day in the format that I choose in the format menu. This is my problem. I want a formula like this. "ICSSA Daily Report For "&now()-1 I would like the result to be ICSSA Daily Report For 12-Aug-05 The result that I get is ICSSA Daily Report For 38575.50547 I need to format the date number for the date above but have not been able to figure out how to accomplish this. Many thanks Bob ="ICSSA Daily Report For "&TEXT(now()-1,"dd mmm yyyy hh:mm:ss") adjust the ...

How to perform sum sum sum...
How to perform sumation within that particular item but the item is not unique...means item 1 has its own quantity and same goes to item 2...but in the same table... -- Message posted via http://www.accessmonster.com On Tue, 17 Apr 2007 05:28:12 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >How to perform sumation within that particular item but the item is not >unique...means item 1 has its own quantity and same goes to item 2...but in >the same table... Group By the item. For a more detailed answer, please post a more detailed question (with a des...

Date-range specific report with counted results of combo box selections
Hi there, I'm a novice with Access, but am currently setting up a patient database for an NGO in India that I'm volunteering with. The primary reason for the database is to collect statistics quarterly. It is a simple database with one table that collects patient data from simple forms with some fields having combo boxes. Ultimately I want to generate reports that will show a summary of 'counts' of various fields for a given period. For example: In the table I have a combo box for where the person came from, with several options including - "Pick up Y. Bazar", &quo...

Dates from Julian calendar
I know it can be done - just don't know how! Embedded into barcodes we use the Julian date system. I need people to enter the julian date into the spreadsheet and the adjacent cell to display the date. Excel seems to give every possible way to convert to Julian dates but not the other way round Can anyone please help this numpty? Thanks Tony -- y_not ------------------------------------------------------------------------ y_not's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19947 View this thread: http://www.excelforum.com/showthread.php?threadid=47...

Summing up user defined results
Hi all - I'm new to VBA programming in Excel and so any help i'd totally love! I'm currently writing a function. Objective: user can select rows (do not have to be sequential). User clicks on button. UserForm appears with summed results from ONLY rows that he selected. What I have now, well it doesn't work: Sub Button6_Click() Dim i As Integer Dim totalNumbers As Integer Dim aRange As range For Each a In Selection.Areas 'MsgBox "Area " & i & " of the selection contains " & _ ' a.Rows.Count & " rows." &...

Multiple variables to sort and sum, return values<0 with sum refer
Can anyone help me with this one please? I have this table of data. I'd like to be able to write a formula(s) which sums the No according to date and code, but then only returns a sum value (with the code and date in the two adjacent cells) if there is a value greater than 0. Code Ref No Date 1 G/032/05/999 400600 212 19/03/2010 2 G/032/03/001/999 400500 50 19/03/2010 3 G/032/02/001/001 400400 170 19/03/2010 4 G/032/05/999 400600 315 19/03/2010 5 G/032/03/001/999 400500 300 19/03/2010 6 G/032/05/999 400600 202 19/03/2010 7 G/032/03/001/002 40...

Date formulas
Hi, I'm doing a timesheet. The month begin on the 21 and end on 20th of each month (don't ask). It'easy to set a listmenu to choose the month, have the first day to start with the 21, having 31 line with =1+precedente line. But for the three last line, idon't know how to have it to stop displaying dates after the 20th, for Febuary and the other 30 days months. An idea anyone? thank In A2: =IF(A1="","",IF(MONTH(A1+1)=MONTH(A1),A1+1,"")) Copy down as far as needed -- Kind regards, Niek Otten Microsoft MVP - Excel &q...

concatenate dates?
Is it possible to concatenate dates? In A1 I have 01/01/05 and in B1 I have 01/02/05. What I ant is for C1 to show "01/01/05 - 01/02/05" but when I try =A1&" - "&B1" I get "38353 - 38384". Gavin Try: =TEXT(A1,"mm/dd/yy")&" - "&TEXT(B1,"mm/dd/yy") HTH Jason Atlanta, Ga >-----Original Message----- >Is it possible to concatenate dates? In A1 I have 01/01/05 and in B1 I have >01/02/05. What I ant is for C1 to show "01/01/05 - 01/02/05" but when I try >=A1&" - "&B1...

Creating a Worksheet with date
Hi, I'm trying to creat a wroksheet that will have two weeks of date at the top row to be printed every two weeks and each time printing, it shows the next two weeks automatically. I'm very new on doing this, please help if there is anyway of doing this. Thank you in advance for your help. Regards, MD Thanks Paul, It worked. MD >-----Original Message----- >Md, how about putting the first date in A1 and then this formula in B1 and >copy accross to as many columns as needed, then you will only have to change >the date in A1, this could be changed automatically wh...

sum subform to form
=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged]) Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a field named Pledge Amount. I want to put a field on the form that gives me a total of the subform's Amount Pledged. I have tried the above, but I get an #error in the unbound text box. Do you need any more info? Any thoughts on why this does not work? Thanks in advance, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1 The proper syntax is =Sum([Forms]![frmCa...

Automatic filling of cells within a certain date range
Can someone please help, I have been stuck on this for the past two days... Column 'AB' has 'Start Date' and column 'AD' has 'Finish date' Row 19, cells 'AI' to 'DW' have the months written as 'J' 'F' 'M' 'A' etc.....from 2008 to 2015. I want to be able to enter a 'start date' in column 'AB' i.e. 1 January 2008 and a 'Finish date' in column 'AD' i.e. 1 April 2015. Then i would like the cells 'J - A' on row 19 to be filled with '1' in each month.... I...

Calculate sum of a field within the query
I have a query that returns the details for a failure record during a specified time period. Where if I search between 1/1/08 and 1/31/08 the query returns the records of all failure records during that period. The row shows the part number and the total parts failed for that record. I need the query to then sum the total part failures for the previous 12 months. So I would see the following: Record # | part # | parts failed | Origination Date| Total Parts failed past 12 months 44444 | x | 4 | 1/2/08 | 200 44445 | Y |1 ...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

Date ranges
Hi I have a list of predefined dates in column B in colum C formula to show todays date. (i.e. if today = 24-6-06) What I want is D1 to to show B2 (19-7-06) and D2 to show B1 (12-6-06) A B C D 1 1 12-6-06 =today() next date = 2 2 19-7-06 previous date = 3 3 24-8-06 4 4 30-9-06 Any help appreciated. Regards Paul T Paul T wrote: > I have a list of predefined dates in column B in colum C formula to show > todays date. (i.e. if today = 24-6-06) > What I want is D1 to to show B2 (19-7-06) an...

=SUM Ranges Do Not Update
I have a Excel 2000 spread sheet, with the following macro to insert new row. Sub InsertRow() ' ' Macro1 Macro ' Macro recorded 4/27/2004 ' 'GoTo label, MyString ActiveSheet.Unprotect Application.Goto Reference:="MyCell" ActiveCell.Select ActiveCell.EntireRow.Insert ActiveCell.Offset(-1, 0).Select ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).Select ActiveCell.PasteSpecial xlPasteAll Application.CutCopyMode = False ActiveCell.Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True Scenarios:=True End Sub The problem is that in the "Mycell&qu...

Need help with automatic date entry
In a sheet used as a data base, how do I have the current date updated or changed when the data changes in a record or row. I would like this to show when that record has been modified with out manually changing that cell. Hi Rightclick the sheet tab, choose "view code", paste this macro in: Private Sub Worksheet_Change(ByVal Target As Range) If Target(1).Column < 5 Then _ Cells(Target(1).Row, 5).Value = Now End Sub Replace 5 with whichever column number your dates are stored in. HTH. Best wishes Harald "Mike200575" <Mike200575@discussions.microsoft.com&g...

Due Dates
I have customers that have different terms, llike Net 7, Net 30, etc. How do I change or set due dates on transactions or add Terms to the invoices. Please help!!! Scott Look at setting up an Account for each term. Rob "Scott" <Scott@discussions.microsoft.com> wrote in message news:42F73DA0-D991-4BE9-8D9C-36014B512D62@microsoft.com... >I have customers that have different terms, llike Net 7, Net 30, etc. How >do > I change or set due dates on transactions or add Terms to the invoices. > Please help!!! > > Scott ...

Sum value between dates problem driving me mad!
Good Evening All, I am really struggling here, have tried, nested IF's including AND's but am a bit stumped. I am fairly familiar with Arrays, but cannot seem to combine all. I have a data table (as detailed below). (Hopefully, this should be 'pastable' into Excel - it works for me) I simply wish to calculate the expected totals per month. Any help & assistance with this would be most welcome. Cheers, Mathew Note, earliest Start is 01/04/03 Start Finish Day Rate Apr-2003 May-2003 Jun-2003 Jul-2003 Aug-2003 01/04/2003 23.00 01/04/2003 09/07/2003 23.00 ...

Conditional Sum Wizard
Currently I am setting up a sheet and I am using the conditional sum wizard for formulas. Once a formula is created the sytem won't let me copy or change a formula. If I try to copy (Copy, paste special) a formula to another cell, the formula won't work anymore. If I create a formula with the wizard and afterwards change one of the parameters, the formula does not work anymore. Can anyone give me a hint on how I can solve these issues? Thanks. Please don't multi-post - you have an answer elsewhere, relating to use of CSE. Pete On Jan 6, 9:23=A0am, MarcoKoenders <MarcoKo...

Sum Question
I am using Excel 2000 I have the following very simple formula in column j Sheet1 =SUM(B5*H5) I have this copied all the way down to line 40. It shows 0 in all of the cells all the way down. I would like for there to be a way that the cell would not show anything in it UNLESS there was something that it was calculating. For example. I have entered data in column b and column h through line 10, but it still shows 0 in column J all the way down to line 40. I realize that it is showing these 0's b/c I have placed the formula there, but is there a way that it will still calculate but only ...

Date in 5 weeks from entered date
What is the formula that would allow me to enter a date in one cell (B1) and have B3 show the date 5 weeks from the date entered? =b1+35 Make sure the cell is formatted as a date. rudyeb wrote: > > What is the formula that would allow me to enter a date in one cell > (B1) and have B3 show the date 5 weeks from the date entered? -- Dave Peterson Hi =B1+35 5 weeks * 7 days -- Regards Roger Govier rudyeb wrote: > What is the formula that would allow me to enter a date in one cell > (B1) and have B3 show the date 5 weeks from the date entered? ...

Sum sum sum
Let me describe my problem... I have a form with with item number with is unique and then i put another form (subform) to linked to record the number of item in and out.... This means a item can have many in and out transactions which make the item number not unique.. My problem is I want to calculate the sum of in quantity within the same item. I want the sum to be keep on increasing when the user enter the quantity. Example user key in 2 then new user key 4 and i want the total quantity to be 6... Please help.... No idea of what i am talking? I will explain... -- Message posted via http:/...

Excel: How do I enter a date in one cell & automatically dates adj
In excel, I am trying to find a formula or funtion so that when I enter a date (1/10/05) in cell A1 for example, then B1 automatically changes to 1/11/05, C1 to 1/12/05, etc. Can anyone help? Thanks!! In cell B1 your formula is =A1+1 That will advance the date by one day. Copy that formula across, and you're good to go. Hi Dave O, Thank you so much! Here's another question that has developed after I entered that formula. Before I have a date in A1, B1 states "1/1/1900", C1 states "1/2/1900"; but it works perfectly once I enter a date in A1. I'm trying ...