# How do you format an Excel date to represent >31 days with hours, minutes and seconds

Let's say A1 contains a value which is the number of days in duration. Example:

``````57.121875
``````

That number represents the number of days.

I would like to use an excel format which ultimately makes it look like this:

``````57.02:55:30
``````

i.e. 57 days, 2 hours, 55 minutes and 30 seconds.

Now, if I were to simply interpret number as a DateTime value and use a format text such as: m/d/yy hh:mm:ss

Then Excel will display it as:

``````2/26/00 02:55:30
``````

Since internally Excel DateTime values are numerically the number of days since the start of the century (Date zero), this makes sense.

So to get the number of days since that date, and the start of the century, I can use the formula:

``````=DATEDIF(0, A1, "D")
``````

which would give me 57

And then I can further construct what I ultimately want using a formula:

``````=TEXT(DATEDIF(0, A1, "D"),"0")&"."&TEXT(A1,"hh:mm:ss")
``````

And I will get what I want, namely:

``````57.02:55:30
``````

The the problem is that that's a formula, it's not a format text. That formula actually constructs the value and produces text.

``````I would like to know if there's a way to do this as a number format only.
``````

The reason is because I would like to use it as a calculated column in Excel and then in pivot tables, and I don't want to lose the numeric'ness of it so that it can be further operated on. And I want to do it without an additional field.

• Did you find an answer to that? I am in the same situation. I need to format it showing total days but it wraps at 31 Jun 14, 2018 at 12:02
• Thanasis, I don't believe there's a way to do it. Jun 14, 2018 at 19:58

Just use custom format 'dd.HH:MM:SS'

• Actually, "dd.HH:MM:SS"; the OP wants a period after the number of days. Mar 25, 2015 at 21:53
• +1 because I think this is the closest solution but it will only work correctly for durations less than one month. Mar 25, 2015 at 22:06
• less than 32 to be exact, it's still 17th of January
– tbc
Mar 25, 2015 at 22:45
• I can't accept the answer. The part before the period can be large numbers. I don't see how I can introduce wrong numbers in the report by doing this. I'm very sorry. Mar 26, 2015 at 2:39
• OK, you don't have to accept the answer if it does't fix your problem. But you should `Edit` your answer to describe your other problem with an example, your 17.121875 works fine with my answer, while 57.121875 doesn't
– tbc
Mar 26, 2015 at 21:14

Kind of like the first answer, but I compute the days (int formula), use concatenate (&) use your period separator, then convert the decimal portion to a time.

``````=INT(A1)&"."&TEXT(A1,"hh:mm:ss")
``````
• Excerpt from question: "The problem is that that's a formula, it's not a format. That formula actually constructs the value and produces text. I would like to know if there's a way to do this as a number format only." Aug 3, 2019 at 7:11