+ Reply to Thread
Results 1 to 8 of 8

Count of Days per month for a contract period

  1. #1
    Registered User
    Join Date
    12-14-2020
    Location
    Riyadh
    MS-Off Ver
    2016, 365
    Posts
    3

    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.

    Thanks for your support.

    Regards,
    SSP
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,421

    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. #3
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    681

    Re: Count of Days per month for a contract period

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-14-2020
    Location
    Riyadh
    MS-Off Ver
    2016, 365
    Posts
    3

    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?
    Additionally- how do I attach the file while I reply?

    Regards,
    SSP

  5. #5
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    681

    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))))
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,421

    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

  7. #7
    Registered User
    Join Date
    12-14-2020
    Location
    Riyadh
    MS-Off Ver
    2016, 365
    Posts
    3

    Re: Count of Days per month for a contract period

    Hi -

    Thanks for your reply. I have attached my excel file here for your review.

    Regards,
    SSP

  8. #8
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,421

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count Number of Days, In a rolling 12 month period
    By ExcelNewbie1977 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-19-2016, 03:29 AM
  2. [SOLVED] Count days in a rolling 12 month period
    By mlafrance in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-13-2014, 02:08 PM
  3. Count days per month within a period
    By Cunner in forum Excel General
    Replies: 4
    Last Post: 02-03-2014, 06:12 AM
  4. Count number of networkdays for each month in a multi month period
    By dreddster in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-10-2013, 11:12 AM
  5. Remove missed days after 12 month rolling period
    By gopherc4 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-21-2009, 06:12 PM
  6. Calculating number of days in each month during a given period.
    By josnah in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-28-2007, 02:52 AM
  7. [SOLVED] Days in month for 10 year period
    By Geoff in forum Excel General
    Replies: 4
    Last Post: 05-06-2005, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1