# Number format problem for large amounts of tim

Copper Contributor

# Number format problem for large amounts of tim

I have a table with per line; Article (text), Quantity (integer or default value), unit time (in hours and minutes), total time (in number of months, number of days, number of hours, and number of minutes). The unit time can be 1 min, 5 min, 1 hour, 3 hours or 8 hours (1 table line per unit time quantity). The total time of the line is equal to the quantity multiplied by the unit time. Of course, at the bottom of the column, I add the total sum of all time totals for each line. The problem that arises concerns the format of the numbers for the time totals. It's one of two things.

If my total time is greater than 31 days and I have used a customized format without the number of months, the number of days displayed will be equal to the number of days exceeding the number of full months (for example, if my total is 33 days, the number of days displayed will be 2 (2 days beyond the 31-day month).

If this is not the case, there are two possible outcomes:

- If my customized format includes the number of months, the number of months is automatically set to 1, even if the cumulative time of my line is only one minute. Furthermore, if the accumulated time exceeds 1 month by even one minute, the number of months displayed will be 2, and so on.

- If my cumulative time is greater than 31 days and I've used a custom format without the number of months, the number of days displayed will be equal to the number of days exceeding the number of full months (e.g. if my total is 33 days, the number of days displayed will be 2 (2 days beyond the 31-day month).

Hence my question: Is there a simple way of solving this problem?

1 Reply

# Re: Number format problem for large amounts of tim

While Excel supports custom number formats for duration in seconds, minutes or hours, it does not support custom number formats in days or months. Using days and/or months in a number format always works as calendar days and months. The lowest calendar month is 1, not 0, so you never get 0 months.

You can replace a formula such as =SUM(B2:B5) with

=LET(s, SUM(B2:B5), INT(s)&" d "&TEXT(s, "hh ""h"" mm ""mn"""))

=LET(s; SOMME(B2:B5); ENT(s)&" j "&TEXTE(s; "hh ""h"" mm ""mn"""))