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

1. 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. 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. 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. Re: Converting Date Calc to days:hours:minutes (2000)

You are awesome! I really like this workaround.

Mary

5. 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
•