Computing for the No of Months and Days in Date Range

HCL Domino Domino Forum
1

Hello All,

I have been searching this forum and I can’t find exact formula that will compute the exact number of months and days for two dates…

How will I compute for the total number of month and days for a date range.

e.g.

End_Date - Start_Date = Result

04/01/2007 - 03/02/2007 = 1 Month and 1 day(s)

need help, Thanks.

pen

2

Subject: Computing for the No of Months and Days in Date Range

Assuming your month is a true calendar month, the following formula determines the number of months between two dates, assuming that each change of month means another month has passed. It computes number of months by subtracting the ending month number, from the beginning month number and then adds 12 months for each year that has passed.For example, from December 31, 1999 to January 1, 2000, the answer is one month by evaluating this as 1 - 12 + 12

If the two dates are in the same month and year, the returned result is a 0 (zero).

If the two dates are in the same month but the second date is the following year, the returned result is 12.

StartDate and EndDate are the input dates.

Note: You could save your field returning errors, if you add something like this…

@If( @IsAvailable( StartDate ) & @IsTime( StartDate ) &

@IsAvailable( EndDate ) & @IsTime( EndDate ); @Success; @Return( "" ) );

vSD := StartDate;

vED := EndDate;

@Month( vED ) + ( 12 * ( @Year( vED ) - @Year( vSD ) ) - @Month( vSD ) );

This is a variation of the previous formula. It assumes that a month has not passed, until you have passed the day of the month in which you began the starting date.

For example the difference between January 15 1999 and February 1, 1999 is 0 months because we have not yet reached February 15.

vSD := StartDate;

vED := EndDate;

( ( @Month( vED ) + ( 12 * ( @Year( vED ) - @Year( vSD ) ) ) - @Month( vSD ) ) - ( @If ( @Day( vED ) <= @Day( vSD ); 0; 1 ) ) );

Hope the above helps a bit.

Afraid I have to go throw a spanner at a server, so I’ll try to come back later with Days, if no one else does.

3

Subject: LotusScript version of - Computing for the No of Months and Days in Date Range

Function MonthCountBetnDates(startdt As String, enddt As String) As Integer On Error Goto ErrorHandler

Dim a As Variant

Dim b As Variant

Dim ayyyymm As Integer

Dim byyyymm As Integer

Dim count As Integer



a = Cdat(startdt) '10-2005	

b = Cdat(enddt)   '01-2006

count = (Year(b) - Year(a)) 	'offset of year (2006-2005=1)

count = count * 12 			'offset in months (1*12=12)

count = count + Month(b) 	'reach to end month (12+1=13)

count = count - Month(a)		'scale backwards to start month (13-10=3)

count = count + 1			'adjustment for exclusive subraction (13+1=14)



MonthCountBetnDates = count

Print "<br>", "MonthCountBetnDates:", startdt, "-", enddt, "=", MonthCountBetnDates, " months"

Exit Function

ErrorHandler:

Print "An error occured in aInternalReimb - MonthCountBetnDates() (" & Err() & " - " & Error$() & " Line No. - 0"&Erl() &  ")<br>"

Messagebox  "An error occured in aInternalReimb - MonthCountBetnDates() (" & Err() & " - " & Error$() & " Line No. - 0"&Erl() &  ")"

Exit Function

End Function

4

Subject: RE: Computing for the No of Months and Days in Date Range

Thanks so much,

I’ll try the logic…and hopefully i can come up with correct formula…

Again Thank you,

pen