Date Subtraction

Can SKS give me the formula to subtract two dates where the answer is in
years (to 2 decimal places)

eg    A1        23/10/92
        A2        1/5/68

Answer in the format "xx.yy" years

Tx

Rob


0
rfj1x2 (4)
7/28/2003 6:43:29 PM
excel 39879 articles. 2 followers. Follow

1 Replies
686 Views

Similar Articles

[PageSpeed] 3

Found the answer in another newsgroup (it is the datedif function - and does
not seem to documented)

Rob

"Rob" <rfj1x2@ntlworld.com> wrote in message
news:m8eVa.3847$G85.1163@newsfep4-winn.server.ntli.net...
> Can SKS give me the formula to subtract two dates where the answer is in
> years (to 2 decimal places)
>
> eg    A1        23/10/92
>         A2        1/5/68
>
> Answer in the format "xx.yy" years
>
> Tx
>
> Rob
>
>


0
rfj1x2 (4)
7/28/2003 7:08:25 PM
Reply:

Similar Artilces:

Calculate 1st of month date from existing date.
I want Excel to post a starting date which is 45 days (or other time period) from an initial date. The rules are: (1) If the date is under 15 (middle of the month) set the starting date to the 1st of the month as calculated and (2) if the date is 15 or over, set starting date to the 1st of the next month. For instance, if my starting date is January 1st, 2006 and I want the date 45 days from there, the answer is February 15, 2006. Reset the calculated value of February 15, 2006 to February 1, 2006. If the date was February 16, 2006 (46 days after start), set the date to March 1, 2006. Th...

Date conditioning format problem
I am using excel 2000. with my cursor in A1, I select cell A1 to A20 which is already in date format, and do a conditioning format(highlight) using IsFormula: =$A$1=TODAY() Unfortunately, all the cells from A1 to A20 are highlighted!!!!!! no matter the dates is today or otherwise!!!!!!!! In fact all the cells are having the same conditioning format which is =$A$1=TODAY(). How to make it suvch that cell AX is having =$A$X=TODAY()conditioning format??? Hi Take the Absolute off the row Reference =$A1=TODAY() Regards Roger Govier pfnus@hotmail.com wrote: > I am using excel 2000. >...

Date numbering out of order in Outlook 07
Hi guys, I am a function organiser and within Outlook I have mail files for each event I am running. So for example, I would have a mail folder set up that looks like this: 12 December 1 Dec Pharmacy Guild 6 Dec 15 Dec Event Central 25 Dec Christmas Day But what I find is that Outlook puts things in a different order than I would expect and so I have ended up with this: [IMG]http://i14.tinypic.com/6kz8umq.jpg[/IMG] Is there a work around (other than renaming every sub-folder a-z)? Thanks in advance for help on sorting in order, Jo Actually, none of your sub-folders a...

DTS Export fails on bad date
While trying to import an excel spreadsheet to a SQL Server table, DTS fails, pointing to a column with date values. I have looked at all the date values, and they appear correct. I need to get this excel spreadsheet loaded to a database table. Is there any way to pinpoint the exact cell causing the problem? Or, can you think of another way to export the data in the spreadsheet besides DTS that might not be so sensitive to data content? Thanks, Dean Slindee ...

How do I change the US date setting in the event schedule planner
Help! I'm using a lovely little template called event schedule planner - however it shows the date in US date format and I need the UK format (dd.mm.yyyy) how do I change it in this template please? If this is Word 2007/2010 then the chances are that it uses date content controls to display the dates. You can change them all with a macro Sub Macro1() Dim oCC As ContentControl For Each oCC In ActiveDocument.ContentControls If oCC.Type = wdContentControlDate Then oCC.DateDisplayLocale = wdEnglishUK oCC.DateDisplayFormat = "dd.MM.yyyy" End ...

How do I calculate escalation rates using dates and a set value
If I have a projected that needs to be escalated using start and completion dates by a certain percentage per year, how do I write the formula. For example if my project starts May 26, 2010 and lasts until may 26 2014 I want to add escalation to midpoint at 4% per year. ...

How can i use a command button to validate date and time
I have two command buttons, each on a different sheet. i need both of them to put the current date in one cell on its sheet and current time in another on its sheet. i also need these cells to be locked so the user can only edit these cells by clicking the command button. Any answers? ...

comment box with date
Is there a way to have a comment automatically open with the days date next to the user name? You could use a simple macro like this and add it to a button on a toolbar: Sub InsertComment() With ActiveCell .AddComment .Comment.Text Text:="name" & Chr(10) & _ Format(Now, "mm/dd/yy") & Chr(10) .Comment.Visible = True End With End Sub HTH Jason Atlanta, GA >-----Original Message----- >Is there a way to have a comment automatically open with >the days date next to the user name? >. > ...

disc date for next month
We want to be able to choose a discount date for the next month the discount is allowed. Our terms are 2% 10th net 15th. We can choose the due date for the 15th of the next month, but we can't choose the discount date as the 10th of the next month. All the invoices entered into the system from the 1st thru the 10th calculate the discount for the current month instead of the next month. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the me...

Formaula for less than date and name begins with...!!! HELP!!!
IM trying to create a formula that gives me a total count for cells that are less than 02/05/10 and the name begins with FHLM, heres what i came up with... =COUNTIF(E:E,">="&DATE(1900,1,1))-COUNTIF(E:E,">="&DATE(2010,2,5))+COUNTIF(E:E,DATE(2010,2,5))-COUNTIF(U:U,"FHLM*") I keep gettin a bunch of ###### or 0, depending on how I move the data around.. Any ideas???? =SUMPRODUCT(--(E1:E100<=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM")) "Senor Martinez" wrote: > IM trying to create a formula that gives me...

Is there a way to copy and paste the date into worksheets
Is there a way to copy and paste dates into the worksheet withou individually typing up each one -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2450 View this thread: http://www.excelforum.com/showthread.php?threadid=38137 Is there a way to insert it the date into the the worksheet tab (not th cells in the actual worksheet but the name of the worksheet itself -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: ht...

The ability to change multiple activity due dates at once
I would find it extremely helpful when I have multiple activities that are due on the same date and I want to change the due date to be able to change them all at once rather than having to open each one separately and then changing the due date. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree&...

Add time to a date time group
Hi, thanks for any help in advance I have my date and time displayed in this format dd hhmm "K" mmm yy which shows up as 03 1800 K Sep 08. I would like a formula that will add 30 min to this time group so it becomes 03 1830 K Sep 08 Assume your date/time is in A1, then you can do this in another cell: =3DA1+30/24/60 Format the cell in the same way as A1 (using the Format Painter icon). Times are stored in Excel as fractions of a 24-hour day, hence the divisions by 24 and 60. Hope this helps. Pete On Sep 4, 12:28=A0am, Patrick <Patr...@discussions.microsoft.com> wr...

Dating 6 events for 5 groups on a 18 month cycle
I have been trying tocalendarise 6 key dates in a cycle for 5 groups Each group A,B,and C all have different start dates to their cycle in the 18 month period - after that time it all repeats itself. Is there anyway this can be done I also need to find some way of labelling these key dates - Thanks no Denise - Despite Colin's certainty that it can't be done, I suspect all it requires is a better statement of your problem. Are you talking about a Gantt chart? If so, check out some of the links on this web page: http://peltiertech.com/Excel/Charts/GanttLinks.html - Jon ------- ...

Pivot Table Report: Getting average of difference of two date columns
Hello I have data in excel sheet as: WORK_ID PROJECT START_DT END_DT 1001 P1 1/1/2006 1/10/2006 1002 P1 1/25/2006 2/20/2006 1003 P1 3/15/2006 3/20/2006 I want to have a report that will give me average time taken to finish a work in a project. That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report ...

combine text & date formula to show mmmm dd, yyyy?
How do I combine text and date formula to show the written date and not the Excel number date? I have text in a couple of cells and a date formula in another cell(input from a different worksheet). I have the formula =A3 & F3 & A4 in A1 and would like it to read: We went to the station on December 21, 2009 and took the train. However, it is reading We went to the station on 40168 and took the train. Thanks. Try something like this... A1 = Today is B1 = 12/21/2009 =A1&" "&TEXT(B1,"mmmm dd, yyyy") Returns: Today is December 21, 2009 ...

how can i format a date that uses "1st" or "2nd" or "3rd" or 4th".
how can i format a date that uses "1st" or "2nd" or "3rd" or 4th"... i am working on a program that generates certificates. i want to use dates such as "3rd of April 2010". when i run my program the words that appears onthe screen was 3-Apr-10. can you help me solve this problem? On Mon, 5 Apr 2010 20:47:01 -0700, cristin wrote: > how can i format a date that uses "1st" or "2nd" or "3rd" or 4th"... i am > working on a program that generates certificates. i want to use dates such > as &q...

Date expression in Query
I am getting 'undefined function' w/this expression in my qry. Expr1: IIf(today()<[PoP_End_Dt],"cur","exp") How do I insert or use todays date in a Query. Expr1: IIf(Date()<[PoP_End_Dt],"cur","exp") -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Steve Stad" wrote: > I am getting 'undefined function' w/this expression in my qry. > Expr1: IIf(today()<[PoP_End_Dt],"cur","exp") > > How do I insert or use to...

Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table. Excel (07) is not recognizing the field as a date. This is a field I would like to group by in the pivottable. I am aware of all the techniques to convert this to a date field, but I am searching for an answer as to why XL pivot tables cannot consume the dates directly from a sql query. I have played with bringing the dates back in a number of differenent formats with no success. Any insights into this would be appreciated. Probably coming in as text and yuo may only need to copy an unused cell and paste sp...

Make A Set Of Date
i have 2 sheets FOGLIO1 e FOGLIO2 in FOGLIO1 cell D2 i have the value 60 in the cell J2 i have a date, for example 27/11/2004, i colud want fill a set of date in FOGLIO2 to start in the cell G2 for every 60 month day year, for example: sheet FOGLIO2 in the cell G2 27/11/2004 in the cell G3 27/12/2004 in the cell G4 27/01/2005 in the cell G5 27/02/2005 .... in the cell BN2 27/10/2009 this macro for every data present in the column D2:D2000 and in the column J2:J2000 from FOGLIO1 when the actual date is the same in the cell G2 of sheet FOGLIO2 delete the cell G2 and skip to left the other c...

Date cells after conversion from Database
In our organisation we are dealing with different date formats (dd/mm/yyyy or mm/dd/yy) When information is requested out of our JDE database it is presented as a csv file. After convertion into Excel the 'dd/mm/yyyy' are recognised as date formats, but the 'mm/dd/yy' only as general formats. How do I change those last ones in recogniseable date formats? Thanks for your help!!!! Rene. if you can highlight them all then right click and choose format cells then under the number tab choose custom then enter mm/dd/yy in the to box. that might help cheer -- Message po...

Last Saved Date #3
Excel 2003 In Word 2003 it is possible to insert a field that will show the date and time that the document was last saved. I have been trying to find something similar for Excel that I can insert as part of a custom footer, or at least in a cell somewhere on the page. How can I get the date and time of the last save to show in an Excel file? --Tom To place in a footer.................. Sub foot_date() ActiveSheet.PageSetup.RightFooter = "Last Saved : " & _ Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), _ "yyyy-...

how do i change dates on my calendar and keep my pics
Publisher doesn't support this. You would need to do it manually. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no warranties, and confers no rights. "Bob T" <Bob T@discussions.microsoft.com> wrote in message news:68671977-F71B-444F-AE72-801D2B359FEE@microsoft.com... > ...

Calendar date range in Publisher 2007
Just changed from Publisher 2003 to 2007, pulled up existing calendar Aug 08, changed date range to Aug 09, and it shows Aug 1st on Friday, not Saturday! How do I fix this? -- assistantclerk ...

SUMIF when YEAR is correct
Hello folks, I have the following table: A B C D E 1 Jan 01 Feb 01 Jan 02 Feb 02 2 Costs 50E 50E 60E 60E 3 Cashflows 100E 20E 100E And I want to do the following: The values in the row 2 should be calculated automatically. - Excel should look in row 1 and read the YEARS. - Then excel should go in row 3 and SUM all values of the same YEAR, e.g. of year 02 (which would be 120E) -Then excel should devide this SUM in equal parts and spread it over the cell...