Format number of days as time span

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).
LVL 73
QlemoBatchelor, Developer and EE Topic AdvisorAsked:
Who is Participating?
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.

Martin LissOlder than dirtCommented:
Do you have a workbook that you could post?
0
QlemoBatchelor, Developer and EE Topic AdvisorAuthor Commented:
There is not much to post to show, or much too much. For testing, just put the numbers 1, 30, 31, 60, 70 etc. in a column, and apply formatting.

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).
0
Martin LissOlder than dirtCommented:
I downloaded the workbook from that article and entered my expert name in B1, but it looks like I can't help since I get the following error when I try to do anything. It may be caused by the fact that I'm running Windows XP as a virtual OS on an iMAC but I've never had that problem before.

Error
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You are not alone Martin, I also got the same error.
0
Martin LissOlder than dirtCommented:
@Qlemo: I assume that you have a working workbook. Can you post it?
0
QlemoBatchelor, Developer and EE Topic AdvisorAuthor Commented:
That workbook does not work anymore, because of the many changes EE made. It was just to show what is behind this question. I didn't expect you to really try it ;-).
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.
0
Martin LissOlder than dirtCommented:
You can use the DUE user defined function (for Days Until Event) and the nice YearsMonthsDays function I found on the web.

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

Open in new window

0
Martin LissOlder than dirtCommented:
I just noticed on the source site that the code is from our own matthewspatrick. I will message him and ask him to post here so that if you like the solution he and I can share the points.
0
QlemoBatchelor, Developer and EE Topic AdvisorAuthor Commented:
Looks oversized for such a "simple" task, but using a UDF is probably the best option.  Remember, this is for statistical forecast, and does not need to be accurate to the day. A simple "30 days per month" assumption is sufficient. Something like
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

Open in new window

(with a slight change).
But I have to usual issue that I need the unformatted value for other calculations, and do not want to have another row just for keeping the real value; hence a conditional format has been used. Let's see if you can come up with something, if not, I'll probably drop the idea and stay with the display of days, or the ETA instead.
0
Martin LissOlder than dirtCommented:
Your FmtDaysRemain UDF refers to > 90 but your question talks about > 60 so that needs resolving. I'm also confused about what you want to see. Please let me know for 1000 days, 370 days, 366 days, 61 days and 30 days.
0
QlemoBatchelor, Developer and EE Topic AdvisorAuthor Commented:
Martin, it is less about the exact details of formatting, but about the how to deal with such "needs" in a more generic fashion. I'm able to modify the code myself accordingly.
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

Open in new window

0
Martin LissOlder than dirtCommented:
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

Open in new window

0
aikimarkCommented:
I know this is butt ugly, but it does seem to get close to the results you seek Q
=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))

Open in new window

0
QlemoBatchelor, Developer and EE Topic AdvisorAuthor Commented:
Martin, your last code does not really change anything compared with what I posted.

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

Open in new window

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.
0

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 trial
aikimarkCommented:
@Q

I 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.
0
QlemoBatchelor, Developer and EE Topic AdvisorAuthor Commented:
Mark, it is not worth it to add more columns (in fact, they would be rows here) just to get something of doubtful meaning anyway :D. But of course this is always my first advice, in particular with unexperienced Excel users trying to build proper formulas with some complexity.
0
Martin LissOlder than dirtCommented:
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
0
QlemoBatchelor, Developer and EE Topic AdvisorAuthor Commented:
That's correct, there was a flaw in the calculations, so I switched to 360 days per year instead. It would have spared me some time if you had said that earlier - needed some research myself to see that bug ;-)
0
QlemoBatchelor, Developer and EE Topic AdvisorAuthor Commented:
As I have used the idea of an UDF for (pre-)formatting purposes, which was the idea of Martin, I'm assigning some points.
The real answer is "you can't do that, but there are workarounds".
0
Martin LissOlder than dirtCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.