+ Start a Discussion
sandeep@devsandeep@dev 

Formula to find out No of days in current month b/w start and end datyes

HI Guys,

I want a formula like below.
eg Site XYZ: was booked for 7 days in total in February – formula looks to calendar to determine # days in month and account for leap year or not. The 7 days booked/ 29 days in leap year = 24.13% for Feb 2016/ If booking starts in previous month, or overflows to next month, the days in the previous and next month should not be calculated as part of the February occupancy. Eg starts 28 Feb so only 2 days BOOKED for Feb should count – 28 + 29 Feb

I wanted to showcase the same in report as below

User-added image

Right now I'm showing it as no of days booked b/w start and end dates based on start month. If it was booked b/w two months I want only those no of days booked in current month and remaining should go to next month. Any help is appreciated.

Thanks in Advance,
Sandeep Kumar
MellowRenMellowRen
Sandeep

You haven’t really specified what fields you have to work out the duration of the booking nor the fields/logic you have to determine the month of interest.

Let’s assume you have a field called Report Date (from which we will determine the month of interest) and for the booking you have two fields called Start Date and End Date.

To calculate the size in days of the reporting month you can use this:
DAY(DATE(YEAR(Report_Date__c),MONTH(Report_Date__c)+1,1)-1)

To calculate the number of days in the booking associated with the booking month, do this:
MIN(End_Date__c,DATE(YEAR(Report_Date__c),MONTH(Report_Date__c)+1,1)-1) - MAX(Start_Date__c,DATE(YEAR(Report_Date__c),MONTH(Report_Date__c),1)) + 1

Now you can simply divide the second result by the first (or combine the formulas into one).

Hope this helps.

Regards
MellowRen