Help: Multiple functions in one cell
- Thread starter alibilal
- Start date
Thanks alot Hui .. you are the Man .. You are dot on. Perfect .. and yes its time sheet for sure (normal time is 7.6, time and half is max 2, double time is anything over - and this changes for Saturday as there is only time and half and double time) but as i am unable to figure out how to calculate time so i have tried doing it in decimals. Once again i thank you for helping me as i am just a beginner and was using so many cells outside the table to get some workable solution.
Alibilal
You have been drip feeding us with information
If you give us all the information upfront it only helps us help you
Originally there was no mention of hours or times at all
I sumised that
When posting questions it is imperative that you tell the people what the problem is and explaining it in as much detail as possible, this only makes it easier for us to help you
You have been drip feeding us with information
If you give us all the information upfront it only helps us help you
Originally there was no mention of hours or times at all
I sumised that
When posting questions it is imperative that you tell the people what the problem is and explaining it in as much detail as possible, this only makes it easier for us to help you
i have given some examples in the excel file with different combinations of A & B values and their expected out comes. The sum of C+D+E is always equal to B-A. I hope i am not making it too difficult for you to understand.
Is there something wrong in your example, say D27? since it starts from 5 which only 1 hr goes to 6, not 2.
I tried to understand your logic and made the formula in Row9:
C9: =MIN(7.6,B9-A9-D9-E9)
D9: =MIN(2,MIN(6,B9)-A9)
E9: =(MIN(6,B9)-A9-D9)+MAX(0,B9-MIN(6,B9)-7.6)
fyi.
as discussed earlier, D7 is related to C7, as C7 can never be more than 7.6 (7hr and 36min) .. so any thing over that is to fall in D7 but at the same time D7 can not be more than 2 so the balance left over from C7 and D7 comes in E7
"A" is start time "B" is end time. "C" is regular hrs which cannot exceed 7.6 (7hrs and 36mins). "D" is time and half and cannot exceed 2hrs, if the "A" (start time) is before 6am, then the difference before 6am is suppose to come in "D". If the the difference before 6am is less than 2hr then it means that there is room in "D" for more till it reaches a total of "2hrs". So any thing exceeding "C" (regular hrs after 6am) can come in "D" (provided it does not exceed 2hr). All the remaining hrs can fall in "E" as double time. e.g if "A" (start time) is 5am and "B" (end time) 18 then "C" is going to be 7.6hrs and "D" 2hrs (i.e 1hr is the difference before 6am and balance 1hr is excess from "C"). Every thing else come in "E"
"A" is start time "B" is end time. "C" is regular hrs which cannot exceed 7.6 (7hrs and 36mins). "D" is time and half and cannot exceed 2hrs, if the "A" (start time) is before 6am, then the difference before 6am is suppose to come in "D". If the the difference before 6am is less than 2hr then it means that there is room in "D" for more till it reaches a total of "2hrs". So any thing exceeding "C" (regular hrs after 6am) can come in "D" (provided it does not exceed 2hr). All the remaining hrs can fall in "E" as double time. e.g if "A" (start time) is 5am and "B" (end time) 18 then "C" is going to be 7.6hrs and "D" 2hrs (i.e 1hr is the difference before 6am and balance 1hr is excess from "C"). Every thing else come in "E"
Added Info:
Rates are not different for Sat, Sun or Public holiday but having said that the only difference for Saturday would be that "C" will be non functional and the values fall in "D" and "E" as per normal conditions. For Public Holidays, "C" & "D" will be non functional and all the values will fall in "E".
"A" is start time "B" is end time. "C" is regular hrs which cannot exceed 7.6 (7hrs and 36mins). "D" is time and half and cannot exceed 2hrs, if the "A" (start time) is before 6am, then the difference before 6am is suppose to come in "D". If the the difference before 6am is less than 2hr then it means that there is room in "D" for more till it reaches a total of "2hrs". So any thing exceeding "C" (regular hrs after 6am) can come in "D" (provided it does not exceed 2hr). All the remaining hrs can fall in "E" as double time. e.g if "A" (start time) is 5am and "B" (end time) 18 then "C" is going to be 7.6hrs and "D" 2hrs (i.e 1hr is the difference before 6am and balance 1hr is excess from "C"). Every thing else come in "E"
Added Info:
Rates are not different for Sat, Sun or Public holiday but having said that the only difference for Saturday would be that "C" will be non functional and the values fall in "D" and "E" as per normal conditions. For Public Holidays, "C" & "D" will be non functional and all the values will fall in "E".
hi Nebu, Thanks for your reply. I will just check and get back.
hi Nebu, I highly appreciate your reply .. but is it possible to change all the values to decimal instead of time for e.g. (for 6.45 i can do 6.75 or 2.15pm = 14.25) as this will help me thoroughly check.
While checking your file .. if you change the value of "A" to 5 (or anything less than 6) and the value of "B" to a number that the sum of "B-A" is less than 7.6 (7.36hrs) then any thing before 6am is over time so it should fall in "D" & "E" and the excess from 6am till what ever time comes in as "C=<7.6", "D=<2", & "E= all the excess"
Hi Ali:
For changing the number format just format the cells to general, the formula will still work.
Regarding your second query I did not understand what do you meant by less than 6, if the start time is less than 6 say for eg:5, the extra one hr should go to column D or E is that what you meant??
For changing the number format just format the cells to general, the formula will still work.
Regarding your second query I did not understand what do you meant by less than 6, if the start time is less than 6 say for eg:5, the extra one hr should go to column D or E is that what you meant??
If the value in "A" is less than 6 then its considered as overtime .. so all the over time should go in "D" or "E" as the case be as "D" cannot have more than 2hrs