Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting Date Calc to days:hours:minutes (2000)

    I have calculated differences between dates. The results are numbers like: 2.5752 and 83.0162.
    I want to display these differences as days-hours-minutes, so I applied a custom numbering format of "d hh:mm". Afterward, the two numbers change to: 2 13:48 and 23 00:23.
    The first one is ok, but anytime the difference between dates exceeds 30 days, the custom formatted number is off.

    Can anyone please advise on a better way to achieve my goal? I greatly appreciate any advice.
    Mary

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Converting Date Calc to days:hours:minutes (2000)

    There is no elapsed "day" custom format

    Check out <post#=365159>post 365159</post#> for a text workaround (the answer will not be a number)

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Converting Date Calc to days:hours:minutes (2000)

    The "d" in a data format stands for "day of the month", so it can't be greater than 31. Put the integer part of the difference in one cell, and the fractional part in another cell. Say that the difference is in cell B1. In another cell, say B1, put the formula

    =INT(B1)

    and set the number format to General. In a third cell, say C1, put the formula

    =B1-INT(B1)

    and format this cell as "hh:mm".

  4. #4
    Lounger
    Join Date
    May 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Date Calc to days:hours:minutes (2000)

    You are awesome! I really like this workaround.

    Mary

  5. #5
    Lounger
    Join Date
    May 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Date Calc to days:hours:minutes (2000)

    Thanks for the pointer!

    Mary

Posting Permissions

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