CODEWITHSUNDEEP
exceldecimalroundingdataformat
miasekk
miasekk

Reputation: 37

Rounding numbers based on decimals value excel

I have problem in Excel which i couldn't handle on my own. So, I have a datasheet in "dd.mm.rrrr hh:mm" format, which shows when a particular process has been finished. When i want to subtract these values to get info how long did it take i will get results like: 6,666667 or 3,33333 or 7,14242.

So i would like to extract integer and then, basing on condition add 0 or 0,5 or 1 to this value. Something like that: If decimal is higher than 0,5 -> add 1, If decimal is between 0,3 and 0,5 then add 0,5, If decimal is lower than 0,3 then add 0 to Integer.

Upvotes: 0

Views: 70

Answers (2)

miasekk
miasekk

Reputation: 37

Excel screenshot

Sorry for a confusion. I would like to clarify my problem. My company works on 2 shifts 6:00 - 14:00 and 14:00 - 22:00. We have processes. I would like to get calendar days of subtract: A) Last Operation and Entrance times and then compare this time to added consecutive difference between sum of consecutive subtracting: B) Entrance and Operation2, operation2 and operation3, operation 3 and operation 4 etc. But when I do It my A) time differ from B) time. I have no idea how to deal with it.

Upvotes: 0

teylyn
teylyn

Reputation: 35915

Try a formula along these lines:

=A1+IF(MOD(A1,1)>0.5,1,IF(MOD(A1,1)>=0.3,0.5,0))

enter image description here

Upvotes: 1

Related Questions