Format cell containing elapsed time to show days hours and minutes

[NewOrderFac33]

Good morning,
I have a cell that contains the following value: 01/01/1900 06:58:27 which equates to 1 day 6 hours and 58 minutes.
Is there any formatting sequence I can use to get the cell to display "1d 6h 58m", rather than the "30h 58m" that I have at the moment, using [hh]"h" mm"m"
I'm using Excel 2016.
Thanks in advance
Pete

 

[mole999]

I would play with checking the length value, so If greater than 1 get the integer value for days, then look at the remainder. Don't have anything quick to hand

 

[mole999]

Start with formR > https://www.mrexcel.com/forum/excel...erting-number-days-hours-minutes-seconds.html

 

[NewOrderFac33]

Excel version (answer in a different cell)

=INT(N36) & "d " & MID(TEXT(N36, "hh mm ss"), 1, 2) & "h " & MID(TEXT(N36, "hh mm ss"), 4, 2) & "m " & MID(TEXT(N36, "hh mm ss"), 7, 2) & "s "

VBA version (replace existing values in current selection)

Sub ElapsedTimesToStrings()
    Dim MyCell As Range
    Dim MyString As String
    For Each MyCell In Selection
        With MyCell
            .FormulaR1C1 = Int(MyCell.Value) & "d " & Mid(Format(MyCell.Formula, "hh mm ss"), 1, 2) & "h " & Mid(Format(MyCell.Formula, "hh mm ss"), 4, 2) & "m " & Mid(Format(MyCell.Formula, "hh mm ss"), 7, 2) & "s "            
            .HorizontalAlignment = xlRight
        End With
    Next
End Sub

Thanks for your suggestions that helped me to arrive at this!

Pete