Forum Discussion
Replicating an Excel formula in Access?
I'm trying to import some Excel data into Access. I have one column in my Excel spreadsheet that performs a calculation on two date cells.
For example:
Excel cell D1 has a date value, for this example, let's use a date of 01-01-1990 (i.e., Jan 1, 1990).
Excel cell D3 has a date value of any date after the date entered in A1, let's use 9-8-2021 (Sept 08, 2021).
Excel cell D5 has a formula:
=DATEDIF($D$1,D3,"y") & "-" & DATEDIF($D$1,D3,"ym") & "-" & DATEDIF($D$1,D3,"md")
When using the above example dates entered in D1 & D3 yields a display in D5 of:
31-8-7
i.e., 31 years, 8 months, 7 days, showing the calculation of time (in terms of years, months days) between the two dates.
Can I import this functionality into Access? If so, how is this done?
Idealy I'd like the Access field to be calculated and displayed as:
31Y, 08M, 07D
but I'll take anything I can get.
You can create a new, calculated field in an Access table. The expression would be different, of course.
That said, we discourage calculated fields in relational database application tables. They belong in queries or controls on forms or reports, not in tables.
In Excel you have no choice. Everything is in one flat interface--the worksheet.
Access is not subject to that limitation and the interface offers much richer alternatives. Therefore, don't think about storing the calculation as part of the data. Think about DISPLAYING it, only when needed, in a form or report.
Because you want the years, months and days elapsed, it needs to be done in a VBA function, rather than as a calculated value in a field.
Public Function getAge(ByVal vDateOfBirth As Variant, ByVal vDateReference As Variant) As String
Dim idays As Integer
Dim iMonths As Integer
Dim iYears As Integer
Dim iDaysInMonth As Integer
Dim dDateOfBirth As Date
Dim dDateReference As Date
dDateOfBirth = Int(Nz(vDateOfBirth, Now()))
dDateReference = Int(Nz(vDateReference, Now()))
iDaysInMonth = DateSerial(Year(dDateOfBirth), Month(dDateOfBirth) + 1, 1) - DateSerial(Year(dDateOfBirth), Month(dDateOfBirth), 1)
iYears = DatePart("yyyy", dDateReference) - DatePart("yyyy", dDateOfBirth)
iMonths = DatePart("m", dDateReference) - DatePart("m", dDateOfBirth)
idays = DatePart("d", dDateReference) - DatePart("d", dDateOfBirth)
If idays < 0 Then
iMonths = iMonths - 1
idays = iDaysInMonth + idays
End If
If iMonths < 0 Then
iYears = iYears - 1
iMonths = 12 + iMonths
End If
getAge = iYears & " yr(s). " & iMonths & " mo(s). " & idays & " day(s)"
End Function
