Hi Expert,I need a help for Overtime calculation, we have a data where the logic is if our out time is 16:30 then we don't allow Overtime till 17:30 if out time is 17:30 then we calculate 1 Hrs Overtime. After that we rounding up this overtime for every 30 minutes added to this. Need a formula to calculate this concept. Please see the excel file need the figures mentioned in highlighted cell.for 01/06/17 result 0.
only if oit time is more than 17:00
it should 0.5
thanks
Hi Mey,
Your question has remained unanswered for so long because it's not very clear. When posting a question it is best to not only provide your sample data, but also provide some example results you'd expect to see so that those helping you have a complete understanding of the desired result.
Please try this formula (see column N of the file attached):
=IF(E10=TIME(17,30,0),TIME(1,0,0),IF(E10>TIME(17,30,0),MROUND(E10-TIME(16,30,0),"0:30"),0))
Note: you need to convert the data in columns, D and E into time. They're currently Text and Excel can't work with Text in formulas like this.
To convert the text to time, enter 1 in any empty cell > copy that cell > select columns D and E > Paste Special > Select radio buttons: Values and Multiply
If that isn't what you wanted then we'll need some clearer instructions and examples with answers.
Mynda
Thanks,I got it
