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?

2
  • 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
    Commented May 17, 2016 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
    Commented May 17, 2016 at 11:14

1 Answer 1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .