Calculate Uptime in Days within the constraint of Total Days in Each Month

Halp!!

I am attempting to calculate total server uptime in days but constrained by the total number of days in that month. Been attempting to use TODAY() against a static date in a given cell (I4) with no luck. Thoughts?
Scenario:

Month of March - the formula should return a value of the total number of days the server has been up but only return at a maximum 31 days.
Month of April - the formula should return a value of the total number of days the server has been up but only return at a maximum 30 days.
Month of May - the formula should return a value of the total number of days the server has been up but only return at a maximum 31 days.
So on and so forth.

Now here is a curveball. More server will be turned up in future months so how to return a blank cell for days prior to the date of the static cell (I4)??

Month of January - the formula should return a blank cell because all days are before the opendate of cell I4. Theoretically it should also return a maximum of 31 days to be copacetic with all other formulas.
Month of February - the formula should return a blank cell because all days are before the opendate of cell I4. Theoretically it should also return a maximum of 28 days to be copacetic with all other formulas.


Any assistance is greatly appreciated.

Sincerely,
Excel Novice
Screenshot 2017-04-28 11.24.13.png

Attach a sample workbook not image). 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.

Hi JohnTropley,

Thanks for your reply. I have uploaded the Spreadsheet as suggested.

I obviously misunderstood the requirement: you are only interested in dates upto the current month: so with start of 15 March and ToDAY() 10th August we show data for Mar, Apr, May, Jun, Jul and 10 days of August?

In K4

=IF(OR(MONTH($I$4) > MONTH(RIGHT(K3,3)&0),MONTH($O$14) < MONTH(RIGHT(K3,3)&0)),"",IF(MONTH($I$4)=MONTH(RIGHT(K$3,3)&0),EOMONTH(DATE(2017,MONTH(RIGHT(K$3,3)&0),1),0)-$I$4-K6,IF(MONTH(RIGHT(K$3,3)&0)=MONTH($O$14),DAY(H14),(DAY(EOMONTH(DATE(2017,MONTH(RIGHT(K$3,3)&0),1),0)))-K6)))

Replaced O15 with I4

DOWNTIME can only be done retrospectively i.e at the end of a month so in the attached we cannot allocate down time to August as we do not know when it occurred (or has occurred!)

Try

=IF(OR(MONTH($I$4) > MONTH(RIGHT(K3,3)&0),MONTH($O$14)
< MONTH(RIGHT(K3,3)&0)),"",IF(MONTH($I$4)=MONTH(RIGHT(K$3,3)&0),EOMONTH(DATE(2017,MONTH(RIGHT(K$3,3)&0),1),0)-$I$4-K6,IF(MONTH(RIGHT(K$3,3)&0)=MONTH($O$14),DAY($O$14),(DAY(EOMONTH(DATE(2017,MONTH(RIGHT(K$3,3)&0),1),0)))-K6)))


I'll sign off now ... it's late here in the UK.

Thank you.

Last question. If we were to replace the date in cell $O$14 with =Today(), why wont the Downtime modifier work in any of the subsequent months (Apr, May, Jun, etc.)? Is it possible to make that work since that is the end goal?

Updated post #10

I had made the same change as your update but if you attempt to put a number in DOWNTIME under Apr in cell $N$6 (after applying the formula across all months in row 4) you will see that it does not modify as it should. *scratches head*

because April is the Current month and we "agreed" that data could only be entered retrospectively i.e. after month end.

If you want tp put down time DURING a month then in K2

=IF($I$4="","",IF(OR(MONTH($I$4) > MONTH(RIGHT(K3,3)&0),MONTH(TODAY()) < MONTH(RIGHT(K3,3)&0)),"",IF(MONTH($I$4)=MONTH(RIGHT(K$3,3)&0),EOMONTH(DATE(2017,MONTH(RIGHT(K$3,3)&0),1),0)-$I$4-K6,IF(MONTH(RIGHT(K$3,3)&0)=MONTH(TODAY()),DAY(TODAY())-K6,(DAY(EOMONTH(DATE(2017,MONTH(RIGHT(K$3,3)&0),1),0)))-K6))))

*high five*

Yes that was it. I misunderstood the reasoning for the modifier to be retroactive. I see what you you changed and it makes some sense. Now for me to go delve into more complicated Excel vids.

Thank you so much for your help Mr. JohnTopley. You are appreciated.

You are very welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.