# Count of Days per month for a contract period

1. ## Count of Days per month for a contract period

Hi All -

I have a list of employees and their billing period against each contract.
I need to calculate the days billed for each employee against each contract by month for last 12 months.
My worksheet and My expected result is mentioned in the attachment.

Regards,
SSP

2. ## Re: Count of Days per month for a contract period

Firstly attaching a workbook makes life much easier as things don't need to be retyped and having dates for the months to perform the calculation on

=if(and(startdate <= eomonth(monthdate,0),enddate>=monthdate), min(enddate,eomonth(monthdate,0))-max(startdate,monthdate)+1,"no")

is along the line you want so if in H1 you put the date 1-feb-20

and the first row of data was 3
in h3

=if(and(e3 <= eomonth(H1,0),f3>=H1), min(f3,eomonth(H1,0))-max(e3,H1)+1,"no")

tou may need to swap, for ; depending on your locale

3. ## Re: Count of Days per month for a contract period

Formula:
`Please Login or Register  to view this content.`

4. ## Re: Count of Days per month for a contract period

Hi Shareez- Thanks for your support. I found some anomalies with the formula especially if the start date is in 2020 and end date is in 2020 or 2021.
row numbers 7,31,36,38,43 have this issue.
Could you please look into it?

Regards,
SSP

5. ## Re: Count of Days per month for a contract period

Go Advanced > Manage Attachments > Choose File > Upload Attachment > Close Window (Read the Yellow ribbon in the website)

Use the formula in cell G2: =IF(\$F2<G\$1,"No",IF(AND(MONTH(G\$1)=MONTH(\$F2),YEAR(\$F2)=YEAR(G\$1)),DAY(\$F2),DAY(EOMONTH(G\$1,0))))

6. ## Re: Count of Days per month for a contract period

follow the instructions in the yellow banner. thee is a go advanced button at the bottom right of the box I am typing in now, click on it. Scroll down to additional options and click on manage attachments. click browse, select the file and upload. then submit in the usual way

Hi -

Regards,
SSP

8. ## Re: Count of Days per month for a contract period

I would do as the attached but differ by 1 for some calculations as it depends how you treat the end date

for example 30Jan-1st Feb you have 2 days in Jan and none in Feb is this true? Did they not work the 1st of Feb

There are currently 1 users browsing this thread. (0 members and 1 guests)