I am trying to find a formula which will work out an allowance amount based on the time it has taken for a task to be completed.
Here is an example, in cell:
A1: I have an allowance value of 150 (which does not change)
B1: I have the time it has taken for the task to be performed
C1: I want a formula to return a value based on the criteria below
Criteria 1: 6hrs or less return 20% of the allowance =IF(AND(B1>=0.01,B1<=5.99),A1*0.2,"")
Criteria 2: Between 6hrs and 12hrs, return 50% of the allowance =IF(AND(B1>=6,B1<=11.99),A1*0.5,"")
Criteria 3: Between 12hrs and 24hrs, return 100% of the allowance =IF(AND(B1>=12,B1<=23.99),A1*1,"")
Criteria 4: First 24hrs, 100% of the allowance and for each successive 12hrs add 50% of the allowance (on top of the 100% for the first 24hrs)
If possible I would like one formula to cover all those criterias into C1 cell. As you can see, I have a formula for the first three criterias but not the last one.
It would be great if someone can help. Many thanks in advance :)
Ab
Aucun commentaire:
Enregistrer un commentaire