Sep 07 2019 10:24 AM
so im trying to sort out my expenses, my company has implemented a new system in which the following rules apply.
if i work Longer than 5 hours = £5.00 but if end time past 8pm = £15.00
if i work Longer than 10 hours = £10.00 but if end time past 8pm = £20.00
if i work Longer than 15 hours or overnight = £25.00
the below formula is for getting the length section correct but im falling short with the >5<10 and the >10<15. i could do this with multiple cells but would like to calculate the days expenses in onn/2 boxes.
Function 1
Q242 is the total hours worked in the day,
R242=IFS((Q242>=15),25,(Q242>5<10),5,(Q242<10>15),10,(Q242<5),0)
my second function works okay for calculating if £10 is to be added after 8pm
S242=IF(J242>=1,10,0)
J242 being a time value for end of working day.
and finally if the answer to function 1 is 25 my total is 25, if not its R242+S242
=IF(R242>=25,R242,R242+S242)
my second and final formula work great but i just strugle with the first long formula getting correct between values for logic test 2 and 3
if anyone has a better way to calculate this id be grateful.
Sep 07 2019 10:42 AM
SolutionSep 07 2019 11:02 AM
@Sergei Baklanvery grateful.
Spent days overthinking this.
Sep 07 2019 12:37 PM
@spudaaa , glad to help. Just note that IFS checks conditions one by one from left to right and stops working on first one which returns TRUE.
Sep 07 2019 01:08 PM - edited Sep 07 2019 01:23 PM
Just to demonstrate the versatility of Excel.
Let the hours worked be called 'hours' and the end time 'clockedOff'
Build three array constants:
worked: ={0;5;10;15}
standard: ={0;5;10;25}
bonus: ={0;15;20;25}
being the time thresholds, the standard rates and the bonus rates for late working.
The pay due is then given by
= LOOKUP( hours, worked, IF( clockedOff>20, bonus, standard ) )
Sep 09 2019 04:52 AM
i have another question,
so i now have a colum for expesense for each day and a total for the week easily.
i now want to work out how much is outstanding to claim between last date claimed and today.
i have a seperate page for existing payements due that have been sent to my office. that contains a cell that has for example
I would like to be able to count dates from last claimed until today
im thinking Countif(Range (Column B total for days expense), Criteria (if Column A Between H1-H2)
Sep 10 2019 01:03 PM
Sorry, I din't catch what exactly is needed. If count dates from last claimed until today - that's
=TODAY()-H1
and apply General format to the result.
If count number of days with payments
=COUNTIFS(A:A,">"&$H$1,$A:$A,"<="&TODAY(),B:B,">0")
If sum all payments for these days
=SUMIFS(B:B,A:A,">"&$H$1,$A:$A,"<="&TODAY())
Sep 07 2019 10:42 AM
SolutionFirst function, if use IFS, could be
=IFS(Q242>=15,25,Q242>10,10,Q242>5,5,TRUE,0)