SOLVED

Time calculation with decimal point issue

%3CLINGO-SUB%20id%3D%22lingo-sub-2069820%22%20slang%3D%22en-US%22%3ETime%20calculation%20with%20decimal%20point%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2069820%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Day%20Team%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20an%20Excel%20sheet%20that%20has%20daily%20time%20tracking%20of%20employees%2C%20and%20final%20summarized%20monthly%20tracking.%20I%20have%20set%20all%20my%20decimals%20as%202%20decimal%20points%2C%20and%20I%20specified%20in%20Excel%20Advanced%20options%20to%20set%20(precision%20as%20displayed)%20so%20excel%20only%20makes%20its%20calculation%20based%20on%20the%20displayed%202%20decimals.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20makes%20time%20calculation%26nbsp%3B(from-to)%20and%20round%20it%20either%20up%20or%20down%20to%20make%20it%202%20decimal%20places.%3C%2FP%3E%3CP%3EEveryday%20there%20is%20either%20a%20round%20up%20or%20a%20round%20down%20based%20on%20the%20actual%20value.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20in%20the%20final%20tracking%20sheet%20we%20put%20the%20(first%20time%20in%20first%20day%20-%20last%20time%20in%20last%20day)%20so%20Excel%20makes%20on%20big%20subtraction%2C%20and%20then%20rounds%20it%20up%20or%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20total%20time%20with%202%20decimals%20in%20(Summed%20Dailies)%20is%20going%20to%20be%20different%20from%20the%20final%20calculation%20due%20to%20the%20fact%20that%20rounding%20was%20done%20many%20time%20during%20the%20month%2C%20whereas%20the%20final%20is%20done%20one%20only.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20a%20sheet%20for%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20around%20this%3F%20VBA%20code%3F%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20that%20I%20need%202%20decimals%20at%20all%20time%2C%20and%20I%20need%20to%20set%20precision%20as%20displayed%20(mandatory)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20you%20can%20support.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2069820%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2069846%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20calculation%20with%20decimal%20point%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2069846%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F885337%22%20target%3D%22_blank%22%3E%40Awfimohammed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERounding%20on%20two%20decimal%20places%20means%20rounding%20on%200.01*24*60%20%3D%20about%2015%20minutes.%20Not%20sure%20that%20could%20be%20corrected%20if%20only%20to%20calculate%20final%20value%20independently%20of%20intermediate%20ones.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2069856%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20calculation%20with%20decimal%20point%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2069856%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F885337%22%20target%3D%22_blank%22%3E%40Awfimohammed%3C%2FA%3E%26nbsp%3BBut%20that's%20not%20so%20strange.%20The%20first%20calculation%20(not%20rounded)%20results%20in%202.145833333%20%2C%20thus%2C%20it%20becomes%202.15%20rounded.%3C%2FP%3E%3CP%3EThe%20other%20two%20calculations%20result%20in%200.29166666%20and%201.85416666%2C%20which%20round%20to%200.29%20and%201.85%20respectively%2C%20adding%20up%20to%202.14%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20you%20should%20use%20formulae%20like%20below%20to%20achieve%20what%20you%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DROUND(C6%2C2)-ROUND(B6%2C2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Good Day Team,

 

I am working on an Excel sheet that has daily time tracking of employees, and final summarized monthly tracking. I have set all my decimals as 2 decimal points, and I specified in Excel Advanced options to set (precision as displayed) so excel only makes its calculation based on the displayed 2 decimals.

 

Excel makes time calculation (from-to) and round it either up or down to make it 2 decimal places.

Everyday there is either a round up or a round down based on the actual value. 

 

However in the final tracking sheet we put the (first time in first day - last time in last day) so Excel makes on big subtraction, and then rounds it up or down.

 

The total time with 2 decimals in (Summed Dailies) is going to be different from the final calculation due to the fact that rounding was done many time during the month, whereas the final is done one only. 

 

Attached a sheet for example.

 

 

Is there any way around this? VBA code? 

Please note that I need 2 decimals at all time, and I need to set precision as displayed (mandatory) 

 

Hope you can support.

 

Thank you,

 

 

3 Replies

@Awfimohammed 

Rounding on two decimal places means rounding on 0.01*24*60 = about 15 minutes. Not sure that could be corrected if only to calculate final value independently of intermediate ones.

Best Response confirmed by Awfimohammed (Occasional Contributor)
Solution

@Awfimohammed But that's not so strange. The first calculation (not rounded) results in 2.145833333 , thus, it becomes 2.15 rounded.

The other two calculations result in 0.29166666 and 1.85416666, which round to 0.29 and 1.85 respectively, adding up to 2.14

 

Perhaps you should use formulae like below to achieve what you need.

 

=ROUND(C6,2)-ROUND(B6,2)

 

 

@Riny_van_Eekelen 

 

This actually works very well for my calculations. Thank you so much sir.