Excel: convert seconds to dd Days hh:mm:ss format

Question

I want to convert seconds into dd days hh:mm:ss.

This is what I have: I have a cell (A1) with value 457003 and in Cell B1 I am doing =Text(A1/(24*60*60),"dd hh:mm:ss" and it is returning 05 06:56:43.

What i am looking for is 05 days 06:56:43.

Please help!!

Thank you

Answer 1

Use this formula:

=TEXT(A1/(24*60*60),"dd \d\a\y\s hh:mm:ss")

Though I would simply use the formula part and do a custom format of dd \d\a\y\s hh:mm:ss. That way the number can be used in later calculations.