• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help: Multiple functions in one cell

alibilal

Member
Hi, i need some help in designing a worksheet .. i have tried to be as clear as i can be in the excel sheet that i am attaching in. Please note that there are multiple conditions for cell "D" as mentioned in the excel file. I hope you be able to help me.
 

Attachments

Hui

Excel Ninja
Staff member
Try:
C3: =MIN(7.6,B3-A3)*(B3>6)
D3: =MIN(2,B3-A3-C3)
E3: =B3-A3-C3-D3

If this doesn't work

Can you tell us what each column represents
I suspect they are work hours and then How much is normal Time, Time and a Half etc
 

alibilal

Member
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

Member
I was just checking .. but in the scenario 3 ... if you change the value of B9 from 6 to 7 .. it all pops up in C9 rather .. C9=1, D9=2 & E9=1.25
 

Attachments

alibilal

Member
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. :)
 

Attachments

Hui

Excel Ninja
Staff member
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
 

alibilal

Member
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
I apologise for inconvenience caused but i have tried to explain everything as clearly as i can, but having said that, i will be more elaborative next time. Once again .. sory for the trouble caused.
 

Hubert

New Member
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.
 

alibilal

Member
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
 

alibilal

Member
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"
 

alibilal

Member
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"

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".
 

alibilal

Member
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
"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".
 

alibilal

Member
Guess this is what you are looking for...
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.
 

alibilal

Member
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"
 

Nebu

Excel Ninja
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??
 

alibilal

Member
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??
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
 

alibilal

Member
Thanks Nebu .. it really looks good .. I will still thoroughly check and see if all options work and then update you. Once again I thank you for all your help.
 
Top