Day issue with Function 3

[traycee]

Hi,
I'm using the following function and don't exactly understand why it isn't working. I do know that days are a pain to count. Example: 9/27/2004 to 1/21/2005 should be 3 months and 25 days but it comes up 3 months and 26 days. Yet other dates come up right...sucn as 11/4/2004 to 1/12/2005 comes out to 2 months and 8 days which is correct. I'm thinking this may be something I can't correct. Is there a better way to do this. The function I'm using follows:

Function YearMonthDate(DateAdmittedToProgram As Date, DischageDate As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(DischageDate), month(DateAdmittedToProgram), Day(DateAdmittedToProgram))
Y = Year(DischageDate) - Year(DateAdmittedToProgram) + (Temp1 > DischageDate)
M = month(DischageDate) - month(DateAdmittedToProgram) - (12 * (Temp1 > DischageDate))
D = Day(DischageDate) - Day(DateAdmittedToProgram)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(DischageDate), month(DischageDate) + 1, 0)) + D + 1
End If
YearMonthDate = Y & " years " & M & " months " & D & " days"
End Function

Thanks

 

[VBslammer]

Check out the DateDiff() function

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[traingamer]

Like DateDiff("d",DateAdmittedToProgram ,DischageDate) will simply return the number of days.



traingamer

 

[traycee]

The datediff function gives me 0 year, 2 months, 69 days for the difference between 11/4/2004 to 1/12/2005 instead of 0 years, 2 months, 8 days.

 

[traingamer]

Your actual flaw is in whenever D is < 0

    If D < 0 Then
        M = M - 1
        D = Day(DateSerial(Year(DischageDate), month(DischageDate) + 1, 0)) + D [COLOR=red]+ 1[/color]
    End If

Try using start and end dates of '12/31/04' and '1/1/2005' expect 1 - get 2.
Just remove the "+ 1"

traingamer

 

[traycee]

I'm correct on more of the dates when I take off the +1 as you suggested...so it's better, but on dates like 6/10/2004 to 2/8/2005 comes out at 0 years 7 months and 26 days, but the answer should be 0 years, 7 months and 28 days.

 

[PHV]

And this ?
D = Day(DateSerial(Year(DateAdmittedToProgram), Month(DateAdmittedToProgram) + 1, 0)) + D

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer]

Sorry, my bad.
Your basic flaw is that you are going forward a month so your calculation is using the number of days in the ending month (Feb in this case) then subtracting the difference (28 - 2 = 26) rather than the days from the preceding month (31 - 2 = 29 which is the correct answer - not 28). Your answer will be wrong every time the ending month has a different number of days than the preceding month. And the answers will be off the most with Feb ending dates.

traingamer

 

[traycee]

Better...but still not quite there. Now..for the example I gave above, the answer is correct using the code you provided, but for 9/24/2004 to 2/7/2005 I get 0 years 4 months 13 days instead of the correct answer which is 0 years 4 months 14 days. I hate working with days...they are always an issue for me.

 

[PHV]

the correct answer which is 0 years 4 months 14 days
Really ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ugathecat]

I just tested both of these and they seem to give the correct answer to all of the dates that have been provided so far. The second snippet is really just the same as the first, but forced into less lines by getting rid of the 'Temp'.

y = DateDiff("yyyy", admitted, discharge)
Temp = DateAdd("yyyy", y, admitted)
If Temp > discharge Then
    y = y - 1
    Temp = DateAdd("yyyy", -1, Temp)
End If
m = DateDiff("m", Temp, discharge)
Temp = DateAdd("m", m, Temp)
If Temp > discharge Then
    m = m - 1
    Temp = DateAdd("m", -1, Temp)
End If
d = DateDiff("d", Temp, discharge)

y = DateDiff("yyyy", admitted, discharge)
If DateAdd("yyyy", y, admitted) > discharge Then y = y - 1
m = DateDiff("m", DateAdd("yyyy", y, admitted), discharge)
If DateAdd("m", m, DateAdd("yyyy", y, admitted)) > discharge Then m = m - 1
d = DateDiff("d", DateAdd("m", m, DateAdd("yyyy", y, admitted)), discharge)

 

[traycee]

Thanks ugathecat! I don't think I would have been able to correct my code without your help. It works perfectly now. All the dates are correct. Also, in case anyone is looking for a function that calculates the difference between dates accurately...I've included the finished code below:

Function YearMonthDate(DateAdmittedToProgram As Date, DischageDate As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Y = DateDiff("yyyy", DateAdmittedToProgram, DischageDate)
If DateAdd("yyyy", Y, DateAdmittedToProgram) > DischageDate Then Y = Y - 1
M = DateDiff("m", DateAdd("yyyy", Y, DateAdmittedToProgram), DischageDate)
If DateAdd("m", M, DateAdd("yyyy", Y, DateAdmittedToProgram)) > DischageDate Then M = M - 1
D = DateDiff("d", DateAdd("m", M, DateAdd("yyyy", Y, DateAdmittedToProgram)), DischageDate)
YearMonthDate = Y & " years " & M & " months " & D & " days"
End Function

After putting this in a new module and naming the module modDateDiff, I reference this in a column of a query as Date:YearMonthDate([DateAdmittedToProgram],[DischageDate])

And yep, I know I spelled Discharge wrong.

Thanks ugathecat and everyone else who helped.

 

[PHV]

How are you explaining that ?
? YearMonthDate(#9/24/2004#,#10/7/2004#)
0 years 0 months 13 days
? YearMonthDate(#9/24/2004#,#11/7/2004#)
0 years 1 months 14 days

 

[traingamer]

Those look like the expected answers.
??!?

traingamer

 

[PHV]

Why adding one month to 13 days should give 1 month and 14 days ?

 

[traingamer]

If you accept the premise that it counts from the 27th of the month and some months have 30 days and some 31, what is the discrepancy?

traingamer

 

[PHV]

and some months have 30 days and some 31
We are talking about the SAME starting month in my example.

 

[traycee]

I don't know that I understand exactly why ugathecat's code corrected my function...I only know that all of the dates are exactly right on the money now and I'm happy that it works. Otherwise say..for (9/24/2004,2/7/2005) the code was giving me 4 months 13 days instead of the correct answer which would be 4 months and 14 days. 7 days left in January plus the first 7 days in February would be 14 days...not 13. Although, at this point I'm not sure I'm sure about anything. Plus, on some other dates it was a couple of days off instead of being correct or just a single day off.

 

[traingamer]

Your original function was looking forward from the ending month to the following month.
Both PHV and ugathecat gave you a function that goes from the proper date in the prior month, which will give you a difference of up to 3 days.

This horse is officially beaten to death!!!!

traingamer

 

[PHV]

9/24/2004,2/7/2005
For me: 6 days in september, 4 months, 7 days in february.