# Thread: Excel formula to save time........ (2000)

1. ## Excel formula to save time........ (2000)

Hi,
I have a rather mishapen excel spreadsheet where some date have been entered and some not.
To fill those spaces, I basically need to find out the length of a time period between 2 dates, ie 14/5/2004 and 5/2013. The first figure always has the day, month and year. The last is the expiry time, and will always need to be displayed as the last date in that month (although this isn't on the database), so in this case it would be 31/5/2013.
I need to split the timeline into months and years..............not too sure how too though.

2. ## Re: Excel formula to save time........ (2000)

With the first date in A1 and the second date in B1, you can use this formula (courtesy of Chip Pearson):

=DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months, " & DATEDIF(A1,B1,"md") & " days"

If you want the individual parts, use

=DATEDIF(A1,B1,"y")
=DATEDIF(A1,B1,"ym")

to get the years and months, respectively.

3. ## Re: Excel formula to save time........ (2000)

Adding to HANS' formula, if B1 has the settlement date entered as 5/2013 (which is the first of the month) and you want to use the last day of the month, then you can change HANS' formula to:

<pre>=DATEDIF(A1,DATE(YEAR(B1),MONTH(B1)+1,0),"y ") & " years, " & DATEDIF(A1,DATE(YEAR(B1),MONTH(B1)+1,0),"ym") & " months, " & DATEDIF(A1,DATE(YEAR(B1),MONTH(B1)+1,0),"md") & " days"
</pre>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•