CODEWITHSUNDEEP
excelexcel-formula
Joshua Dela Cruz
Joshua Dela Cruz

Reputation: 235

How can I change positive value to zero in Excel

I have two columns for the time in and time out of the employee, and I want to determine their lates and undertimes. But I cannot properly do it, I already can do the negative value to zero but I just want to know how to do the positive value to zero.

This is my formula =(G10-C10)*1440 then as per my column values it will show a positive value but I want the positive value become ZERO

Thanks!

Upvotes: 0

Views: 7072

Answers (2)

user4066647
user4066647

Reputation:

As an alternative to Jerry's answer, after applying your original formula, you can change the format of the cells to something like this:

"0";-0;0

  • The first 0 is for positive. The quotes around it make sure the zero gets displayed literally.

  • The second section (each section separated by a semi-colon ;) is for negative and the last one's for zero values.

  • The zero without quotes here means that one number should get displayed compulsorily.

Here's the output:

Number formatting

Upvotes: 1

Jerry
Jerry

Reputation: 71538

You could use IF, which would be the most popular choice:

=IF(G10-C10>0,0,(G10-C10)*1440)

Could be read as, 'if the difference is greater than 0 (positive), then put 0, otherwise put the difference itself'.

Or a little less common, but simpler:

=MIN((G10-C10)*1440,0)

Upvotes: 1

Related Questions