Heavy Nested IFs

[Jeffrey Lebowski]

Hi,

I would like some help using multiple nested IFs and concatenation to develop timestamps. See the attached file.

• Column A: The timestamp "code" (d=days, h=hours, m=minutes, s=seconds) I would like applied in concatenation.
• Column B: The time data, in hours.
• Column C: My current solution for the simpler nested IF combos.

Does anyone know if it is possible to get more complex with this current nested if combo? Specifically:

• Can there be more levels of detail, such as 10d6h45m?
  
• Currently instead of returning 1h or 1d, the formula returns 1h0m and 1d0h, respectively. Is there away of omitting 0 values?

I'm not sure if this is where it would be appropriate to use AND() or OR() in conjunction with the nested IFs. I am trying to really master the complex nested IF, for fun.

Thanks for the help!

 

[azumi]

Im confused what you after when you have the solution in column C

 

[Hui]

Alternatively

in D2: =B2/24
Copy down
Apply a Custom Number Format of
[>=1]d"d"h"h";[>=0.00069444] h"h"m"m";s"s"