Function fGetDate(pStrVal As String, pDateVal As Variant)

On Error Resume Next

Dim i As Integer, xLen As Integer
Dim xStr As String, xText As String, xNum As String, xDate As String

fGetDate = vbNullString
xNum = vbNullString
xText = vbNullString
xLen = Len(pStrVal)

For i = 1 To xLen
xStr = Mid(pStrVal, i, 1)
If IsNumeric(xStr) Then
xNum = xNum + xStr
Else
xText = xText + xStr
End If
Next

xText = Trim(xText)

Select Case xText
Case "gün", "Gün", "GÜN"
xDate = "d"
Case "hafta", "Hafta", "HAFTA"
xDate = "ww"
Case "ay", "Ay", "AY"
xDate = "m"
Case "yıl", "Yıl", "YIL"
xDate = "yyyy"
End Select

fGetDate = DateAdd(xDate, CInt(xNum), pDateVal)

End Function

Örneğin;
A1 hücresi 5 gün olsun
B1 hücresi 27.05.2022 olsun
C1 hücresine = fGetDate(A1;B1)
yazıp dener misiniz
istediğinizi karşılayabilir...