+ Reply to Thread
Results 1 to 2 of 2

Custom Numeric Format: Show Integer Quotient and Remainder

  1. #1
    Registered User
    Join Date
    12-09-2010
    Location
    Denver, CO
    MS-Off Ver
    Excel 2007
    Posts
    1

    Custom Numeric Format: Show Integer Quotient and Remainder

    I have a number of months in a cell that I would like to display in years and months (for example, 30.50213 should display as "30 yr 06 mo"). Essentially, I am attempting to show the value of:

    =FLOOR(CONTENTS / 12)&" yr "&MOD(FLOOR(CONTENTS), 12)&" mo"

    I have tried the following format, which gets me close (change the cell contents into fractional years instead of fractional months):

    dd \y\r hh \m\o

    The above gets 2*months since there are 24 hours in a day. I have tried the following to try and get the months (hours) to appear in halves to no avail:

    dd \y\r hh ?/2 \m\o

    Any ideas how I can display my number divided by twelve as well as mod 12 in the same cell using custom numeric formats? Thanks,

    -Jeff

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Custom Numeric Format: Show Integer Quotient and Remainder

    Hello,

    with contents = 30.50213, how about

    =INT(contents)&" yr "&TEXT((MOD(contents,1)*24)/2,"0#")&" mo"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1