# Problem with If function whith values in hours

Copper Contributor

# Problem with If function whith values in hours

Hi guys,

Currently, for personal reasons I have to leave work quite frequently and I decided to create an Excel sheet to keep track of the hours that I am absent from work and must recover.

To give you an idea, the sheet is like this:

The problem that I have is with the function that calculates my break time (I column). This function should show me 0:30:00 in the "I" column if all of these three conditions are met:

-The departure time (F column) must be less or equal to 10:00:00

-The arrival time (G column) must be greater or equal to 10:30:00

-The duration of departure (H column) must be less or equal to 3:13:00 hours

If one of the conditions is not met, it should display 0:00:00

The formula that I currently have is the following:

=IF(AND(F2<="10:00:00";G2>="10:30:00";H2<="3:13:00");"0:30:00";"0:00:00")

With this fucntion all the "I" column show me 0:00:00, when for example, in the sixth column it should show me 0:30:00

Could any of you tell me how to correct this? Because right now I'm lost and I don't know how to solve it.

# Re: Problem with If function whith values in hours

Time values are not text strings. You can use the TIMEVALUE function to convert a text string such as "10:00:00" to a real time.

=IF(AND(F2<=TIMEVALUE("10:00:00"); G2>=TIMEVALUE("10:30:00"); H2<=TIMEVALUE("3:13:00")); TIMEVALUE("0:30:00"); TIMEVALUE("0:00:00"))

Alternatively, use the TIME function:

=IF(AND(F2<=TIME(10;0;0); G2>=TIME10;30;0); H2<=TIME(3;13;0)); TIME(0;30;0); TIME(0;0;0))