Trouble combining nested IF / AND / OR function

[Jane Huddleston]

I am trying to create a formula to support resource management.

I have a start date and end date of an activity and want to spread the remaining resource equally between the two dates (dividing the Remaining Days between the Start and End Dates in monthly buckets). The formula below works well apart from when the start date and end date are in the same month. Any idea how I can fix this?

=IF(AND(DATE(YEAR($E2),MONTH($E2),1)<=S$1,S$1<=EOMONTH($F2,0)),($J2/DATEDIF(DATE(YEAR($E2),MONTH($E2),1),$F2+15,"m")),0)

Start Date = E2
End Date = F2
Remaining Days = J2
Spread of Dates S2 - AJ2 (monthly buckets from July 2020 through to Dec 2021)

Example spreadsheet attached.

Any help gratefully received!!!
Thank you!
Jane

[Pepe Le Mokko]

Perhaps post a small sample sheet ? ( see yellow banner)

[bebo021999]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Jane Huddleston]

Just added! Many thanks

[Jane Huddleston]

Just added! Many thanks

[JeteMc]

Try:

Please Login or Register  to view this content.

I noticed that the sum of S2:AJ2 isn't the same as the value in J2 even before I changed the formula, however post #1 states that "he formula below works well apart from..." so I didn't make any change based on row 2.
Let us know if you have any questions.

[Jane Huddleston]

Many thanks for this!! Works a treat :-)

Thank you!
Jane

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

[bebo021999]

Nice to hear it works! To make thing better:

DATE(YEAR($E2),MONTH($E2),1)

can be shortened by:

E2-DAY(E2)+1

or

EOMONTH(E2,-1)+1