Use the formula "if" to calculate a certain time interval

Asked

Viewed 5,212 times

3

I’m trying to create a condition but I’m having trouble, Can someone help me?

if the cell number C15 is between -00:10 and 0:10 (minutes) the cell D15 should appear 0:00, otherwise the cell D15 should show the same cell number C15. How to mnetar this condition.

Thanks in advance

excel

  • Hello, already managed to solve the problem of not being possible to put directly the hours negatively ?

  • Ricardo, welcome to Soft. Go to Ajuda and take the Tour, to make better use of the resources here and see how to deal with the questions and answers. Try to be clear es your questions and put part of the already made, code, formulas or part of the spreadsheet (you can insert picture).

  • Hello Ricardo, here are some models that have something similar if you can help. https://github.com/excelguru/controle-ponto Abs

  • Hi @Ricardo, the answer I gave served you in some way? If yes, please mark the answer in the "check" below the voting score of the answer. Grateful.

1 answer

4

The Excel does not work (does operations) with negative hours, you are probably treating the time as a "string" (text format, e.g.: ="00:05").

In this case, even for a "string", the Excel understands that it is time to be positive, either in the standard format "00:00:00" or another, if your list of time formats "00:00".

So how do you get in "00:10" minutes and "-00:10" minutes, values are between 0 and 10 in "absolute values", do the following:

  1. Take the negative sign out of the hour to make the comparison

  2. In another cell, take the "Value" of this hour, like this:

    =Value(A1)

  3. The result will be a number representing this time for the Excel. Like "00:10" minutes is equal to 0,000115740740740741, if the cell is between this value and 0, the time is in the range you want.

I took the test using the time like this:

="00:00:09"

="00:00:10"

="00:00:11"

And the formula:

=Se(Valor(C15)<0,000115740740740741;"00:00:00";C15)

or better...

=Se(Valor(C15)<Valor("00:00:10");"00:00:00";C15)

Just adapt to your case and apply!

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.