Forum Discussion
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.
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))
