This course teaches how to install and configure Windows Server 2012 R2. It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

I have a formula resulting in "days until event". I want to display it as

[#years]a [#months] if more than one year

[#months] [#days] if more than two months

[#days] otherwise

The stupid idea was to use "[>365]JJ\a MM;[>60]MM TT;##" (german template), until I found out that it wants to format a date that way, and is one month off for the "months days" format. E.g. 70 days are "03 10" instead of "02 10" or similar because the 70th day in a year is 10-March.

Any way to be more smart than that?

Note: The formula used to build the value is somewhat complex, and I don't want having to use additional cells, so building another formula around that for "formatting" should be avoided (but would be an option of last resort).

[#years]a [#months] if more than one year

[#months] [#days] if more than two months

[#days] otherwise

The stupid idea was to use "[>365]JJ\a MM;[>60]MM TT;##" (german template), until I found out that it wants to format a date that way, and is one month off for the "months days" format. E.g. 70 days are "03 10" instead of "02 10" or similar because the 70th day in a year is 10-March.

Any way to be more smart than that?

Note: The formula used to build the value is somewhat complex, and I don't want having to use additional cells, so building another formula around that for "formatting" should be avoided (but would be an option of last resort).

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If you need some background: The value is corresponding to

(points needed for getting the next EE rank) / (12 month average of points) * 30

(in a workbook based on http://www.experts-exchange.com/articles/3887/Getting-your-EE-Ranking-statistics-in-Excel-The-Next-Generation.html).

The new workbook is very detailled, and too distracting.

Guess you are thinking too big. To make it more clear, I'll prepare something simple.

Usage: = DUE(A1)

```
Function DUE(DaysUntil As Range) As String
Dim strTemp As String
Select Case DaysUntil
Case Is > 365
DUE = Split(YearsMonthsDays(Now, DateAdd("d", DaysUntil, Now)), ",")(0)
Case Is > 60
strTemp = YearsMonthsDays(Now, DateAdd("d", DaysUntil, Now))
If Split(strTemp, ", ")(1) = "0 days" Then
DUE = Split(strTemp, ",")(0)
Else
DUE = strTemp
End If
Case Is > 1
DUE = DaysUntil & " days"
Case Else
DUE = "1 day"
End Select
End Function
Private Function YearsMonthsDays(Date1 As Date, Date2 As Date, Optional ShowAll As _
Boolean = False, Optional Grammar As Boolean = True)
' This function returns a string "X years, Y months, Z days" showing the time
' between two dates. This function may be used in any VBA or VB project
' Date1 and Date2 must either be dates, or strings that can be implicitly
' converted to dates. If these arguments have time portions, the time portions
' are ignored. If Date1 > Date2 (after ignoring time portions), the function
' returns an empty string
' ShowAll indicates whether all portions of the string "X years, Y months, Z days"
' are included in the output. If ShowAll = True, all portions of the string are
' always included. If ShowAll = False, then if the year portion is zero the year
' part of the string is omitted, and if the year portion and month portion are both
' zero, than both year and month portions are omitted. The day portion is always
' included, and if at least one year has passed then the month portion is always
' included
' Grammar indicates whether to test years/months/days for singular or plural
' By definition, a "full month" means that the day number in Date2 is >= the day
' number in Date1, or Date1 and Date2 occur on the last days of their respective
' months. A "full year" means that 12 "full months" have passed.
' In Excel, this function is an alternative to the little-known DATEDIF. DATEDIF
' usually works well, but can create strange results when a date is at month end.
' Thus, this formula:
' =DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months, " &
' DATEDIF(A1,B1,"md") & " days"
' will return "0 years, 1 months, -2 days" for 31-Jan-2006 and 1-Mar-2006.
' This function will return "0 years, 1 month, 1 day"
' Chip Pearson gives a VBA equivalent in his Age() function at
' http://www.cpearson.com/excel/datedif.htm
' Unfortunately, it gives the same result in the test case above
Dim TestYear As Long, TestMonth As Long, TestDay As Long
Dim TargetDate As Date, Last1 As Date, Last2 As Date
' Strip time portions
Date1 = Int(Date1)
Date2 = Int(Date2)
' Test for invalid dates
If Date1 > Date2 Then
YearsMonthsDays = ""
Exit Function
End If
' Test for whether the calendar year is the same
If Year(Date2) > Year(Date1) Then
' Different calendar year.
' Test to see if calendar month is the same. If it is, we have to look at the
' day to see if a full year has passed
If Month(Date2) = Month(Date1) Then
If Day(Date2) >= Day(Date1) Then
TestYear = DateDiff("yyyy", Date1, Date2)
Else
TestYear = DateDiff("yyyy", Date1, Date2) - 1
End If
' In this case, a full year has definitely passed
ElseIf Month(Date2) > Month(Date1) Then
TestYear = DateDiff("yyyy", Date1, Date2)
' A full year has not passed
Else
TestYear = DateDiff("yyyy", Date1, Date2) - 1
End If
' Calendar year is the same, so a full year has not passed
Else
TestYear = 0
End If
' Test to see how many full months have passed, in excess of the number of full
' years
TestMonth = (DateDiff("m", DateSerial(Year(Date1), Month(Date1), 1), _
DateSerial(Year(Date2), Month(Date2), 1)) + IIf(Day(Date2) >= _
Day(Date1), 0, -1)) Mod 12
' See how many days have passed, in excess of the number of full months. If the day
' number for Date2 is >= that for Date1, it's simple
If Day(Date2) >= Day(Date1) Then
TestDay = Day(Date2) - Day(Date1)
' If not, we have to test for end of the month
Else
Last1 = DateSerial(Year(Date2), Month(Date2), 0)
Last2 = DateSerial(Year(Date2), Month(Date2) + 1, 0)
TargetDate = DateSerial(Year(Date2), Month(Date2) - 1, Day(Date1))
If Last2 = Date2 Then
If TestMonth = 11 Then
TestMonth = 0
TestYear = TestYear + 1
Else
TestMonth = TestMonth + 1
End If
Else
TestDay = DateDiff("d", IIf(TargetDate > Last1, Last1, TargetDate), Date2)
End If
End If
If ShowAll Or TestYear >= 1 Then
YearsMonthsDays = TestYear & IIf(TestYear = 1 And Grammar, " year, ", _
" years, ") & TestMonth & IIf(TestMonth = 1 And Grammar, " month, ", _
" months, ") & TestDay & IIf(TestDay = 1 And Grammar, " day", " days")
Else
If TestMonth >= 1 Then
YearsMonthsDays = TestMonth & IIf(TestMonth = 1 And Grammar, " month, ", _
" months, ") & TestDay & IIf(TestDay = 1 And Grammar, " day", " days")
Else
YearsMonthsDays = TestDay & IIf(TestDay = 1 And Grammar, " day", " days")
End If
End If
End Function
```

```
Function FmtDaysRemain(days As Integer) As String
Select Case days
Case Is > 365: FmtDaysRemain = Format((days \ 365) * 100 + (days Mod 365 \ 30), "00\a 00")
Case Is > 90: FmtDaysRemain = Format((days \ 30) * 100 + days Mod 30, "00\m 00")
Case Else: FmtDaysRemain = Format(days, "#0")
End Select
End Function
```

(with a slight change).To stay with your examples:

```
1000 => 02a 09
370 => 01a 00
366 => 01a 00
91 => 03 01 (or 03m 01)
61 => 61 (originally 02 01, but now want to have it in days)
30 => 30
```

```
Function FmtDaysRemain(days As Integer) As String
Select Case days
Case Is > 365
FmtDaysRemain = Format((days \ 365), "00\a") & Format((days Mod 365) \ 30, " 00")
Case Is > 90 ' > 60?
FmtDaysRemain = Format((days \ 30) * 100 + days Mod 30, "00\m 00")
Case Else
FmtDaysRemain = Format(days, "#0")
End Select
End Function
```

```
=IF(INT(B1/365.25)>0,INT(B1/365.25)&"a "&ROUND(((B1/365.25)-INT(B1/365.25))*12,0),IF(B1>89,INT(MONTH(NOW()+B1)-MONTH(NOW()))&" "&DATE(YEAR(NOW()),MONTH(NOW())+INT(MONTH(NOW()+B1)-MONTH(NOW())),DAY(NOW()))-INT(NOW()+B1),B1))
```

Mark, it is ugly, yes. ;-). In particular as "B1" is a formula,

(points needed for getting the next EE rank) / (12 month average of points) * 30

and I would hate it having to repeat that expression several times.

I've decided to take the input as a starting point, and do a mixture of "de-calculation" and formatting: I use the UDF

```
Function Days2Dec(days As Long) As Long
If days > 9 * 360 Then days = 9 * 360
Days2Dec = (days \ 360) * 10000 + ((days Mod 360) \ 30) * 100 + (days Mod 30)
End Function
```

which scales to a numeric ymmdd format, and then apply a cell format of "# ## ##" to make it look like "y mm dd". Of course the calculations are inaccurate, but I don't need more precision. And not doing string formatting in the UDF allows me to properly sort and use RANK and similar stuff on the result.
Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialI originally used multiple columns to arrive at the correct answer. The resulting formula is the result of substitution of cell references with those cells' contents. If you were willing to have some (probably hidden) columns, this wouldn't be nearly as ugly.

Your Days2Dec function reminds me of a function I wrote that calculates a patient's age at the time of their encounter. Combining month and day into a single number minimizes the required comparison operations.

Martin, your last code does not really change anything compared with what I posted.You're right, it's not much different but it did correct a problem where the >365 days part was incorrect. For example your code for 400 days produced "01a 04" while mine produced "01a 01".

In any case you're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.

Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014

The real answer is "you can't do that, but there are workarounds".

In my profile you'll find links to some articles I've written that may interest you.

Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.