Reducing Dates 1

[GrandadUK]

I am trying to use an excel spreadsheet to detail contracts in place but with a column to show reducing outstanding period for example;
A2 = Contract commenced
b2 = Period e.g. 3 years
c2 = Formula required for outstanding period expressed in years & days.

Ian

 

[tony813]

Hello IanJE...
Maybe.. a combination of formulas would help....

Try:

="Years:"&(YEAR(A2)-YEAR(A1))&" Days: "&DATEDIF(A1,A2,"d&quot

Example:
A1: Has the start date 1/12/2001
A2: Has the end date 12/2/2002
A3: Has the formula above....

This would result: Years: 1 Days: 689...

Hope this helps..
Tony813

 

[carrr]

I'm not quite sure how to make it come out the exact way you want it, but something like this might be close....

Col A Col B Col C Col D
Start Date End Date =TODAY() =(DAYS360(B1,C1)*-1)

Column D has the number of days remaining in your contract
period based on your end date and today's date (which will refresh).

Don't think this is totally what you want, but might help...

 

[xlbo]

Bit of a variation on above - You will need 2 cells
Say start date in A1
Number of years in B1
in C1 enter:
=DATEVALUE(LEFT(TEXT(A1,"dd/mm/yy&quot,6)&VALUE(RIGHT(TEXT(A1,"dd/mm/yy&quot,2))+B1)
in D1 enter:
="Years: " & INT(YEARFRAC(TODAY(),C1,1))& " Days: " &INT((YEARFRAC(TODAY(),C1,1)-INT(YEARFRAC(TODAY(),C1,1)))*365.25)

Should work ok Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[Hasit]

Not sure exactly how you want to review the data in C2, if you format it as GENERAL, and then use this formula:

=((B2*365)+(A2-TODAY()))/365

it should work.

For example, I have use 1/1/02 as the start date of the contract, and the time period of 3 years. The remaining period is 1.97 of a year.

The other option (I am sure the formula can be simplified, but this is a quick first attempt) is to use the following formula which will result in 1Years 350Days appearing in cell C1:

=INT(((B2*365)+(A2-TODAY()))/365)&"Years"&" "&(MID(((B2*365)+(A2-TODAY()))/365,2,3)*365)&"Days"

Is this what you want?

 

[ETID]

This function will give exact year, month and day breakdown between 2 dates.

Paste the following function into a module.....

----------------------------------------------
Function YMD(Day1 As Date, Day2 As Date) As String
Dim years, months, days, m
years = Year(Day2) - Year(Day1)
If Month(Day1) > Month(Day2) Then
years = years - 1
End If
If Month(Day2) < Month(Day1) Then
months = 12 - Month(Day1) + Month(Day2)
Else
months = Month(Day2) - Month(Day1)
End If
If Day(Day2) < Day(Day1) Then
months = months - 1
If Month(Day2) = Month(Day1) Then
years = years - 1
months = 11
End If
End If
days = Day(Day2) - Day(Day1)
If days < 0 Then
m = CInt(Month(Day2)) - 1
If m = 0 Then m = 12
Select Case m
Case 1, 3, 5, 7, 8, 10, 12
days = 31 + days
Case 4, 6, 9, 11
days = 30 + days
Case 2
If (Year(Day2) Mod 4 = 0 And Year(Day2) _
Mod 100 <> 0) Or Year(Day2) Mod 400 = 0 Then
days = 29 + days
Else
days = 28 + days
End If
End Select
End If

YMD = CStr(years) + &quot; years &quot; + CStr(months) _
+ &quot; months &quot; + CStr(days) + &quot; days &quot;
End Function
---------------------------

then in sheet...the formula will be....


=YMD(startdate,enddate)

 

[carrr]

Hasit,

That is most definitely star-worthy.

 

[carrr]

Sorry Hasit, meant ETID's function (though your was a good go, too)...
(I need to stay away from keyboards til coffee kicks in)

 

[xlbo]

Good function Hasit but the original request was for a start date and a period rather than start date and end date
For the function to work, you would need, in C1, to enter:
=DATEVALUE(LEFT(TEXT(A1,&quot;dd/mm/yy&quot,6)&VALUE(RIGHT(TEXT(A1,&quot;dd/mm/yy&quot,2))+B1)
to get the end date
and then, using your function:
=YMD(A1,C1) Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[Hasit]

carr - thanks for the attempted star. At least you checked it out.

xlbo - did you mean to address your comment to carr, rather than me? My formula works. Just!

 

[xlbo]

Durrrrr - brain not working - meant to address it to ETID

Apologies Hasit Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[Hasit]

No apology needed. Your solutions have helped me in the past, and this just proves you are human.