Comma Separator for Elapsed Hours [h]

[RobertN]

I am summing elapsed hours that total more than 24, so I am using [h]. Those that total more than one thousand hours, display without a comma separator. Is there a way to format these to use the comma separator?

 

[jasonb75]

Possibly with a formula converting it to a text string in another cell, but not by formatting the original time value.

Something like =TEXT(INT(A1*24),"#,#")&TEXT(MOD(A1,1),"\:mm:ss") where A1 contains the time to format.

 

[RobertN]

Possibly with a formula converting it to a text string in another cell, but not by formatting the original time value.

Something like =TEXT(INT(A1*24),"#,#")&TEXT(MOD(A1,1),"\:mm:ss") where A1 contains the time to format.

Thank you, Jason. I forgot to mention that I already have a formula in these fields [=SUMIFS(TimeSheet_1[Hours],TimeSheet_1[Date],"<>",TimeSheet_1[Date],">=2/1/2020",TimeSheet_1[Date],"<$D$4")]. I am trying to come up with a custom formatting sequence that might add the comma separator or is there a way to stack these formulas?

 

[jasonb75]

This should do it

=TEXT(INT(SUMIFS(TimeSheet_1[Hours],TimeSheet_1[Date],"<>",TimeSheet_1[Date],">=2/1/2020",TimeSheet_1[Date],"<$D$4") *24),"#,#")&TEXT(MOD(SUMIFS(TimeSheet_1[Hours],TimeSheet_1[Date],"<>",TimeSheet_1[Date],">=2/1/2020",TimeSheet_1[Date],"<$D$4") ,1),"\:mm:ss")

Note that using this will mean that the result of the formula is no longer a valid time format, so any other formulas, charts, pivot tables, etc. that refer to the result of this formula will no longer work.

 

[RobertN]

This should do it

=TEXT(INT(SUMIFS(TimeSheet_1[Hours],TimeSheet_1[Date],"<>",TimeSheet_1[Date],">=2/1/2020",TimeSheet_1[Date],"<$D$4") *24),"#,#")&TEXT(MOD(SUMIFS(TimeSheet_1[Hours],TimeSheet_1[Date],"<>",TimeSheet_1[Date],">=2/1/2020",TimeSheet_1[Date],"<$D$4") ,1),"\:mm:ss")

Note that using this will mean that the result of the formula is no longer a valid time format, so any other formulas, charts, pivot tables, etc. that refer to the result of this formula will no longer work.

You're a genius, Jason. Thanks so much!