Summary of Difference between dates in years, months, days

I need to calculate the difference between 2 dates and then total them.
Here's what I have so far:

From                              To                          Length of 
Service
01/09/2003                     31/01/2010              6y 4m 30d
01/06/2000                     30/11/2002              2y 5m 29d
Total of Service:                                             ??????????

I've used the following formula to calculate the total days worked:
=DATEDIF(A4,B4,"Y")&"y "&DATEDIF(A4,B4,"ym")&"m "&DATEDIF(A4,B4,"md")&"d"

Problem is I don't know how to add the two together to get the total length 
of service.  

Thanks
1
Utf
4/17/2010 12:01:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1134 Views

Similar Articles

[PageSpeed] 33

=DATEDIF(1,B2-A2+B3-A3,"y")&"y "&DATEDIF(1,B2-A2+B3-A3,"ym")&"m 
"&DATEDIF(1,B2-A2+B3-A3,"md")&"d"

Depending on how you define length you may have to add 1 to the date 
difference (or to one of the dates)
Of course the formula can be shortened by using an intermediate cell for 
B2-A2+B3-B2

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"mardoh" <mardoh@discussions.microsoft.com> wrote in message 
news:5DD7448B-1B9E-470E-857D-03628A82B4FC@microsoft.com...
>I need to calculate the difference between 2 dates and then total them.
> Here's what I have so far:
>
> From                              To                          Length of
> Service
> 01/09/2003                     31/01/2010              6y 4m 30d
> 01/06/2000                     30/11/2002              2y 5m 29d
> Total of Service:                                             ??????????
>
> I've used the following formula to calculate the total days worked:
> =DATEDIF(A4,B4,"Y")&"y "&DATEDIF(A4,B4,"ym")&"m "&DATEDIF(A4,B4,"md")&"d"
>
> Problem is I don't know how to add the two together to get the total 
> length
> of service.
>
> Thanks 

0
Niek
4/17/2010 12:45:57 PM
And of course the whole thing gets a bit doubtful as months have different 
lengths, and if you haven't got one unique starting date you don't really 
know how many days to include in a month, but Niek's idea is about as good 
as you can do.
--
David Biddulph


"Niek Otten" <nicolaus@xs4all.nl> wrote in message 
news:0B637316-27F8-4CE6-A48C-3E43451152DC@microsoft.com...
> =DATEDIF(1,B2-A2+B3-A3,"y")&"y "&DATEDIF(1,B2-A2+B3-A3,"ym")&"m 
> "&DATEDIF(1,B2-A2+B3-A3,"md")&"d"
>
> Depending on how you define length you may have to add 1 to the date 
> difference (or to one of the dates)
> Of course the formula can be shortened by using an intermediate cell for 
> B2-A2+B3-B2
>
> -- 
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "mardoh" <mardoh@discussions.microsoft.com> wrote in message 
> news:5DD7448B-1B9E-470E-857D-03628A82B4FC@microsoft.com...
>>I need to calculate the difference between 2 dates and then total them.
>> Here's what I have so far:
>>
>> From                              To                          Length of
>> Service
>> 01/09/2003                     31/01/2010              6y 4m 30d
>> 01/06/2000                     30/11/2002              2y 5m 29d
>> Total of Service:                                             ??????????
>>
>> I've used the following formula to calculate the total days worked:
>> =DATEDIF(A4,B4,"Y")&"y "&DATEDIF(A4,B4,"ym")&"m "&DATEDIF(A4,B4,"md")&"d"
>>
>> Problem is I don't know how to add the two together to get the total 
>> length
>> of service.
>>
>> Thanks
> 

0
David
4/17/2010 12:59:10 PM
Much appreciated Niek.
Thank you.

"Niek Otten" wrote:

> =DATEDIF(1,B2-A2+B3-A3,"y")&"y "&DATEDIF(1,B2-A2+B3-A3,"ym")&"m 
> "&DATEDIF(1,B2-A2+B3-A3,"md")&"d"
> 
> Depending on how you define length you may have to add 1 to the date 
> difference (or to one of the dates)
> Of course the formula can be shortened by using an intermediate cell for 
> B2-A2+B3-B2
> 
> -- 
> Kind regards,
> 
> Niek Otten
> Microsoft MVP - Excel
> 
> "mardoh" <mardoh@discussions.microsoft.com> wrote in message 
> news:5DD7448B-1B9E-470E-857D-03628A82B4FC@microsoft.com...
> >I need to calculate the difference between 2 dates and then total them.
> > Here's what I have so far:
> >
> > From                              To                          Length of
> > Service
> > 01/09/2003                     31/01/2010              6y 4m 30d
> > 01/06/2000                     30/11/2002              2y 5m 29d
> > Total of Service:                                             ??????????
> >
> > I've used the following formula to calculate the total days worked:
> > =DATEDIF(A4,B4,"Y")&"y "&DATEDIF(A4,B4,"ym")&"m "&DATEDIF(A4,B4,"md")&"d"
> >
> > Problem is I don't know how to add the two together to get the total 
> > length
> > of service.
> >
> > Thanks 
> 
0
Utf
4/17/2010 1:41:01 PM
Glad it helps you. But do keep David's warnings in mind!

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"mardoh" <mardoh@discussions.microsoft.com> wrote in message 
news:1578064E-D178-45FB-85D9-DD84787FFBDD@microsoft.com...
> Much appreciated Niek.
> Thank you.
>
> "Niek Otten" wrote:
>
>> =DATEDIF(1,B2-A2+B3-A3,"y")&"y "&DATEDIF(1,B2-A2+B3-A3,"ym")&"m
>> "&DATEDIF(1,B2-A2+B3-A3,"md")&"d"
>>
>> Depending on how you define length you may have to add 1 to the date
>> difference (or to one of the dates)
>> Of course the formula can be shortened by using an intermediate cell for
>> B2-A2+B3-B2
>>
>> -- 
>> Kind regards,
>>
>> Niek Otten
>> Microsoft MVP - Excel
>>
>> "mardoh" <mardoh@discussions.microsoft.com> wrote in message
>> news:5DD7448B-1B9E-470E-857D-03628A82B4FC@microsoft.com...
>> >I need to calculate the difference between 2 dates and then total them.
>> > Here's what I have so far:
>> >
>> > From                              To                          Length of
>> > Service
>> > 01/09/2003                     31/01/2010              6y 4m 30d
>> > 01/06/2000                     30/11/2002              2y 5m 29d
>> > Total of Service: 
>> > ??????????
>> >
>> > I've used the following formula to calculate the total days worked:
>> > =DATEDIF(A4,B4,"Y")&"y "&DATEDIF(A4,B4,"ym")&"m 
>> > "&DATEDIF(A4,B4,"md")&"d"
>> >
>> > Problem is I don't know how to add the two together to get the total
>> > length
>> > of service.
>> >
>> > Thanks
>> 

0
Niek
4/17/2010 1:46:28 PM
Reply:

Similar Artilces:

date taken for photos copied
date taken for photo copied, actually modified date on any documents copied in explorer tends to changed to the Now (date and time of copying). especially annoying for folders anyway to change this default behaviour? On 5/25/10, GS posted: > date taken for photo copied, actually modified date on any documents copied > in explorer tends to changed to the Now (date and time of copying). > especially annoying for folders > anyway to change this default behaviour? Use the value in the metadata (tags)? -- Gene E. Bloch (Stumbling Bloch) anyway to cha...

Differences between MFC, Win32 API and .NET?
Hello, I am new to windows development. I was curious if someone could give me a brief summary of each of the topics? .NET, MFC and Win32API. Also, someone told me that not many people use the MFC anymore, and everyone programs with .NET. is that true? My two cents: Win32 API is the interface (functions) that Windows exposes so you can create your applications (Application Programming Interface). This was the way to program apps back in the day with C. To make programming easier, Microsoft developed MFC (Microsoft Foundation Classes) to make Windows programming easier, and it worked...

Conditional formatting: How to set condition "formula" with is "date" formatted
I want to use Conditional Formatting. I opt for "formula". The condition should be that a cell F9 that is formatted to Date 14-Mar-07 (see cell format) need to be filled with a date (any date). If empty then FALSE. What is the formulah I need to fill in. DATE(F9<>0) failed for me. This is the condition that is set for cell H9. Thank you. Bart Excel 2003 =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0) -- --- HTH Bob (there's no email, no snail mail, but somewhere sho...

Why is printed page different than print preview?
I have a newsletter that I mail merge with an Excel file. Last month it printed fine but this month several things change when I print. I previewed one of the merged newsletters and it still looks fine. But when I actually print SOME of my indentations have increased causing me to lose wording. Since everything looks fine in Publisher program I don't know what to do to correct, changing spacing won't help. Any suggestions? Microsoft Office Publisher 2003 and older are critical about printer drivers. Usually upgrading (or downgrading in some cases) will fix the problems you ar...

setting notice to group (large company, recipients in different cities)
I'm sorry for the multiple groups. I don't understand Outlook well enough to narrow down my groups to just the right one or two. Here's my situation and my question -- there are certain deadlines and tasks that are due on a monthly basis, all revolving around certain business days (related to closing the corporate books). Presently these deadlines are on an excel based calendar located on the intranet, and a monthly email reminder is sent out restating the deadlines. As you can see it is somewhat awkward to force people to navigate through the intranet and then print out th...

choice from a drop down list returns a result to a different cell
in the drop-down box when you make a selection such as "repair" "customer service" or "rental" I want it to return contact info that would be different for each possible choice but to display in the adjacent cell. Seems that that this should be easier than it is turning out to be. Any suggestions??? Use a lookup formula in the cell with contact information, so it looks up the appropriate value from a table based on the selection in the dropdown. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - ...

annualize sales for remainder of year
I have a figure for YTD Sales and am trying to calculate estimated annualized sales off of this. And, this will change monthly as sales managers update their YTD sales to actual for the prior month end. Mathematically, the formula would be: YTD Sales + ((YTD Sales/# whole months passed) (# whole months remaining in year)). Any suggestions? You have not given us any clue as to how we know the number of month's of data that has been entered. a) if this is based on the current date; let N moths have passed then your formula =ytd +(ytd/N)*(12-N) which is the same as 12*y...

Save in different folder
I want to save a workbook in a different folder. The folder already exists, and I want to use the same file name. What is correct code to do this? Try code like the following: Sub AAA() Dim NewPath As String NewPath = "D:\Temp\" & ThisWorkbook.Name ThisWorkbook.SaveAs Filename:=NewPath End Sub Change the "D:\Temp\" to the full folder path to which you want to save the file. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Sat, 22 May 2010 13:2...

different fiscal periods
Set different fiscals period template for dfferent users. I understand that CRM can set the fiscal period but i cant set a different fiscal period for a second user. Coz users have differnet fiscal period quota Please tell me that there is a way around this?? Thanks Regards, ...

Difference in behavior or ProjectPercentCompleted and ProjectPercentWorkCompleted?
I'm sure of the implications, but not sure about how these two attributes actually differ from each other in behavior in project server. Are they basically the same? When would you use one column over the other in the reporting database? Thanks, Andy Novak UNT Andy -- These two fields correspond with the Percent Complete field and the Percent Work Complete fields you see in Microsoft Project. Percent Complete refers to the percentage of the Duration completed for a task or for the project, and is calculated using the formula: Percent Complete = Actual Duration/D...

Date() Not Working in 2007
Anyone know why Date() does not work in 2007 Access and what has replaced it? Thanks! I don't have ac2007 but I suspect Date() works as it always has. Do you have any reference problems? http://www.accessmvp.com/djsteele/AccessReferenceErrors.html Does it throw an error? DSmith wrote: >Anyone know why Date() does not work in 2007 Access and what has replaced >it? Thanks! -- HTH - RuralGuy (RG for short) acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1 ...

Date and Timestamp in Filename
I have a spreadsheet that is modified several times a day and by several people. Is it possible to somehow insert the date and timestamp (perhaps with a macro?) into the filename? It is very difficult to get users to use abbreviations/version numbers etc to show when the last modified spreadsheet was used - many "forget". If there was a way that automatically put the date and time into the filename then users will know which is the most recent document to use - and not have any excuses! many thanks peeps! Emily, Pretty easy ActiveWorkbook.SaveAs Filename:=sFilename &...

X axis collapsing to date on hourly data
Hello, I have a spreadsheet I am trying to chart that has values by date and time, IE Date-Time, Value 9/17/07 20:00, 1 9/17/07 21:00, 2 9/17/07 22:00, 4 9/17/07 23:00, 1 9/18/07 00:00, 2 9/18/07 01:00, 1 9/18/07 02:00, 0 When I highlight all of the above and select "Insert Chart" and choose line chart, it creates the chart as if it had the following information: Date-Time, Value 9/17/07, 8 9/18/07, 3 It also looks more like a bar chart then a line chart. Anyone have an idea on why this would happen and how to fix it? -Jason I am mistaken, it is collapsing into all 24 series...

One Bank, 2 Accounts, 2 Different logins
Hope this hasn't been posted before. I did a search and did not find it. I have 2 accounts with the same Bank but I have a different login id and pass for both accounts. How can I set it up to download for both accounts? It seems the program is set so that it assumes that if your dealing with one Bank online that you will be able to access all your info though one ID. Thanks in advance for any help. James In microsoft.public.money, JDickens wrote: >Hope this hasn't been posted before. I did a search and >did not find it. > >I have 2 accounts with the same Bank...

Month display to show in the Inbox
Do you know of a way to diplay the month at the top right of the screen while in the Inbox? Thanks Outlook version? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted w/out reading. "DC" <dcspins4u@att.net> wrote in message news:084601c3c324$f1ba4020$a401280a@phx.gbl... | Do you know of a way to diplay the month at the top right | of the screen while in the Inbox? Thanks 2002 >-----Original Message----- >Outlook version? > >-- >Milly St...

How do I sort by month and day
I want to sort multiples dates by day and month, but NOT year. Basically to put all of our birthdays in order for the year......... Let's assume that all your dates are some column, say column B In another column, enter the formula =MONTH(B1) and copy down. In a third column, enter =DAY(B1) and copy down. Then sort the data by month and then by day. -- Gary's Student "CVSTANFORD" wrote: > I want to sort multiples dates by day and month, but NOT year. Basically to > put all of our birthdays in order for the year......... "CVSTANFORD" wrote: > I...

addendum to 'Monthly Budget 2006 Retail', Post
MS MONEY 2006 - RETAIL Every step of the way - an adventure !! Under the ACTUAL TOTAL INCOME figure of the "Monthly Budget Report", there is a greyed out figure of $500 which does not figure in to the Grand Totals of the Budget Report. I found out the source but I don't understand why, or how to get rid of that "ghostly" figure. These are the actual entries I made: ACCOUNTS - CHECKING OTHER INCOME - Credit $527. Tax Refund OTHER INCOME - Debit 500. To Savings Balance in OTHER INCOME ...

emails appearing after 4 years, again! #5
I've recieved an email that was sent to me 4 years ago, in my Inbox. What causes this? The Sender is still working with me and he definitely does not have a copy of the letter. ...

Contacts' Modified Dates Change After Opening
Hello This problem popped up 3 days ago, I don't know what triggered it. After I double click on a contact, to open it, the modified date for this contact is updated to the present date and time, although I did not modify the contact. Right after I open it, its modified date is altered. I want to avoid this as I often use a "sort by modified date" view. Please help me solve this problem Thanks Gz If you open something in OL the modified date is changed, its default/std behaviour. "grendi" <ufoREMOVErobotTHISgrendizerusTO@CONTACTyahoo.MEcom> wrote in messag...

Date in forms
I would like to have a command button that I could use to insert a date in a date field. the date would be for scheduling events at the beginning of each week, hence I would like the dates to be for the beginning of each week. First date could be 04-mar-07 the next record in the date column would be 11-mar-07. Each record would have the beginning of the week date. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200703/1 ...

Differences between Tasks and Reminder/Meetings
Hi, from the moment I started using Outlook I have used the Calendar to store my personal activities (i.e. "backup", a reminder which repeats monthly). However, I read that tasks are more suitable for personal activities. What's the difference between tasks and reminders/meetings? How do you use them? Thanks which version do you have? -- Here are some tips about how to post questions: http://support.microsoft.com/?id=555375 Regards Judy Gleeson MVP Outlook Trainer and Consultant There are various articles about using Outlook here: www.judygleeson.com/articles.aspx Ca...

Auto input days of week
Hi, I'm looking to set up a spreadsheet where users enter new months date in cell A1, I have worked out how to auto input dates down the rest of column A but need to fill in days of week in column B. I'm trying to automate process as not all users proficient with excel. It needs to work for any month. Any help would be much appriciated. -- brom0910 ------------------------------------------------------------------------ brom0910's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27416 View this thread: http://www.excelforum.com/showthread.php?threadid=49...

Allowing only Date and Integer for user form
I have a user form and as part of the procedure when I click OK is the following .... 'Make sure a number is entered If TextName1 = "" Then MsgBox "You must enter a number." Exit Sub End If 'Transfer the number If Range("M5") = True Then Range("O5") = TextName1 If TextName2 = "" Then MsgBox "You must enter a date." Exit Sub End If 'Transfer the date If Range("M5") = True Then Range("P5") = TextName2 1. I would like to make sure that only an integer is...

OUTLOOK mail@msn.com frustrating day
my pop3 password verification on msn.com isn't working today. msn denies there is a problem (email x@msn.com). So I switched from pop3 to http. and my mail is coming in fine. only problem is that when I read the mail each message is automatically moved to the deleted folder and I can't, for the life of me, figure out how to stop that (Newest Outlook with all service pack upgrades. "Old pop3" did not delete message upon read). thanks in advance. This is a multi-part message in MIME format. ------=_NextPart_000_000B_01C46F6D.46528CB0 Content-Type: text/plain; charset=&q...

Difference within HTML/Rich Text/Plain Text
Could anyone explain me the difference between the three mail format (HTML, Rich text and Plain text) and wich do you recommend the best? Thank you Use plain text unless you want to set the format of your text, as in specifying the font, using italics, etc, in which case you'd probably use HTML. Generally, you'll only use rich text if you're on an Exchange server and sending messages to others in the same organization. Having said all that, I admit that I prefer using rich text as it just works better in Outlook 2003. The real answer is use whatever you prefer. -Peter &qu...