Works on site have a log-on and log-off time, I have fields set up to capture these times, plus a field for number of staff on site. I want to create another field that will show me chargeable hours, as the first hour on site cannot be charged, and just to complicate things further if 2 staff attend site I can charge full rates for the second person on site but I still have to take off 1 hour for the first person. I was trying to set up a formula of: log-off time minus log-on time multiplied by staff on site minus 1 hour (I.e. 10am-12pm for 2 staff: total 2 hours x 2 staff = 4 hours less 1 hour I can't charge for so total chargeable hours = 3). I can't figure this one out as I have date/time fields and a number field so I keep getting errors. Is this type of formula possible?
- Both the fields are Date/Time fields
- You would need Business Hours from this calculation
Here's the formula you can use
((ROUND(11*( (5*FLOOR((DATEVALUE(Out_Time__c)-DATE(1996,01,01))/7) + MIN(5, MOD(DATEVALUE(Out_Time__c)-DATE(1996,01,01), 7) + MIN(1, 24/11*(MOD(Out_Time__c-DATETIMEVALUE('1996-01-01 13:00:00'), 1))) )) - (5*FLOOR((DATEVALUE(DateTime__c)-DATE(1996,01,01))/7) + MIN(5, MOD(DATEVALUE(DateTime__c)-DATE(1996,01,01), 7) + MIN(1, 24/11*(MOD(DateTime__c-DATETIMEVALUE('1996-01-01 13:00:00'), 1))) )) ), 0))* NumberOfEmployees )-1
Out_Time__c = LogOff Time
DateTime__c = Log In Time