Calculating Days in a month depending on start date and end date of order

[ARCDad]

I'm trying to create a formula that tells me how many days I have in a certain month based on the start date and end date of an order. There are 9 situations that I'm trying to create a formula for:
Days in a campaign= if the order started before December 1, but ended 12/31 THEN 12/31-12/1
Days in a campaign= if the order started before December 1, but ended before 12/31 THEN END DATE-12/1
Days in a campaign= if the order started before December 1, but ends after 12/31 THEN 12/31-12/1
Days in a campaign= if the order started December 1, but ended before 12/31 THEN END DATE-12/1
Days in a campaign= if the order started December 1, but ended after 12/31 THEN 12/31-12/1
Days in a campaign= if the order started December 1, but ended on 12/31 THEN 12/31-12/1
Days in a campaign= if the order started after December 1, but ended before 12/31 THEN END DATE-START DATE
Days in a campaign= if the order started after December 1, but ended after 12/31 THEN 12/31- START DATE
Days in a campaign= if the order started after December 1, but ended on 12/31 THEN 12/31-START DATE

I only care about what's in December (or whatever month it is).

I thought it would be an IF statement, but I can't figure out how to write it out in excel.

[FlameRetired]

Please upload a sample Excel workbook with 10-20 rows of relevant data ... Start / End dates ... other wise you are asking volunteers to supply fake data which may not be representative. It saves time.

If you are not familiar with how to do this:

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[ARCDad]

That's a good point. Workbook attached with examples of all situations there. I need 1 formula to be able to calculate the dates for all of those situations, depending on the dates provided in the report.

[FlameRetired]

Only December ... the attached assumes only December 2018. Note the 0 in JNLSPAID_P1100_PCH_GSK_01SEP18_LL_Canada 12/1/2019 1/16/2019


With the first of the target month in cell E1 (change date to change month) this formula in D2 and filled down.

Please Login or Register  to view this content.

[Phuocam]

Or try:

=SUMPRODUCT(--(MONTH(ROW(INDIRECT(B2&":"&C2)))=12))