Sum and round time in the same cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2021393%22%20slang%3D%22en-US%22%3ESum%20and%20round%20time%20in%20the%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2021393%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20a%20time%20sheet%20that%20has%3C%2FP%3E%3CUL%3E%3CLI%3Eclock-in%20in%20cell%20B9%3C%2FLI%3E%3CLI%3Etime%20for%20lunch%20in%20cell%20c9%3C%2FLI%3E%3CLI%3Eclock%20out%20in%20cell%20d9%3C%2FLI%3E%3CLI%3Eformula%20for%20total%20time%20worked%20in%20cell%20e9%26nbsp%3B%20which%20is%20currently%3A%26nbsp%3B%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ESUM%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3ED9%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E-%3C%2FSPAN%3E%3CSPAN%3EB9%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E-%3C%2FSPAN%3E%3CSPAN%3EC9%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%2F%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E24%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3EWhat%20I%20need%20is%20a%20formula%20to%20also%20have%20the%20results%20in%20E9%20rounded%20to%20the%20nearest%2015%20minutes.%26nbsp%3B%20Any%20suggestions%3F%26nbsp%3B%20I%20know%20how%20to%20do%20it%20if%20I%20want%20the%20rounded%20results%20in%20another%20cell%20but%20I'd%20like%20it%20to%20be%20in%20the%20same%20cell%20(E9)%20instead.%26nbsp%3B%20Screenshot%20is%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2021393%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2021465%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20and%20round%20time%20in%20the%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2021465%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F913864%22%20target%3D%22_blank%22%3E%40jeananotgina%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20wrap%20your%20formula%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMROUND(%20SUM(...)%2C%20%2200%3A15%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20apply%20time%20format%20to%20the%20result.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2021479%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20and%20round%20time%20in%20the%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2021479%22%20slang%3D%22en-US%22%3E%3CP%3E%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%26nbsp%3BTell%20me%20how%20one%20acquires%20this%20knowledge!!!%26nbsp%3B%20Thank%20you%20so%20much%2C%20seriously%2C%20I%20had%20scoured%20the%20internet.%26nbsp%3B%20Much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, I have a time sheet that has

  • clock-in in cell B9
  • time for lunch in cell c9
  • clock out in cell d9
  • formula for total time worked in cell e9  which is currently: =SUM(D9-B9)-C9/24

What I need is a formula to also have the results in E9 rounded to the nearest 15 minutes.  Any suggestions?  I know how to do it if I want the rounded results in another cell but I'd like it to be in the same cell (E9) instead.  Screenshot is attached

 

Thanks in advance.

3 Replies

@jeananotgina 

You may wrap your formula like

=MROUND( SUM(...), "00:15")

and apply time format to the result.

@Sergei Baklan Tell me how one acquires this knowledge!!!  Thank you so much, seriously, I had scoured the internet.  Much appreciated.

@jeananotgina , you are welcome.

By the way, if google "excel round time" first will be returned is Excel formula: Round time to nearest 15 minutes | Exceljet