Add multiple cells without changing the original format

%3CLINGO-SUB%20id%3D%22lingo-sub-3342860%22%20slang%3D%22en-US%22%3EAdd%20multiple%20cells%20without%20changing%20the%20original%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3342860%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3CBR%20%2F%3E%3CBR%20%2F%3EIm%20trying%20to%20add%20multiple%20cells%20which%20has%20%3CSTRONG%3E%3DTEXT(cellvalue%2C%20%22%5Bh%5D%3Amm%3Ass%22)%3C%2FSTRONG%3E%20formula.%20I%20tried%20%3CSTRONG%3E%3Dsum(range)%3C%2FSTRONG%3E%20but%20end%20result%20is%200.%20but%20I%20can%20able%20to%20add%20only%20when%20I%20select%20with%20individual%20cells%20eg%3A%20%3CSTRONG%3E%3DSUM(cell%2Bcell)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESo%20I%20used%20%3CSTRONG%3E%3DValue(Text(cellvalue%2C%22%5Bh%5D%3Amm%3Ass%22))%3C%2FSTRONG%3E%20now%20format%20is%20changed%20to%20numbers%20and%20able%20to%20calculate%20with%20%3CSTRONG%3E%3DSum(range)%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3EMy%20point%20is%20I%20want%20my%20cells%20to%20be%20in%20this%20format%3CSTRONG%3E%3DTEXT(cellvalue%2C%20%22%5Bh%5D%3Amm%3Ass%22)%3C%2FSTRONG%3E%2C%20and%20sum%20with%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20there%20any%20way%20we%20can%20do%20this%3F%20Kindly%20help%20me%20out%20through%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-05-09%20at%204.52.58%20PM.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F370175iB72A93788E0B63E2%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-05-09%20at%204.52.58%20PM.png%22%20alt%3D%22Screenshot%202022-05-09%20at%204.52.58%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3342860%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Scripts%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3343741%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20multiple%20cells%20without%20changing%20the%20original%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3343741%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1384750%22%20target%3D%22_blank%22%3E%40ashwingopal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhy%20not%20to%20apply%20custom%20format%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20358px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F370189i4DBA47ACC87A78C0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Einstead%20of%20converting%20to%20texts%3F%20Sum%20of%20texts%20is%20always%20zero.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3343924%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20multiple%20cells%20without%20changing%20the%20original%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3343924%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1384750%22%20target%3D%22_blank%22%3E%40ashwingopal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20your%20purpose%20in%20using%20TEXT%2C%20in%20the%20first%20place%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20might%20be%20the%20correct%20thing%20to%20do%2C%20if%20your%20purpose%20is%20to%20ensure%20that%20the%20result%20of%20some%20time%20calculation%20is%20the%20exact%20internal%20(binary)%20representation%20of%20the%20time%20rounded%20to%20the%20second.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20might%20be%20necessary%20in%20order%20to%20ensure%20that%20lookups%20and%20matches%20work%20correctly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20in%20that%20case%2C%20you%20should%20ensure%20that%20the%20result%20is%20numeric.%26nbsp%3B%20One%20way%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%3CFONT%20size%3D%224%22%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23DF0000%22%3E--%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FFONT%3ETEXT(cellref%2C%20%22%5Bh%5D%3Am%3As%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20either%20case%2C%20format%20either%20the%20original%20cell%20and%2For%20the%20TEXT%20cell%20as%20Custom%20%5Bh%5D%3Amm%3Ass%20.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3350184%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20multiple%20cells%20without%20changing%20the%20original%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3350184%22%20slang%3D%22en-US%22%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%3CBR%20%2F%3EThe%20contents%20in%20the%20excel%20is%20processed%20through%20JavaScript%20so%20I'm%20using%20%3DText%20formula%20for%20formatting.%20Im%20trying%20to%20find%20the%20your%20suggestion%20via%20JavaScript.%3CBR%20%2F%3EThank%20you%3CBR%20%2F%3EAshwin%3C%2FLINGO-BODY%3E
New Contributor

Hi All,

Im trying to add multiple cells which has =TEXT(cellvalue, "[h]:mm:ss") formula. I tried =sum(range) but end result is 0. but I can able to add only when I select with individual cells eg: =SUM(cell+cell)

So I used =Value(Text(cellvalue,"[h]:mm:ss")) now format is changed to numbers and able to calculate with =Sum(range).

My point is I want my cells to be in this format=TEXT(cellvalue, "[h]:mm:ss"), and sum with range.

 

is there any way we can do this? Kindly help me out through this

 

Screenshot 2022-05-09 at 4.52.58 PM.png

2 Replies

@ashwingopal 

Why not to apply custom format

image.png

instead of converting to texts? Sum of texts is always zero.

 

Hi @Sergei Baklan,
The contents in the excel is processed through JavaScript so I'm using =Text formula for formatting. Im trying to find the your suggestion via JavaScript.
Thank you
Ashwin