09-07-2019 10:24 AM
09-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.
Q242 is the total hours worked in the day,
my second function works okay for calculating if £10 is to be added after 8pm
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
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.
09-07-2019 01:08 PM - edited 09-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:
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 ) )
09-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)
09-10-2019 01:03 PM
Sorry, I din't catch what exactly is needed. If count dates from last claimed until today - that's
and apply General format to the result.
If count number of days with payments
If sum all payments for these days