Start and End date formula help!

[Storageguy]

Hello and thank you for time.

As you will see, I am working on a spreadsheet that will allow me to track items that I receive to then bill the owners of the items for storage on a month to month basis.

The 2 lines are examples of the typical scenarios that will be calculated.

Starting at cell N2, you can see I have that I have a received date, then O2 a delivery date, P2 is the day to begin billing for February, Q2 is the day to end billing in February on, and R2 will calculate the number of days the item is stored in February.

N2 date is prior to Feb.1 so i made a formula (i am not well skilled in excel) to say to start storage for the month on Feb 1. If we look down at line 3, the scenario is different. The item was received after feb.1, so billing for February needs to start on that date instead.

I am having similar problems with my feeble attempt to get a formula working in Q2 for the ending date of billing in February. Since the item has been delivered in Feb., I need the "end February billing on" date to be the delivery day. The same goes for line 3, but in this case the item is not delivered this month and should populate with 3/1/22.

Last scenario can be seen in line 3, where the item came in before February, and has not been delivered this month.

All in all, i just want to be able to input my received date, and if necessary the delivered date, and then get my days stored to populate on a monthly basis. Hopefully whatever formulas are used here can also be used in a column for each month of the year with minor changes from february to march to april etc.

Thanks again for your time.
Regards,
storageguy

 

[Storageguy]

quick update:
I have made this formula in column Q:
=IF(ISBLANK(O2),"3/1/2022",IF(ISNONTEXT(O2),O2))

this is working

Still cannot get P3 to work right. It keeps using "2/1/22" instead of the received date of "2/3/22"

See attached updated excel file with this formula added. also apologies, im working this in sheets and looks like my original file that saved as an excel file didnt convert very well. This attachment should be good
Thank you again!

 

[p45cal]

This is a guess at what you're wanting; the number of billing days for a given month(&year) between the dates in columns N & O?
In the attached, is a formula in cell T2:
=MAX(0,MIN(EOMONTH(S2,0),O2)-MAX(EOMONTH(S2,-1),N2))
This depends on the billing month which I've put into column S (Billing Month). This is a plain Excel date which can be any date within that billing month.

That's it.
The formula in cell T2 is derived from the broken-up steps in columns WW:AA where AA contains the same answer. You can delete these columns, they're only there to show the workings.