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

1. 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

2. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

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.

3. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

Hi JohnTropley,

4. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

In K4

``Please Login or Register  to view this content.``
Copy across

Open Date in Q15:

5. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

Wow! JohnTopley, you are amazing.

That's about 98% there and way more than I could do. Thanks.

Do you think that you could figure out how to also include the following using your spreadsheet?

1) You use O15 as your opendate of 05/15/2017 which is fine. But lets say that TODAY() today's date when the spreadsheet is open is only August 10. August should only show 10 days and then Sept thru Dec should also return blank (like Jan~Apr). Everyday the spreadsheet is opened the current month in should update by 1.

2) Let's also say that cell O6 (downtime) has a number of 2. How can we have that number subtract from the total in O4?

Again, I really appreciate your time and help.

6. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

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!)

7. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

I may not have explained myself fully and I apologize for that. This is splendid. 99.999% done. Yes, DOWNTIME will have to be done retrospectively and manually inputted which is correct.

The formula doesn't seem to tally correctly, like it's a day or two off? If you see Apr (with no downtime) you will see it max at 30 when we are only on day 28. It should be 28.

In essence to complete this formula we will need to omit cell O14 completely and use the TODAY() function. Today's date will be updated every time the workbook is opened. I replaced

MONTH(\$O\$14)

with

MONTH(TODAY())

but all I get are 0's.

Let me know what you think (besides me being a novice fumbling through this).

8. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

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.

9. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

Everything works fine so far except one major detail and one minor detail. Here is the formula I finished from what you have provided thus far:

=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()),(DAY(EOMONTH(DATE(2017,MONTH(RIGHT(K\$3,3)&0),1),0)))-K6)))

MAJOR = With both of our formulas Mar will calculate DOWNTIME correctly but Apr (and I suspect every month thereafter) does not.

MINOR = If cell \$I\$4 is blank then all the cells return their maximum value. They should remain blank.

Thanks for trying.

Sleep well.

10. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

In K2

``Please Login or Register  to view this content.``

11. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

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?

12. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

Updated post #10

13. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

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*

14. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

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

15. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

*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.

16. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

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.

17. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

I used this formula in K2 copied across

=IF(\$I\$4="","",MAX(0,MIN(TODAY(),EOMONTH(1&RIGHT(K\$3,3),0))-MAX(\$I\$4,EOMONTH(1&RIGHT(K\$3,3),-1))-K6))

That should give the required results except that you get zeroes rather than blanks when months are in the future or before the I4 date. You can format the cells so that zeroes display as blanks, i.e. with custom format

;;0

18. Re: Calculate Uptime in Days within the constraint of Total Days in Each Month

Hi DLL
You can kill zeros with adding IFERROR(1/(1/(
Formula:
`Please Login or Register  to view this content.`

Users Browsing this Thread

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

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