Ask Search:
Lindsay LarsonLindsay Larson 

List the dates between 2 date fields

I'm not sure if this is possible but I'm trying to create a report that will show each week and then a count of the number of projects that are being worked on during that week.

For example: 
Project 1: Start Date 1/1/18 - End Date 1/18/18
Project 2: Start Date 1/15/18 - End Date 3/5/18

Then the report will show every week for 2018 and count the number of projects being worked.

So for week 1/8/15 there is 1 project being worked on, week 1/15/18 there are 2 projects, 1/22/18 there are 1 projects. 

Is this possible? TIA
 
Best Answer chosen by Lindsay Larson
Steve DoddSteve Dodd
Sure, I hear you...custom development isn't cheap. I don't think you need a custom app, but I was thinking a project management tool like Milestones PM might work for you since it has it's own Gantt chart, but you're right...there would be somewhat of a process change.

I thought a formula (multiple formula fields) might be useful for something like this. See below - if you have a Checkbox formula field called "Week X" for example, you can use logic to show whether or not the date range spans a particular "week" of the year.

This one would result in TRUE for Week # 5, and it works, but you would have to do that 52 times - one field for each week. And even that isn't going to go too far with reporting because you can't do that many groupings in a report, but it may give you some dashboard options. It would be ideal if you could use a Text formula that results in "Week 5" but you can't because your start and end dates could span more than one week, and the formula would display the first result but not more than one.

You could definitely have reports or list views that display "current week's projects" or "next week's projects" or look at things that are coming in the next month, quarter, etc., but I think it would be difficult to try to create a report that would display projects for the entire year. You may be able to do it in a list view and sort the list by Week # - that may actually be the best option. I would just hate to have to use up that many fields for the sake of a workaround.

Formula Return Type = Checkbox
OR( 
MOD(FLOOR((Start_Date__c - DATE(1905,1,1)) / 7), 52) - 1 = 5, 
MOD(FLOOR((End_Date__c - DATE(1905,1,1)) / 7), 52) - 1 = 5 
)

 

All Answers

Steve DoddSteve Dodd
You could probably do that with a creative formula and a lot of fields.

Does it need to be reportable at the object level, or have you considered using a project management tool that integrates with Salesforce ?
Lindsay LarsonLindsay Larson
I've looked at some free project management tools but it seems like we would have to change our current process to make those work. 

We just need an overview chart that our CEO can view. He is currently using the data in salesforce to create his own chart in excel that shows week over week how many projects are being worked on.

The caveat is he doesn't want to pay for a developer to create a VF page or pay for an app.

So my only option really is to try to create a formula and additional fields. Do you have any suggestions or starting points for the fields?

 
Steve DoddSteve Dodd
Sure, I hear you...custom development isn't cheap. I don't think you need a custom app, but I was thinking a project management tool like Milestones PM might work for you since it has it's own Gantt chart, but you're right...there would be somewhat of a process change.

I thought a formula (multiple formula fields) might be useful for something like this. See below - if you have a Checkbox formula field called "Week X" for example, you can use logic to show whether or not the date range spans a particular "week" of the year.

This one would result in TRUE for Week # 5, and it works, but you would have to do that 52 times - one field for each week. And even that isn't going to go too far with reporting because you can't do that many groupings in a report, but it may give you some dashboard options. It would be ideal if you could use a Text formula that results in "Week 5" but you can't because your start and end dates could span more than one week, and the formula would display the first result but not more than one.

You could definitely have reports or list views that display "current week's projects" or "next week's projects" or look at things that are coming in the next month, quarter, etc., but I think it would be difficult to try to create a report that would display projects for the entire year. You may be able to do it in a list view and sort the list by Week # - that may actually be the best option. I would just hate to have to use up that many fields for the sake of a workaround.

Formula Return Type = Checkbox
OR( 
MOD(FLOOR((Start_Date__c - DATE(1905,1,1)) / 7), 52) - 1 = 5, 
MOD(FLOOR((End_Date__c - DATE(1905,1,1)) / 7), 52) - 1 = 5 
)

 
This was selected as the best answer
Steve DoddSteve Dodd
#Oopz...one change to that formula
 
OR( 
MOD(FLOOR((Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 5, 
MOD(FLOOR((End_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 5 
)

 
Lindsay LarsonLindsay Larson
Thank you so much for your detailed response Steve! I really appreciate your help! 
Lindsay LarsonLindsay Larson
Hi Steve - Sorry to bother you again! I've gotten your formula to work and created a few test projects. I think this could work! Would you happen to know a formula that would check all the boxes in the date range? For example, the below project 10 is between week 5 and week 9 so week 6,7,and 8 should be checked too.

User-added image
Thank you so much for your help!!
Steve DoddSteve Dodd
Hi, no bother at all.

You would only be able to update one of those formula fields at a time, but what about doing it this way...

Convert those Checkbox formula fields to regular Checkbox fields and create a process in Process Builder that updates each field if the date range falls within that week. It would be a big process - 52 different criteria nodes - but I think it would work. 
Steve DoddSteve Dodd
But before you do that, can you paste the syntax you're using for your formulas for Weeks 6, 7 and 8 first ? I just want to see if I can figure out why those formulas aren't working since it looks like that date range would in fact cross all three of those weeks.
Lindsay LarsonLindsay Larson
Hi Steve - below are the formuals for week 6,7, and 8. Thanks again!

Week 6:
OR( 
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 6, 
MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 6 
)

Week 7:
OR( 
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 7, 
MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 7 
)

Week 8:
OR( 
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 8, 
MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 8 
)
Steve DoddSteve Dodd
I see what it's doing...it's only counting each "week" if the Start or End Date is in that week, and it's not taking into account a date range that spans more than two different weeks (in this case, 5 & 9 but not 6-7-8).

Let me try to re-write it...we just need it to be able to result in TRUE if the Start Date (or End Date) is either within that week or that week falls between the two dates.
Steve DoddSteve Dodd
Try changing your syntax for each of those three formulas to this:
 
WEEK 6

OR( 
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 6, 
MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 6,
AND(
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 <= 6, 
MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 >= 6
)
)


WEEK 7

OR( 
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 7, 
MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 7,
AND(
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 <= 7, 
MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 >= 7
)
)


WEEK 8

OR( 
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 8, 
MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 8,
AND(
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 <= 8, 
MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 >= 8
)
)



 
Lindsay LarsonLindsay Larson
This is perfect! You are awesome! 2018 works perfect. 
User-added image

I created the 52 fields to double check what it would do if a start date is in 2018 and end date in 2019. Do you think there is a way to make the formula check off week 46-week 6? 

User-added image

I really appreciate all of your help!! I seriously owe you a beer or something!
Steve DoddSteve Dodd
I see what you're saying...it's only counting weeks between as long as the start and end date both fall within the same year...but not if the start date is in one year and the end date is in the next year.

Let me take a look...it may be later this afternoon (Eastern US) before I can work on it though.
Steve DoddSteve Dodd
Also, just curious...did you modify your all of your week checkbox formulas to include that extra 'AND' condition ?
Lindsay LarsonLindsay Larson
By all means, take your time! You are saving my life right now! :) And yes I included the extra AND.

All of them look like this, with the end number changed each time.
OR( 
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 52, 
MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 52, 
AND( 
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 <= 52, 
MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 >= 52 

)
Steve DoddSteve Dodd
I've got this so far if you want to plug it in and try it for weeks 46 through 50.

It gets really messy at Week # 51 - I haven't been able to figure out how to make it work for 51 & 52 so far.

Also, do you have any projects that span more than two calendar years ?
(Example: Project starts in 2018 and ends in 2020)
 
OR(

AND(
YEAR(Project_Delivery_Date__c) > YEAR(Start_Date__c),
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 <= 47,
MOD(FLOOR(( Project_Delivery_Date__c  - DATE(2006,1,1)) / 7), 52) - 1 < 47
),

MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 47,
MOD(FLOOR(( Project_Delivery_Date__c  - DATE(2006,1,1)) / 7), 52) - 1 = 47,
MOD(FLOOR(( Project_Delivery_Date__c  - DATE(2006,1,1)) / 7), 52) - 1 < 1,

AND(
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 <= 47,
MOD(FLOOR(( Project_Delivery_Date__c  - DATE(2006,1,1)) / 7), 52) - 1 >= 47
)

)

 
Steve DoddSteve Dodd
Week # 1 should look like this:
OR( 

AND( 
YEAR(Project_Delivery_Date__c) > YEAR(Project_Start_Date__c), 
MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 >= 1 
), 

MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 1, MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 1, 

AND( 
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 1, MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 1 
) 

)

...and Week # 2 like this:
OR( 

AND( 
YEAR(Project_Delivery_Date__c) > YEAR(Project_Start_Date__c), 
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 > 2, MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 >= 2 
), 

MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 2, MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 = 2, 

AND( 
MOD(FLOOR(( Project_Start_Date__c - DATE(2006,1,1)) / 7), 52) - 1 <= 2, MOD(FLOOR(( Project_Delivery_Date__c - DATE(2006,1,1)) / 7), 52) - 1 >= 2 
) 

)

I have to revisit 3 through 9 when I can get my eyes to re-focus...it looks like the single digit weeks don't play nice when the project spans two different years for some reason.
Lindsay LarsonLindsay Larson
Hi Steve - I hope you had a nice weekend! It is possible that a project could span more than 2 calendar years but it's not often. I've updated the formulas for weeks 46-52 using the first formula and weeks 1-6 using the second formula. I haven't updated all of them because I noticed they were different and didn't know at which point to use the other formula. It does look like it works though!

I do see how a project spanning more than 1 year can be an issue since all the checkboxes would be true. Would there be a point in time where the formula would reset? Like once it's a hits new year.

User-added image
Lindsay LarsonLindsay Larson
Hi Steve - I think if we have any projects going 1 year or longer that we would just need to figure out of a different process for those or just be mindful of the start/end date fields.

I think we are getting somewhere with the formulas you provided though and believe those will work. If we can only get them to work for projects that start in one year and end in the next year we will be golden.
 
Steve DoddSteve Dodd
I agree - a formula to try to accommodate anything that spans three calendar years is probably possible, but it would have to contain so many conditions that my worry is it would hit the size limit.

Did you try testing it (using the above formulas) with a Project Start Date that is in weeks 46 through 50, then ends after the first of the year ?