Time function alternative to concatenate 3 cells into HH:MM:SS-CodePudding

i wonder if there is another excel fuction i can use to combine 3 numbers into time formated as (hh:mm:ss).

The time function works great but example below shows a problem that it cant go beyond 24h.

Ive tried to reformat the cells as [HH]:MM:SS but this sadly dont seem to work. Ive found information online that this is due to a restriction for the time function.

Formula:

=TIME(

   Hour:
   FLOOR((20/3   (M7 - 20) / (3 * (IF(M7 < 20,ts_0,Y7) Z7)))), 

   Minute:
   ROUND(MOD(((20/3)   ((M7 - 20) / (3 * (
   IF(M7 < 20,ts_0,Y7)   Z7))) - FLOOR((20/3   (M7 - 20) / (3 * (
   IF(M7 < 20,ts_0,Y7) Z7))))) * 60;60)),

   Second:
   ROUND(MOD(((20/3)   ((M7 - 20) / (3 * (
   IF(M7 < 20,ts_0,Y7)   Z7))) - FLOOR((20/3   (M7 - 20) / (3 * (
   IF(M7 < 20,ts_0,Y7) Z7))))) * 3600;60))

)

M7= (Int) Some distance, 15 or 25 for example.
Z7= (Percent) Some percentage, 5% or 25 for example.
Y7= (Float) For ex. 1,0
ts_0= (Float) For ex. 1,0

Problem:

Example good Result: 01:29:27 (01,29,27)

Expected Output: 01:29:27
Actual Output: 01:29:27

Example bad Result: 01:27:17 (25,27,17)

Expected Output: 25:27:17
Actual Output: 01:27:17

CodePudding user response：

I found an solution!

=TEXT(CONCATENATE(':',,A1,B1;C1);'[HH]:MM:SS')

Then change each cell reference (A1,B1,C1) to your formula.

So for me it looks like this,

Function text:

=TEXT(

Function concatenate:

=concatenate(":",,

A1:

FLOOR((20/3   (M7 - 20) / (3 * (IF(M7 < 20,ts_0,Y7) Z7)))),

B1:

 FLOOR(MOD(((20/3)   ((M7 - 20) / (3 * (
 IF(M7 < 20,ts_0,Y7)   Z7))) - FLOOR((20/3   (M7 - 20) / (3 * (
 IF(M7 < 20,ts_0,Y7) Z7))))) * 60;60)),

C1:

ROUND(MOD(((20/3)   ((M7 - 20) / (3 * (
IF(M7 < 20,ts_0,Y7)   Z7))) - FLOOR((20/3   (M7 - 20) / (3 * (
IF(M7 < 20,ts_0,Y7) Z7))))) * 3600;60))

concatenate end parenthases:

text ending and formating:

;"[HH]:MM:SS")

Note, Important to include hour format within brackets if you want to go beyond 24h.

CodePudding user response：

Your formula seems awfully complicated.

Try: =A2/24 B2/1440 C2/86400

Custom Format the cell as [hh]:mm:ss