In a form I need to calculate total due based on time in and time out fields.
If the total worked upto 1 hour remuneration is say 0.200 fils, subsequent 1 hours is 0.100 fils for each hour. A person working 3 hours gets 400 fils, 2 hours gets 300fils and so on… how to calculate it in a simple and elegant way.
The general construct of the expression could be
IF( [Duration]<=1, 200, (200+ ([Duration]-1)*100))
You may wish to elaborate if you have durations in between and for those duration if you wish to calculate say 50 fils for 30 minutes or you wish it to be rounded off to next hour or lower hour.
Also duration may need to be in decimal format by using TOTALHOURS() expression TOTALHOURS([Duration]) for using it in mathematical computations.
Thanks, this solves it for my use case,
but what if 50 fils required for 30 minutes and with rounding?
Please elaboarate how your current [Duration] expression is. If you current duration expression is in column [Duration] with “HH:MM:SS” format, then wrapping it with TOTALHOURS() will give the duration in decimal format. So a duration of 04:30:00 will become 4.5, 00:30:00 will become 0.5 with TOTALHOURS([Duration])
For rounding off please use ROUND() function.
So in general expression could be
IF( TOTALHOURS([Duration])<=1, 200, (200+ (TOTALHOURS([Duration])-1)*100))
With rounding ,
IF( ROUND(TOTALHOURS([Duration]))<=1, 200, (200+ ROUND((TOTALHOURS([Duration])-1))*100))