#### Calculating date difference

```Hi

I need to calculate the number of years of service for
person(difference between Start_Date & End_Date). The result should b
in terms of years, months and days (all in one single cell). Ca

Thanks

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

```
 0
rll.xjm6a (1)
11/27/2003 8:03:02 AM
excel.misc 78881 articles. 5 followers.

3 Replies
189 Views

Similar Articles

[PageSpeed] 53

```Have a look at Chip's site:

http://www.cpearson.com/excel/datedif.htm

Andy.

"rll" <rll.xjm6a@excelforum-nospam.com> wrote in message
news:rll.xjm6a@excelforum-nospam.com...
>
> Hi
>
> I need to calculate the number of years of service for a
> person(difference between Start_Date & End_Date). The result should be
> in terms of years, months and days (all in one single cell). Can
>
> Thanks!
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>

```
 0
andy1646 (82)
11/27/2003 8:52:29 AM
```"rll" <rll.xjm6a@excelforum-nospam.com> wrote in message
news:rll.xjm6a@excelforum-nospam.com...
>
> Hi
>
> I need to calculate the number of years of service for a
> person(difference between Start_Date & End_Date). The result should be
> in terms of years, months and days (all in one single cell). Can
>
> Thanks!

The problem here is that months are not all the same length. Presumably you
would consider the difference between 1st Feb 2003 and 1st March 2003 as one
month. However, what about the difference between 1st Jan 2003 and 29th Jan
2003? This is the same period (28 days), but not a complete month. If you
can define exactly what you want in all such circumstances, perhaps someone
will have a chance of helping you achieve it.

```
 0
Paul
11/27/2003 9:15:30 AM
```Give this a try:

=DATEDIF(A47,NOW(),"y")&" years, "&DATEDIF(A47,NOW(),"ym")&" months,
"&DATEDIF(A47,NOW(),"md")&" days"

This returns difference between date in A47 and "today" and will
change as the calendar day changes.
If you prefer you can substitute a 2nd cell ref with end_date for
"NOW()"

> Hi
>
> I need to calculate the number of years of service for a
> person(difference between Start_Date & End_Date). The result should be
> in terms of years, months and days (all in one single cell). Can
>
> Thanks!
```
 0
tworsrus (23)
11/27/2003 10:57:43 PM

Similar Artilces:

Can I change the calendar year beginning date?
Our fiscal year runs July 1 thru June 30 of each year. I need to track attendance, gas cards, etc for monthly reports. I have a HUGE table with a gazillion queries and reports for each one. I need one report to reflect quarterly output for our fiscal year as stated above. Can I make this report do that? Thank you! Create a query to use as the source for your report. In query design, type this into the Field row: FinYear: DateAdd("m", -6, [InvoiceDate]) replacing InvoiceDate with the name of your date field. This yields 2007 for all dates in the 2007/2008 financial year...

Date Formatting when Concantenating
I have a simple question. I have a cell that has date that looks lik this: 10/15/1999 14:34 When I use the concantenate feature my date looks like this: 36448.6073611111 I tried to format the call every which way - but I cannot get it t look the original. Feeling really silly for even asking.. thanks all for your help -- Message posted from http://www.ExcelForum.com Hi bleu808! Use: ="Today is "&TEXT(TODAY(),"mm/dd/yyyy hh:mm") -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "bleu808 >" <<bleu808.189yij...

Show a date 30 days out
I have a feild that I would like to show what the date would be in 30 days for a feild with a date in it [Sold Date]. Sold Date feild would be 1 Jan 07 so the feild would show 30 Days later 31 Jan 07. I did =[Sold date]+30 and it did not work Thanks how about dateadd: dateadd("d", 30, [Sold Date]) -- steve. "KAnoe" <KAnoe@discussions.microsoft.com> wrote in message news:DE9BDDAE-5C84-4A52-8185-DAB6C3167E7A@microsoft.com... >I have a feild that I would like to show what the date would be in 30 days > for a feild with a date in it [Sold Date]. > ...

How OMPM Scanner (offscan) Filter by Access/Modified Date ?
Hello, I have problem to inventory excel files on very big file server, but I believe there are so many documents we no longer need to maintain. I want to skip files if the access date or modified date longer than 6 month, but don't see the OMPM providing feature about it. I currently running OMPM since 2 weeks ago and running out of time for reporting to my manager. Please help me, this is my critical assignment. -- Eldi Munggaran ...

Dates in fomula showing as whole number
I have a fomula in a cell that takes the name of a person (from cell 2B), their License number (from another cell 2C)and the Date that License Expires (From cell 2D). The expire date in "2D" is either the word "none" or a date that that persons license needs to be renewed. Those instructors with "None" come out in the calculated field fine, however the ones with dates come back as whole numbers, Example 8/6/10 shows 40396. any help will be appreciated Hi, You need to change the format of that cell or column, highlight the cell or the column, right click o...

1 Chart
I presently have an XY line chart showing asset price over time. Pretty simple. X Axis - Time Scale Y Axis - Asset Price I would now like to add an additoinal series showing the volume of assets traded, ideally this would be as a bar chart sitting "underneath" the asset price on the chart. They would share the same X Axis. I have added another series, but this simply displays the volume traded as another line, and even when this is set to a secondary axis the scaling makes this unworkable. i have adjusted the scales of both, still this does not make it workable, i want the series...

Line Chart with dates in 5 day working week only
Hi, Trying to format a chart so that only the 5 working days of the week are displayed on the x axis. The source data only has the five days (e.g. 05/09/2005 down to 09/09/2005 and then on to 12/09/2005 down to 16/09/2005 etc etc) So I have missed out the weekend dates. When I create the line chart however, the weekend dates appear automatically and just show no point on the chart, therefore there is a longer line between Fridays and Mondays!! Hope this makes sense. Does anyone have any ideas on how to change this? I have tried looking at Tools-options-chart and cannot seem to turn...

Percentage difference calc that knows the largest figure
I have a calculation in cell A3 which looks at the content of Cell A1 and Cell A2 and then works out the difference between both as a percentage For instance Cell A1 = 100 Cell A2 = 10 Cell A3 returns the difference as being 90% My simple calculation in Cell A3 is as follows =(A1-A2)/A1 Cell A3 is formatted to give the answer as a percentage to 2 decimal places The above works fine as long as the number in Cell A1 is greater than the number in Cell A2 If the number in A2 is greater than the number in A1 then my calculated answer is incorrect For instance Cell A1 = 10...

How to get only the year in the date format in Access
How to get only the year in the date format I.e in the table in need to display only year E.g 2005 - should be display " 05" automatically Custom format the cell as: yy -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "yanu" <yanu@discussions.microsoft.com> wrote in message news:14CE9F60-F7B9-467A-8C16-71088C31BEBA@microsoft.com... > How to get only the year in the date form...

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

date/time formatting
I download a csv report from a web based program. Everything works great but the date comes through as "Jul 21 2009 1:51pm". I do not need the time in my report. I have tried reformatting the cells, tried to copy to a new file with the cells already formatted to "7/21/09", opening the cell format using the F2 key and deleting the time (time will still not be formatted correctly). The only way I get get just the time is to retype every cell. What am I doing wrong? I am using Excel 2002 SP3 TIA, Cindy When data is downloaded from the web, a lot of "other&quo...

Calculating Correlation using arrays
I have a sheet full of data for many products in chronological order. Column A is Date of manufacture Column B is time of manufacture Column C is the product Column D is a measurement from the automatic control system Column E contains occasional manual measurements for calibratio checks Up till now I have split the data out by grade and checked calibratio using pivot tables and also checked slope and offsets. After gainin exposure to all kinds of clever functions via this board I now wonde whether it would not be possible to automate these checks in some way ie on a separate sheet I list t...

Calculate Subreport totals in a main report Group footer
Hi all Apologies if this has been answered before but I can’t find it. I have a main Report with a Group called “Product_Category” which lists a number of “Products” in the Detail I have a Subreport named “product_costs” which has a record for each date and Text Boxes named “materials” and “fuel” (there are more but I’ll keep it simple). The Subreport sums all costs and has Text Boxes named “summaterials” and “sumfuel” in the footer (all with a height of 0.1cm) The Subreport is embedded in the Detail of the Categories and linked by Product_id In the Detail of the Main Rep...

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

Users cannot login to OWA after Exchange 2K3 was moved into different OU
I created a new OU named member servers, and moved the exchange 2k3 server into it. After I moved it, only the administrator was able to login to OWA successfully. No users could login to OWA. I created a test account with a mailbox, and was able to login to OWA with the newly created test account. I then moved the exchange 2k3 server from the member servers OU into the computers OU, and the newly created account was unable to login to OWA. Any help with this would be appreciated. ...

Moving certain data to different sheet
I need to move data that meets a certain criteria, to another sheet within a workbook. For instance, if a column of data is for a certain ZIP code area, I need it to automatically copy to a sheet for that city. Say, 40202 would go to the Louisville, KY sheet. Because Louisville has multiple ZIPs, I would need only the data that begins with 402 to go to that sheet. Lexington KY's data, which begins with ZIP code 405, would go to its own sheet. Macro? Formula? Thanks! This can definitely not be created with a formula. I suggest that you make use of the macros. Rgrds, Kris...

Calculating dates #3
Can anyone tell me what I should use (in the way of helper cells) to take any date (mm/dd/yyyy) and turn it into that same month and day for specific year? For instance, turn... 10/12/2009 into 10/12/2010 and 4/6/1998 into 4/6/2010 I'm trying to determine anniversary date based on start date and do it starting in 2010. TIA -- Jordon Try this: =3DDATE(2010,MONTH(A1),DAY(A1)) Assuming your date is in A1. Hope this helps. Pete On Jan 5, 5:35=A0pm, Jordon <jordon@REMOVE~THISmyrealbox.com> wrote: > Can anyone tell me what I should use (in the way of helper cells) > to tak...

how do I format cells to change date and time to just date
I want to format a column that contains date and time and I want it to show just the date and not the time. Going into format and clicking on the date and changing doesnt work. Probably the cells aren't really dates, but text. You can check with the ISTEXT() function. If they are text, formatting doesn't have any effect. -- Kind regards, Niek Otten Microsoft MVP - Excel "bondam" <bondam@discussions.microsoft.com> wrote in message news:F4D61EC1-30E2-4723-89E1-F47B545818EE@microsoft.com... >I want to format a column that contains date and time and I want it t...

Are there MFC subclasses that give a different "look and feel" to the UI?
I was wondering if there are free implementations of MFC subclasses of windows and controls that give you a different look and feel? I like the Windows 2000 look, but it's quite a bit dated now, and the XP look is hideous for my taste, so I'm looking for something new, more modern looking, sleek. Thanks in advance for any suggestions. Franco See if this has something your looking for. http://www.prof-uis.com/ "Franco" <cuminato@yahoo.com> wrote in message news:1149800640.987272.304540@c74g2000cwc.googlegroups.com... >I was wondering if there are free implementa...

What is Compatibility Mode format? How its different from others
What are the advantages/disadvantages of Compatibility Mode? Which other format mode can be used? Compatibility Mode uses only features that are available in Word 97-2003. It is suitable for most purposes and will be used for any document that is saved in .doc format, rather than the Word 2007 docx or docm formats. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "author2010" <author2010@discussions.microsoft.com&...

Calculate age as of a date certain
Employee benefit enrollment requires the age of each employee as of a specific date, such as 1/1/2005. Given the date of birth, how would this be calculated? -- Joe S. Hi see: http://www.cpearson.com/excel/datedif.htm#Age -- Regards Frank Kabel Frankfurt, Germany "Joe S." <JoeS@discussions.microsoft.com> schrieb im Newsbeitrag news:0EC4F9E5-2E60-448A-A107-7B085BC764C5@microsoft.com... > Employee benefit enrollment requires the age of each employee as of a > specific date, such as 1/1/2005. Given the date of birth, how would this be > calculated? > -- > Joe ...

Conditional formatting a date range
If I have a column of dates that are manual entered what is the formula to conditionally format them based on a date range of three months before the current date to the current date and another three months after the current date to the current date? Assume the dates are in column A, starting with A1. Highlight all the dates, with A1 as the active cell, and click on Format | Conditional Formatting. In the dialogue box you should select Formula Is rather than Cell Value Is and then enter this formula: =3DAND(A1>=3DTODAY()-91,A1<=3DTODAY()) Click on the Format button and choose the ...

What are the different methods of representing numerical data?
Can someone answer my questions "What are the different methods of representing numerical data"? "Excel Problem" wrote: > Can someone answer my questions "What are the > different methods of representing numerical data"? Sounds like a test question. What's the context? Is it asking about numerical formats? Or is it asking about date v. time v. other numerical data? (Which really is not "different methods" per se. All are floating-point aka real numbers. But the test might be asking for the specifics of date and time rep...

Date/Time stamp in Memo Field
Hi all..is it possible to programmatically insert Now() when text in a Memo field becomes edited? Thanks for all help! If it has to be entered directly in the memo field tetxbox: Private Sub MemoFieldName_AfterUpdate() If IsNull(Me.MemoFieldName.OldValue) Then Me.MemoFieldName = Now & " " & Me.MemoFieldName Else Me.MemoFieldName = Left(Me.MemoFieldName, Len(Me.MemoFieldName.OldValue)) & " " & Now & " " & Right(Me.MemoFieldName, Len(Me.MemoFieldName) - Len(Me. MemoFieldName.OldValue)) End If Me.Dirty = False End...

Add Actual End Date to Resolved Cases view
Hi We would like to add the actual end date to the Case General Tab or possibly to the Resolved Cases and/or My Resolved Cases view. I know that the Actual End Date is available on the Service Activity and Case Resolution but this field is not available for the Case. Any workaround for this? Thanks Mark ...