0

I am logging Sickness in salesforce and have a start and end date. For reporting purposes i need to be able to assign a 1 to each of the working days between these 2 dates.

Then when i report i can do a sum/count of the number of days that sit in each month where people are sick.

Thus, if I want to show a user as having a holiday on the following days: 29th Jan 30th Jan 31st Jan 1st Feb 2nd Feb and so on - they would have a start and end date recorded against the sickness record, and when i put it in a matrix report it would show sick days on each in that range.

Currently, this works fine if the sickness is all in one month - e.g. 4th Jan - 11th Jan

However when i go over the month end e.g. 29th Jan - 9th Feb i can only report on either the start or end date not split out the working days that fall into each month.

Improve this question
3
  • What edition are you using? You need to use the BusinessHours object and standard class together to do this sort of calculation. As long as you can use Apex...
    – Adrian Larson ♦
    Jan 4, 2017 at 16:03
  • I have never used Apex but found a solution using Process Builder. I have created a seperate object and 2 process's that create 1 seperate object (days) based on the sickness start date Then the second process keeps recurring this process until the Date of the (days) matches the sickness end date Prob more elegent solutions using Apex but this works nicely for now... Now to just encorporate it to exclude the weekends :) Think i know how to do this though using a weekday formula
    – Matthew Bracewell
    Jan 4, 2017 at 16:05
  • don't forget holidays! Adrian's suggestion is most robust as you can configure your holiday calendars for many countries into the far future
    – cropredy
    Jan 6, 2017 at 0:33

1 Answer 1

Reset to default
0

Unless you doing anything particularly clever to deduce "working days" between your two dates, in a formula you can simply mathematically compare dates as 

End_Date__c - Start_Date__c

Which would give you the number of days between say Feb 9th and Jan 29th as 11.

Improve this answer
2
  • Hi Simon, Maybe i'm not explaining myself well. I from a reporting point of view i want to show the user as having a holiday on the following days: 29th Jan 30th Jan 31st Jan 1st Feb 2nd Feb and so on - so when i put it in a matrix report it would allow me to show holidays on each day A start and end date wouldnt give me that at it would just show 7 days against either the 29th Jan or 9th Feb Hope that makes sense Matt
    – Matthew Bracewell
    Jan 4, 2017 at 15:01
  • ah right, so the matrix report is to resemble a calendar-like view? I'm afraid reporting is well outside my knowledgeable zone, esp. matrix reports. I'll drop this answer off soon (once I reckon on you having read this!) and hope someone else can help out.
    – Simon Lawrence
    Jan 4, 2017 at 15:55

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.