calculate time with if condition


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


in 2nd sheet i am using this 


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.




As for the formula you asked about, here is my suggestion.


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.



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 
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
My mistake. I apologise for having offered an improvement on your formula which you had not asked for. However, your complaint isn't related to your original question, either. I think it is a new question altogether. Therefore I shall now remove my suggestion regarding the formula you find faulty. Please then judge my answer based on how helpful it is in regard to your original question. If it solves your problem please acept it. As for your new problem, I understand it and find it interesting. Put it in a new question, with only the Daily sheet attached, and I shall be pleased to help you find a solution.
Variatus (rep: 1850) Oct 5, '18 at 6:00 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login