Change number to Year, Month, Days

[trident]

I have created a subform which is a basic information for Employees, on this form there is a calculated Text that computes the length of service but in days. I have the following information:

Text 49 - current date
Join Date
End Date

the formula i have created can give me the number of days.

=iif(isnull([End Date]),[Text 49]-[Join Date],[End Date]-[Join Date])



With this formula, how can I get the year month and day.

Help please!

[ItsMe]

Maybe the DataAdd function is what you are looking for.
https://msdn.microsoft.com/en-us/lib.../gg251759.aspx

[Missinglinq]

In doing this kind of thing you have to understand that all years are not equal (some have 364 days, some 365 days) and all months are not equal (they can be 28-31 days) but this should get you close enough:

Dim X As Integer

X = iif(isnull([End Date]),[Text 49]-[Join Date],[End Date]-[Join Date])

 Years =  X \ 364
 Months =  ((X Mod 364) \ 7) \ 4
 Weeks =  (X Mod 364) \ 7
 Days =  X Mod 7

Linq ;0)>

[Rawb]

If you mean, you want to convert "130 days" to "4 months, 10 days" (for example), then there's no simple answer to that.

Unfortunately, the number of days in a month varies from month to month (and in the case of February, year to year!) so that can be very difficult to really calculate.

Perhaps the easiest way would be to use a Function call that uses the Day(), Month(), and Year() Functions:

Public Function DaysInMonth(InputDate) As Integer
  DaysInMonth = DateSerial(Year(InputDate), Month(InputDate) + 1, 1) - DateSerial(Year(InputDate), Month(InputDate), 1)
End Function

Public Function DurationToString(DateStart As Date, DateEnd As Date) As Variant
  On Error GoTo Error_DurationToString
  DurationToString = ""

  Dim dteTmp As Date
  Dim nbrDay As Integer
  Dim nbrMonth As Integer
  Dim nbrYear As Integer

  If DateStart > DateEnd Then
    dteTmp = DateStart
    DateStart = DateEnd
    DateEnd = dteTmp
  End If

  ' Calculate the number of years
  nbrYear = Year(DateEnd) - Year(DateStart)

  ' Calculate the number of months
  nbrMonth = Month(DateEnd) - Month(DateStart)

  Do While nbrMonth < 0
    nbrMonth = nbrMonth + 12
    nbrYear = nbrYear - 1
  Loop

  ' Calculate the number of days
  nbrDay = Day(DateEnd) - Day(DateStart)

  Do While nbrDay < 0
    nbrDay = nbrDay + DaysInMonth(DateSerial(Year(DateStart) + nbrYear, Month(DateStart) + nbrMonth, 1))
    nbrMonth = nbrMonth - 1

    If nbrMonth < 0 Then
      nbrMonth = nbrMonth + 12
      nbrYear = nbrYear - 1
    End If
  Loop

  If nbrYear > 1 Then
    DurationToString = nbrYear & " years"
  ElseIf nbrYear > 0 Then
    DurationToString = nbrYear & " year"
  End If

  If nbrMonth > 0 Then
    If Len(DurationToString & vbNullString) > 0 Then
      DurationToString = DurationToString & ", "
    End If

    If nbrMonth > 1 Then
      DurationToString = DurationToString & nbrMonth & " months"
    ElseIf nbrMonth > 0 Then
      DurationToString = DurationToString & nbrMonth & " month"
    End If
  End If

  If nbrDay > 0 Then
    If Len(DurationToString & vbNullString) > 0 Then
      DurationToString = DurationToString & ", "
    End If

    If nbrDay > 1 Then
      DurationToString = DurationToString & nbrDay & " days"
    ElseIf nbrDay > 0 Then
      DurationToString = DurationToString & nbrDay & " day"
    End If
  End If

Function_Closing:
  Exit Function

Error_DurationToString:
  DurationToString = False
End Function

If you put the above code in a Module, then you can return a string like "4 months, 10 days" just by calling the function DurationToString(DateStart, DateEnd).

[trident]

I am really new in VBA, where will I put this code, in the form, BEFORE UPDATE or AFTER UPDATE

[trident]

Missinglinq,

I saw your reply to a the same question this is around 2007.

how can I enter this, do I need to create a blank text box and enter in the Event (Before Update).

can you show me how to code this using the information given:

Text 49 - Current Date
JoinDate - Start Date
EndDate - Last Day

the code is as follows:

Function YMD(No As Integer) As Variant
Dim Y As Long
Dim M As Long
Dim D As Long

Y = Int(No / 365.25)
M = Int((No - (Int(No / 365.25) * 365.25)) / 30.4375)
D = No - ((Y * 365.25) + (M * 30.4375))

YMD = Y & " years " & M & " months " & D & " days"
End Function

Where should the labelnames enter on the above coding?

Thanks in advance

[Dhamdard]

If I understand the question correctly, I will go with the DateDiff function. The advantage of this function is that it can display either day or month or year between two dates. For example: John joined an organization on 25-May-2012 and left the organization on 25-May-2015.

Function: DateDiff("interval",[StartDate],[EndDate])
Result 1: If the interval is Day DateDiff("d",[StartDate],[EndDate]) the resultant number will be 761 days.
Result 2: If the interval is Month DateDiff("m",[StartDate],[EndDate]) the resultant number will be 25 months.
Result 3: If the interval is Year DateDiff("yyyy",[StartDate],[EndDate]) the resultant number will be 2 years.

Hope this solves the problem.

Thanks,
Dad

[Dhamdard]

The resultant numbers are OK if joining date is 24-Apr-12 and departure date is 25-May-2014.

Thanks,
Dad

[Rawb]

If you are NEVER going to use this functionality anywhere EXCEPT on this one Form, you can just include the functions at the top of the VBA code of the Form (directly under the Option Compare Database and Option Explicit lines). If this is something that you might want to be able to use elsewhere in the db (say in another Form or a Report or Query), then you'd be better off creating a blank Module and putting it in there.

Then, in the Control's After Update or Exit Event, just add the following code:

If Len(Me!JoinDate & vbNullString) > 0 And Len(Me!EndDate & vbNullString) > 0 Then
    Me!ControlName = DurationToString(Me!JoinDate, Me!EndDate)
End If

Where ControlName is the name of the Form Control used to display the length of service.

[trident]

Dear Rawb,

I tried the codes above and changed the ControlName = Text24, however, the error the VBA is showing is the "DurationToString". The error message is Compile Error: Sub or Function not defined.

How can i go around this error.

Thanks in advance for the assistance.

Trident

[Rawb]

Did you take the code in my first post and put into a Module? Also be sure to name the Module something other than just "DurationToString". I'd recommend "modDurationToString" or something similar.