# calculate time with if condition

0

in 1st sheet i am using this formula to calculate time

=IF(E3<>"",E3-D3,IF(D3<>"",D3,""))

in 2nd sheet i am using this

=IF(Daily!I3<>"",Daily!I3,"")

Now in 3rd I need a formula wich calculate overtime >8 hours, other cells should be blank, in-case of sunday then get all caculated hours from 2nd sheet. On other days calculated time should >8.

Here 8 Hours are duty time which is fixed.

0

``=IFERROR(Monthly!D2-IF(WEEKDAY(D\$1,1)=1,0,8/24),0)``

Note that WEEKDAY(D\$1,1) returns the day number based on a week starting on Sunday. Therefore the test IF(WEEKDAY(D\$1,1)=1 determines if the weekday is a Sunday. Further note that Monthly!D2 contains hours in date format, inherited from Daily!I3. In that format 1 hour = 1/24. So, the above formula takes the value of Monthly!D2 and deducts 8 hours from it, unless it's a Sunday in which case 0 is deducted.

This formula will result in an error if Monthly!D2 contains a letter instead of a number. Observe at this point that "" (possibly inserted by your formula there) would also cause such an error but 0 wouldn't. The IFERROR() function catches the error and inserts a 0 in that case. You may like to replace this with "" in order to have a blank cell. My recommendation would be to keep the zero so as to be able to do calculations with it but suppress its display using the cell format or even suppress display of zeroes globally for the entire worksheet.

In the case of your worksheet there would be no immediate difference because you use the SUBTOTAL() function for calculation which converts strings to zeroes. SUM() does the same. The null string would cause an error if you were attempting to do multiplications with it, such as calculating overtime pay.

### Discussion

actually i've different shift as under,
06 to 14
14 to 22
22 to 06
22 to 07
your formula is not working on 22 to 06
i am using
=IF(E3>=D3,E3-D3,24-D3+E3)
this one calculating perfectly with both decimal and time format. Problem in this formula is blank cells without time it is calculating zero instead of blank. if cells contain R, C, or any other text it is giving error.
I need if cell contains time then time, if cell contains text then text, if cell blank then result should be blank.
Thanks for help
Kalim Oct 5, '18 at 2:53 am