Format for rounding in a query when using a function with date fields

[smg]

I have the following function in a module. Now I need to add it to a query and round so that I get the number of months between two dates


My query field is:

Months to Release: GetDays360 ([TimesheetDate,[ReleaseDate])

The query is just calculating the time between i.e., ReleaseDate 10/31/2022 TimesheetDate 9/6/2019 Result 43086
what I expect to get is the number of months which is 38

How do I write this around this function and fields?

[pbaldy]

I assume that's a custom function you're calling. Are you sure you're passing it 2 date values (not text)? What is the function, so we can double check what it's supposed to receive and return?

[CJ_London]

why not just use the datediff function?

[smg]

I am not using datediff because it differs from Days360 enough to make a difference and my customer requires we get as close to Days360 as possible. I actually had this working but not sure where I messed it up.
Here is my function:
Public Function GetDays360(TimesheetDate As Date, Release As Date)
Days360 = ((Year(Release Date) - Year(Timesheet Date)) - 1) * 360 + ((12 - Month(Timesheet Date)) * 30) + (30 - Day(Timesheet Date)) + ((Month(Release Date) - 1) * 30) + (Day(Release Date))

End Function

[Bob Fitz]

What are the variables in red. They don't appear to be declared in the function

Days360 = ((Year(Release Date) - Year(Timesheet Date)) - 1) * 360 + ((12 - Month(Timesheet Date)) * 30) + (30 - Day(Timesheet Date)) + ((Month(Release Date) - 1) * 30) + (Day(Release Date))

[smg]

ReleaseDate 10/31/2022
TimesheetDate 9/6/2019

[orange]

If you have embedded spaces in your variable names, you need to use/enclose with "[" and "]".
eg [Release Date].

Perhaps you should name the function MonthsXX or similar to show you expect Months to be returned.

Eg using Datediff

Function fGetMonths(releaseDate As Date, TimesheetDate As Date) As Integer
    On Error GoTo fGetMonths_Error
    fGetMonths = DateDiff("m", releaseDate, TimesheetDate)
   
    On Error GoTo 0
fGetMonths_Exit:
    Exit Function

fGetMonths_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fGetMonths, line " & Erl & "."
    GoTo fGetMonths_Exit
End Function

[smg]

I do not have embedded spaces

[orange]

I was basing my comment on your post #4 -specifically the code in blue??

Here is my function:
Public Function GetDays360(TimesheetDate As Date, Release As Date)
Days360 = ((Year(Release Date) - Year(Timesheet Date)) - 1) * 360 + ((12 - Month(Timesheet Date)) * 30) + (30 - Day(Timesheet Date)) + ((Month(Release Date) - 1) * 30) + (Day(Release Date))

End Function

[smg]

here is what I have:

Public Function GetDays360(ReleaseDate As Date, TimesheetDate As Date) As Integer
GetDays360 = ((Year(ReleaseDate) - Year(TimesheetDate)) - 1) * 360 + ((12 - Month(TimesheetDate)) * 30) + (30 - Day(TimesheetDate)) + ((Month(ReleaseDate) - 1) * 30) + (Day(ReleaseDate))
End Function

ReleaseDate 10/31/2020
TimesheetDate 9/6/2019

Result I am getting is 1052
Result I would like to get 38

Help!

[smg]

Apologies, ReleaseDate 11/29/2019

[orange]

???? I get 332 with your data and function. OOOOps I changed the wrong date.!!!!!!!

UPDATE:

Sub testgetDays360()
Dim rdate As Date: rdate = #11/29/2019#
Dim tdate As Date: tdate = #9/6/2019#
Debug.Print GetDays360(rdate, tdate)
End Sub

83

In plain English, what do you think your function is doing??

[Bob Fitz]

Apologies, ReleaseDate 11/29/2019

Using your dates I get 443

[Gicu]

Did you look at the update file I uploaded in response to your other post? https://www.accessforums.net/showthread.php?t=81516?

Here is the function to get you the months:

Public Function GetMonths360(TimesheetDate As Date, Release As Date)
GetMonths360 = ((Year([TimesheetDate]) - Year([Release])) - 1) * 360 + ((12 - Month([Release])) * 30) + (30 - Day([Release])) + ((Month([TimesheetDate]) - 1) * 30) + (Day([TimesheetDate]))
GetMonths360 = -Int(-GetMonths360 / 30)
End Function

Cheers,
Vlad

[smg]

Thanks Vlad, I think that works!