正如其他用户已经指出的那样，您将遇到困难，因为不同的月份有不同的天数和周数（给定的年份也可能有不同的天数）。
我认为你最好的办法是“假设”一个月有给定的天数（例如：30），你不是在闰年。那么你的算法应该是这样的：

Private Const DAYS_IN_YEAR As Integer = 365
Private Const DAYS_IN_MONTH As Integer = 30

Public Sub OutputElapsed(days As Integer)
    Dim years As Integer, months As Integer, daysRemaining As Integer

    years = Int(days / DAYS_IN_YEAR)
    daysRemaining = days - (years * DAYS_IN_YEAR)

    months = Int(daysRemaining / DAYS_IN_MONTH)
    daysRemaining = daysRemaining - (months * DAYS_IN_MONTH)

    Debug.Print _
            years & " years, " & _
            months & " months, " & _
            daysRemaining & " days"
End Sub

字符串

这是我为完成这项任务而设计的潜水艇。我从你的代码中后退了一步。如果我没看错你的问题，你有一个包含日期Range("D" & startrow)的单元格，然后你计算自该日期以来的原始天数，然后将该数字转换为年，月，天。我的代码改为从单元格中获取日期字符串（例如：3/1/2019），然后独立计算过去的年、月和日。它考虑了每个月和闰年的不同日期，通过我的测试，唯一没有处理的逻辑是，如果单元格中的日期实际上是在未来-我希望这是好的。

Sub DateCalc()
    'Worksheet Variables
    Dim mySheet As Worksheet
    Dim dateCell, outputCell As Range

    Set mySheet = Sheets("Sheet1")
    Set dateCell = mySheet.Cells(1, 1)
    Set outputCell = mySheet.Cells(1, 2)

    'Date variables
    Dim fileDate, curDate, tempDate As Date
    Dim fileYear, curYear, tempYear, years, _
    fileMonth, curMonth, tempMonth, months, _
    fileDay, curDay, tempDay, days, _
    curDPM, fileDPM _
    As Integer

    'Get date of file and calculate year, month, and day
    fileDate = dateCell.Value
    fileYear = CInt(Split(fileDate, "/")(2))
    fileMonth = CInt(Split(fileDate, "/")(0))
    fileDay = CInt(Split(fileDate, "/")(1))

    'Get the current date, year, month, and day
    curDate = Date
    curYear = CInt(Split(curDate, "/")(2))
    curMonth = CInt(Split(curDate, "/")(0))
    curDay = CInt(Split(curDate, "/")(1))

    'Calculate years passed
    If curYear > fileYear Then
        years = curYear - fileYear
    End If
    If years = "" Then
        years = 0
    End If

    'Calculate months passed
    If curMonth > fileMonth Then
        months = curMonth - fileMonth
    ElseIf curMonth = fileMonth Then
        months = 0
    ElseIf curMonth < fileMonth Then
        months = (12 - fileMonth) + curMonth
    End If

    'Calculates days per month (DPM) for current year
    Select Case curMonth
        Case 4 Or 6 Or 9 Or 11
            '31-Day months
            'April, June, September, November.
            curDPM = 30
        Case 2
            'February will either have 29 or 28 days
            'If the current year is divisible by 4 it
            'is a leap year and there are 29
            curDPM = IIf(curYear Mod 4 = 0, 29, 28)
        Case Else
            '31-Day months
            curDPM = 31
    End Select

    'Calculates days per month (DPM) for file year
    Select Case fileMonth
        Case 4 Or 6 Or 9 Or 11
            fileDPM = 30
        Case 2
            fileDPM = IIf(fileYear Mod 4 = 0, 29, 28)
        Case Else
            fileDPM = 31
    End Select

    'Calculates days passed
    If curDay > fileDay Then
        days = curDay - fileDay
    ElseIf (curDay = fileDay) Then
        days = 0
    ElseIf (curDay < fileDay) Then
        days = (fileDPM - fileDay) + curDay
    End If

    'years, months, and days are calculate independently
    'so this loop corrects them to work together
    'Ex: 12/31/2000 to 1/1/2001 would be 1 year, 1 month, 1 day without this loop
    Do
        tempDate = DateAdd("yyyy", years, fileDate)
        tempDate = DateAdd("m", months, tempDate)
        tempDate = DateAdd("d", days, tempDate)

        tempYear = CInt(Split(tempDate, "/")(2))
        tempMonth = CInt(Split(tempDate, "/")(0))
        tempDay = CInt(Split(tempDate, "/")(1))

        If tempYear > curYear Then
            years = years - 1
        ElseIf tempYear < curYear Then
            years = years + 1
        End If

        If tempMonth > curMonth Then
            months = months - 1
        ElseIf tempMonth < tempMonth Then
            months = months + 1
        End If

        If tempDay > curDay Then
            days = days - 1
        ElseIf tempDay < curDay Then
            days = days + 1
        End If
    Loop While tempDate <> curDate

    'Set cell to display time passed
    outputCell.Value = years & " Years, " & months & " Months, " & days & " Days"
End Sub

字符串
This is the output要使其适用于您的单个工作表，您需要做的就是更改mySheet、dateCell和outputCell变量。我的日期单元格设置为mm/dd/yyyy格式。我没有用其他日期格式测试过它。

这是我的解决方案。我在我的公司里使用这个，虽然它是用印度尼西亚语写的，因为我来自那里。这是一个函数，它需要两个参数：tanggal_akhir（翻译为“开始日期”）和tanggal_mulai（翻译为“结束日期”）。公式是BEDATANGGAL，意思是“日期差异”。希望对你有帮助。

Function BEDATANGGAL(tanggal_mulai As Date, tanggal_akhir As Date)

Dim hari As Integer
Dim bulan As Integer
Dim tahun As Integer
Dim durasi As Integer
Dim tambah As Date

hari = 0
bulan = 0
tahun = 0
tambah = tanggal_mulai

durasi = DateDiff("d", tanggal_mulai, tanggal_akhir) - 1

For i = 0 To durasi

hari = hari + 1
tambah = tambah + 1

If Day(tanggal_mulai) = Day(tambah) Then

    hari = 0
    bulan = bulan + 1

    End If

If bulan = 12 Then

    hari = 0
    bulan = 0
    tahun = tahun + 1

    End If

Next i

BEDATANGGAL = tahun & " tahun, " & bulan & " bulan, " & hari & " hari"

End Function

字符串

对泰勒上述回答的修改

Function date_duration(start_date, end_date)
'Worksheet Variables

Dim dateCell, outputCell As Range


'Date variables
Dim fileDate, curDate, tempDate As Date
Dim fileYear, curYear, tempYear, years, _
fileMonth, curMonth, tempMonth, months, _
fileDay, curDay, tempDay, days, _
curDPM, fileDPM _
As Integer

'Get date of file and calculate year, month, and day
fileDate = start_date
fileYear = CInt(Split(fileDate, "/")(2))
fileMonth = CInt(Split(fileDate, "/")(0))
fileDay = CInt(Split(fileDate, "/")(1))

'Get the current date, year, month, and day
curDate = end_date
curYear = CInt(Split(curDate, "/")(2))
curMonth = CInt(Split(curDate, "/")(0))
curDay = CInt(Split(curDate, "/")(1))

'Calculate years passed
If curYear > fileYear Then
    years = curYear - fileYear
End If
If years = "" Then
    years = 0
End If

'Calculate months passed
If curMonth > fileMonth Then
    months = curMonth - fileMonth
ElseIf curMonth = fileMonth Then
    months = 0
ElseIf curMonth < fileMonth Then
    months = (12 - fileMonth) + curMonth
End If

'Calculates days per month (DPM) for current year
Select Case curMonth
    Case 4 Or 6 Or 9 Or 11
        '31-Day months
        'April, June, September, November.
        curDPM = 30
    Case 2
        'February will either have 29 or 28 days
        'If the current year is divisible by 4 it
        'is a leap year and there are 29
        curDPM = IIf(curYear Mod 4 = 0, 29, 28)
    Case Else
        '31-Day months
        curDPM = 31
End Select

'Calculates days per month (DPM) for file year
Select Case fileMonth
    Case 4 Or 6 Or 9 Or 11
        fileDPM = 30
    Case 2
        fileDPM = IIf(fileYear Mod 4 = 0, 29, 28)
    Case Else
        fileDPM = 31
End Select

'Calculates days passed
If curDay > fileDay Then
    days = curDay - fileDay
ElseIf (curDay = fileDay) Then
    days = 0
ElseIf (curDay < fileDay) Then
    days = (fileDPM - fileDay) + curDay
End If

'years, months, and days are calculate independently
'so this loop corrects them to work together
'Ex: 12/31/2000 to 1/1/2001 would be 1 year, 1 month, 1 day without this loop
Do
    tempDate = DateAdd("yyyy", years, fileDate)
    tempDate = DateAdd("m", months, tempDate)
    tempDate = DateAdd("d", days, tempDate)

    tempYear = CInt(Split(tempDate, "/")(2))
    tempMonth = CInt(Split(tempDate, "/")(0))
    tempDay = CInt(Split(tempDate, "/")(1))

    If tempYear > curYear Then
        years = years - 1
    ElseIf tempYear < curYear Then
        years = years + 1
    End If

    If tempMonth > curMonth Then
        months = months - 1
    ElseIf tempMonth < tempMonth Then
        months = months + 1
    End If

    If tempDay > curDay Then
        days = days - 1
    ElseIf tempDay < curDay Then
        days = days + 1
    End If
Loop While tempDate <> curDate

'Set cell to display time passed
date_duration = years & " Years, " & months & " Months, " & days & " Days"
End Function

字符串

已经很久了，但可能对某人有用。

Public Function convertToYMD(days As Integer) As String
    Dim intYears As Integer
    Dim intMonths As Integer
    Dim intDays As Integer
    Dim strY As String
    Dim strM As String
    Dim strD As String
    
    intYears = Evaluate("DATEDIF(0," & days & ",""y"")")
    intMonths = Evaluate("DATEDIF(0," & days & ",""ym"")")
    intDays = Evaluate("DATEDIF(0," & days & ",""md"")")
    
    If intYears > 0 Then
        If intYears = 1 Then
            strY = "1 Year, "
        Else
            strY = intYears & " Years, "
        End If
    End If
    If intMonths > 0 Then
        If intMonths = 1 Then
            strM = "1 Month "
        Else
            strM = intMonths & " Months "
        End If
    End If
    If intDays > 0 Then
        If intDays = 1 Then
            strD = "and 1 Day"
        Else
            strD = "and " & intDays & " Days"
        End If
    End If
    
    
    convertToYMD = strY & strM & strD
End Function

字符串

尝试以下操作。

date_created = Worksheets("FileCleaner").Range("D" & startrow).Value
File_Age_Day = DatePart("yyyy", date_created) & " years, " & DatePart("M", 
date_created) & " Months, " & DatePart("d", date_created) & " days"
Worksheets("FileCleaner").Range("E" & startrow).Value = File_Age_Day

字符串