0

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?

  • so if 10 staff persons came, only 1st person's 1 hour should be reduced and the remaining 9 persons every hour should be taken to consideration...Is that right? – Subhash May 17 '16 at 11:10
  • If you are using Date/Time Fields means, better you should go with Apex Trigger. You can't seperate Only time from Date/Time Fields in validation rule or formula field – Subhash May 17 '16 at 11:14
0

Considering

  1. Both the fields are Date/Time fields
  2. 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.