Diff between two dates formatted as years and months

I am trying to display an employee's length of service at a particular date 
using a simple formula to subtract one date from the other and format the 
result as y"y" m"m" so that I get eg.  2y 4m as the person's service.

It is returning odd results eg.  0y 12m for diff between 1/1/04 and 1/1/05 
and 1y 1m for diff between 31/12/03 and 1/1/05.

Is there a more accurate method of doing this to ensure that I get the 
result I want?

Thanks for all your help
0
Leyland (5)
10/7/2004 2:55:43 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
436 Views

Similar Articles

[PageSpeed] 11

A complete explanation:

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

-- 

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Sally Leyland" <Sally Leyland@discussions.microsoft.com> wrote in message 
news:6D7A0C64-692B-43E9-AB03-5E6934D0EFCB@microsoft.com...
>I am trying to display an employee's length of service at a particular date
> using a simple formula to subtract one date from the other and format the
> result as y"y" m"m" so that I get eg.  2y 4m as the person's service.
>
> It is returning odd results eg.  0y 12m for diff between 1/1/04 and 1/1/05
> and 1y 1m for diff between 31/12/03 and 1/1/05.
>
> Is there a more accurate method of doing this to ensure that I get the
> result I want?
>
> Thanks for all your help 


0
nicolaus (2022)
10/7/2004 3:13:33 PM
Reply:

Similar Artilces:

Date updating automatically
Using the Invoice template in Excel 2000, how do I prevent the date from updating automatically? I cannot seem to find what I need to do to remove this. It seems to be a macro I think, but what do I need to do? Any suggestions? Sam I reckon it is done with a macro - which is passworded. You can overtype it with any date, however. Andy. "Sam" <anonymous@discussions.microsoft.com> wrote in message news:2b8e901c39322$5c4fca50$a601280a@phx.gbl... > Using the Invoice template in Excel 2000, how do I > prevent the date from updating automatically? I cannot > seem to...

Is there an xml diff test function
Hi; I have a bunch of unit tests that generate xml files (WordML and SpreadsheetML) and then compare those to the "correct" file. This works great except that the java code I use to generate the xml (dom4j) differs from XmlTextWriter in that dom4j places a space at the end of <name atr='value' /> and XmlTextWriter has no space <name atr='value'/>. They are identical as xml but not as raw text. Is there some method I can use to compare xml? And I do need to be able to ignore 2 nodes that are different - they have the version of the software that cre...

Crystal Report dates
Hi, Our software is RMS and we have Crystal Reports (brand new). We're trying to run a report but the date fields are screwing us up. Help! -- Thanks,Jan Jan, I don't have much to go on with your post, but RMS is very particular to the version of Crystal Reports that it runs internally. CR 8.5 is the default version that *should* work with RMS. Some have said that even then, the def files sometimes need a little tweaking. I, have 8.5 Dev and 11 (XI) Dev, and have used both in different methods, 11 as an externally called VB routine. What do you mean by "screwing us" up?...

Date field behavior differs between forms
I have two forms with seemingly identical date fields. In one, if I place my cursor in the middle of it, the first number I type gets put in the far left of the field. In the field on the other form, if I place the cursor in the middle, it starts typing right where I am. Any ideas? I have thoroughly looked through the properties of each field and each form and I cannot find what is causing this behavior. I would love to get both of them to start placing typed characters at the far left instead of where the cursor is. Thanks! ...

Dates for the whole month
Hi, Team Amount Date ------- --------- ------ Blue 100 01/03/2008 Blue 200 02/03/2008 Green 500 01/03/2008 Green 400 02/03/2008 How can i sum the amounts for each team. So everyday the Amount field will include the amount from the previous date. Basically i want a comulative figure for the month for each Team. Thanks for your help mahmad, If your Date field/column has a date/time data type, then something like this might work: SELECT DateSerial(Y...

Two Outlook Users on XP
We just replaced our old computer with a new Dell. I loaded Office XP and transferred my Outlook 2002 email to the new computer. However, ever since we started using this new one, we are having trouble seeing the email on both "sides" of XP. When I log in, the email downloads to my side, but is not replicated on his side. When he logs in, all of the email downloads to his side and is not seen on my side. We have checked all of the set-up and settings, and have been looking through all support to try to find a clue. It worked before, so I am at a loss as to what the problem ...

Cell Formats #2
I know how to reference the value of a cell but is there a way o reference a cells format? Provide more details about what you wany to achieve, please. There are ways to ascertain the formatting of a cell but generally would require VBA Gord Dibben MS Excel MVP On Sat, 29 Dec 2007 13:21:02 -0600, "Jeff Klein" <jklein@nospam> wrote: >I know how to reference the value of a cell but is there a way o reference a >cells format? > On Sat, 29 Dec 2007 13:21:02 -0600, "Jeff Klein" <jklein@nospam> wrote: >I know how to reference the value of a...

Compare two columns in two different workbooks
I have two workbooks. One named "last.xls" and the other name "new.xls". Both have one sheet each with a SheetName of "Sheet1" Both sheets have the same number of columns (A thru K). Here is what need to do with the data: I want to compare "Column A" in both workbooks. If a value exists i "Column A of last.xls", but does not exist in "Column A of new.xls", want to take that entire row (the one that exists in last.xls, but no in new.xls) and copy it to a new workbook named tnnew.xls. I need t copy all of the rows in "last.xls&...

Monthly Spending termomether
Just newbie in Money 2K4. I was testing and I found the monthly spending thermometer and I thought it could be usefull. I checked in "All Expenses" "tell me when ... over 500 ?" "show thermometer" And I started to insert all my expenses. I have found that in thermometer are not reflected "all expenses". Those on categories: household: funitures (IKEA shopping) and job expenses are not reflected. Does anybody know why and how to solve? Thanks in advance: Eusebio If i recall correctly, the thermometers only show spending in budget accounts, so ...

Two accounts asign to one mailbox (owa)
How to asign two accounts to one mailbox, and can read mail by owa. When i use permision "Send As & Receive As" for users, it works but only in Outlook. There is no way to use OWA to open two mailboxes like you do in Outlook using the Folder List but you can access a mailbox that you have permissions by connecting to it directly. I am actually working on testing this tonight. I will let you know exactly how to do this once I get it figured out myself. Thanks! "Irek" <anonymous@discussions.microsoft.com> wrote in message news:1a84801c44f0b$00eaf810$a601280...

formatting the column/bar of a graph
How do I set the transparency of a bar or column in a graph. Have gone to Format Data Series - Fill effects, but the transparency is greyed out. Method 1: On the Patterns tab Area section choose None Or Method 2 1. Select an empty cell 2. Hold down the Shift key and choose Edit, Copy Picture, OK 3. Select the series on the chart and choose Paste. -- Cheers, Shane Devenshire Microsoft Excel MVP "WitchLady" wrote: > How do I set the transparency of a bar or column in a graph. Have gone to > Format Data Series - Fill effects, but the transparency is greyed out. That s...

how to multiply two columns
Hi, i have two columns (A and B) with 126 datas each. And i want to multiply column A with Column B and like to displayed in column C. pls. help me to do this..thanks. Assuming your data starts in A1, then in C1 enter this formula: =A1*B1 Select C1 again and double-click the fill handle - this is the small black square at the bottom right corner of the cursor, and double-clicking it will cause the formula to be replicated down column C for as many data items as you have in column B. Hope this helps. Pete it really works..thanks again..but finally have to drag the border of the cell i...

List Dates
Hey there, I am currently creating a project in excel for my AS Level ICT exam. I need to create a timetable for appointments. I have a good veiw of how i would like it to work in my head but i just cannot figure out how to actually get it working. I would like for excel to import the dates and days of a month when i enter the name of the month and the year. e.g. 2006 January | 1st SAT | 2nd SUN | ect Any help would be greatly appreciated. Thanks Fanle Hi! Here's one way that gets pretty close: The returned value will look like this: 1 - Sun (January 1 2006 is on Sunday) Create t...

Find value in table based on two inputs
Given A1: Name B1: Dept C1: Age A2: Henry B2: 501 C2: 28 A3: Stan B3: 201 C3: 19 A4: Mary B4: 101 C4: 22 A5: Larry B5: 301 C5: 29 how would write a formula to find the age of 'Mary' in dept '101'? vlookup doesn't seem to support multiple compares. Thanks Hi Try =SUMPRODUCT(--(A2:A5="Mary"),--(B2:B5=102),--(C2:C5)) or better still, put Name required in cell D1 and Dept required in E1 then =SUMPRODUCT(--(A2:A5=D1),--(B2:B5=E1),--(C2:C5)) Change values in D1 and E1 for other selections....

vlookup with two data points.
Ok...I am not sure how to set up this formula. Sheet 1: Column A:Employee ID # B:Effective Dates Sheet 2: Column A:Employee ID # B:Effective Dates C:Change Reasons Example Data: Sheet 1: Employee ID Effective Date Change Reasons 11734 3/23/2009 11734 12/16/2008 11734 12/1/2008 20045 6/5/2008 Sheet 2: Employee ID Effective Date Change Reason 11734 3/23/2009 New Hire 11734 12/1/2008 Manager Change I need to be able to get the 'Change Reasons' in Sheet 1 if the Employee I...

TextBox
Greetings, I have a text box with the default date in ("dd/mm/yyyy") format. The user can then type over this date or leave it alone, and exits the text box by means of the TAB key. Within the exit code all the date validation takes place (See below). If there is a problem the idea is to not allow the user to leave the textbox until it is corrected. The code executes and the error messages are displayed, HOWEVER there is also code which executes when the TAB key is pressed. This code does no validation as is all done within the exit event, the KEYPRESS envent disables the text...

date diff help
Hey All, select datediff(wk,'1/10/2010','12/9/2009') This to me should be 6 instead of 5. I've tried the SET DateFirst but they all resolve to 5. Am I looking at this all wrong? thanks, rodchar scratch this one too. not my day... "rodchar" wrote: > Hey All, > > select datediff(wk,'1/10/2010','12/9/2009') > > This to me should be 6 instead of 5. I've tried the SET DateFirst but they > all resolve to 5. Am I looking at this all wrong? > > thanks, > rodchar How about posting what you fou...

diffs between crm std and crm proffessional suite
there is no information on the differences between the 2 products - but I know that crm std suite doesn't have the contracts mgmt piece - but I don't know what else it doesn't have. We have installed the std suite, and need contract mgmt, so I was wondering what else is missing from the std suite. thanks, Paul Sorensen here is a list of features for sales and server standard and pro ..Microsoft CRM Sales Standard -Lead management -Opportunity management -Correspondence / mail merge -Account & contact management -Notes & attachments -Activity & task managemen...

melting of two and more columns
Hi, I have an Excel chart with several columns of different height. Is there a way to combine these columns to ONE column which represents their average height/value? Thx for any help, Jurgen -- juergenkemeter ------------------------------------------------------------------------ juergenkemeter's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25248 View this thread: http://www.excelforum.com/showthread.php?threadid=387375 Can you be more specific about the data that you have? "juergenkemeter" <juergenkemeter.1s6pac_1121385949.7358@excelforu...

Time formula over two days
I do appreciate everyones help but I have to try and explain the whole problem again so its clear and someone might be able to help, I'm having a hard time getting from my head to the question box, I am trying to create an actual v. planned report in excel: Here is the scenario: Our drivers have a standing appointment at the stores We are considered "Early" if we arrive more than 15 mins before the appointment We are considered "Late" if we arrive more than 30 mins after the appointment So our &q...

Diff Perf Diff User?
Is it possible to set up one id for number crunching, another for quick logon, another for databasing, another for graphics? - = - Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist http://www.panix.com/~vjp2/vasos.htm http://www.facebook.com/vasjpan2 ---{Nothing herein constitutes advice. Everything fully disclaimed.}--- [Homeland Security means private firearms not lazy obstructive guards] [Urb sprawl confounds terror] [Phooey on GUI: Windows for subprime Bimbos] vjp2.at@at.BioStrategist.dot.dot.com wrote: > Is it poss...

Conditionally Format A Line?
Greetings, Access Novice here. I have Line18 formatted as Visible = "NO" in Properties, and I want to make it visible depending on the value of certain fields. Why doesn't this code work when put in the "On Format" or "On Print" events of the Detail section? If [Inventory Posting Group] = "RESALE" Then Line18.Visible = "YES" BTW, I tried "TRUE" in place of "YES", and it still didn't work. TIA. Assuming [Inventory Posting Group] is bound to a control in the report, you should be able to use: ME.L...

STDEV down, but not across, two cols??
I don't understand Standard Deviation very well - I was just told to use this function. Up 'til now, I've only had one column of numbers. Now I have two columns of five data points each. Each set across is one pair (two measurements of the same item). I don't think they want the STDEV between the paired points included in the calculations, only between the individual pairs. So how do I reduce this to only five data points and still get a valid STDEV? Or have I asked an irrational question because I don't know STDEV? ED standard deviation is the parameter for the ...

splitting a field into two fields; one in ()
I have a Name field in my database that is LastName,FirstName(dept). I have figured out how to split last name and first name but I don't know how to leave out the department name that is in () after the first name. Any suggestions? Quick and Dirty way to get the field down to just LastName,Firstname is to use the following as an expression in a query: LastFirst:Left([Name],Instr([Name],"(")-1) This should give you all of the characters to the left of the opening parenthesis. You could then apply your method for splitting the First and Last Names. HTH ...

Posting Date used in Revenue Expense Deferral
I have a PM Invoice with Document Date 16/11/2009 and Posting Date of 01/12/2009. Entered Deferral details starting 01/12/2009. In GL the deferral charges commence 01/12/2009 and are all as expected. The Credit entry for the full invoice is posted to GL with posting date of 01/12/2009. The problem I have is that the Debit entry for the full invoice amount is posted to the document date of 16/11/2009 and I want it to be the same as the invoice posting date (01/12/2009) Is there a way to do this? Thanks Audrey ...