+ Reply to Thread
Results 1 to 8 of 8

Convert server downtime to Uptime in Hours for whole month

  1. #1
    Registered User
    Join Date
    07-29-2016
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Red face Convert server downtime to Uptime in Hours for whole month

    New to the site and new to excel so hope someone can help. I have downtime start date to downtime end date, which generated by system.

    I would like a way to record on a daily basis what the uptime has been for each server and then to record the same for that month in a hours… The time always started at 6.00 AM to 6.00 AM next day everyday. Maybe you could edit the attached file for more clear understanding on my problem.

    Appreciate if someone can guide me.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Convert server downtime to Uptime in Hours for whole month

    Try

    J5

    =IF(AND($I5>=INT($C$4),$I5<INT($D$4)),0,24)

    K5

    =IF(AND($I5>=INT($C$5),$I5<INT($D$5)),0,24)

    L5

    =IF(AND($I5>=INT($C$6),$I5<INT($D$6)),0,24)

  3. #3
    Registered User
    Join Date
    07-29-2016
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Re: Convert server downtime to Uptime in Hours for whole month

    Quote Originally Posted by JohnTopley View Post
    Try

    J5

    =IF(AND($I5>=INT($C$4),$I5<INT($D$4)),0,24)

    K5

    =IF(AND($I5>=INT($C$5),$I5<INT($D$5)),0,24)

    L5

    =IF(AND($I5>=INT($C$6),$I5<INT($D$6)),0,24)
    Thank you so much sir, it works. But, how to modify the formula if its has multiple set of downtime in a month and the hour also changing. Please refer to the file attached.

  4. #4
    Registered User
    Join Date
    07-29-2016
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Re: Convert server downtime to Uptime in Hours for whole month

    Anyone here to help me??

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Convert server downtime to Uptime in Hours for whole month

    How would the hours be allocated for these downtime start and end times :

    2016-05-14 08:00:00 AM 2016-05-15 09:00:00 AM?

    2016-05-14 22
    2016-05-15 24
    2016-05-16 3

    and you need change data in C and to Date/Time not text

    ... might need a VBA solution

  6. #6
    Registered User
    Join Date
    07-29-2016
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Re: Convert server downtime to Uptime in Hours for whole month

    Quote Originally Posted by JohnTopley View Post
    How would the hours be allocated for these downtime start and end times :

    2016-05-14 08:00:00 AM 2016-05-15 09:00:00 AM?

    2016-05-14 22
    2016-05-15 24
    2016-05-16 3

    and you need change data in C and to Date/Time not text

    ... might need a VBA solution
    The uptime:
    2016-05-13 06:00:00 AM 24
    2016-05-14 06:00:00 AM 2
    2016-05-15 06:00:00 AM 21
    2016-05-16 06:00:00 AM 24

    Maybe attached file can show more details.

    I tried to change the format to Date/Time, but need to change 1 by 1, as the system the generate a thousand of row of the downtime using the Text format.

  7. #7
    Registered User
    Join Date
    07-29-2016
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Re: Convert server downtime to Uptime in Hours for whole month

    Anyone here can help me? Really appreciate it..

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Convert server downtime to Uptime in Hours for whole month

    When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. In particular, please read forum rule #4 about PM messages. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

    (link above in the menu bar)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Server Uptime/Downtime Calculation
    By rovert.natsud in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-13-2018, 12:28 PM
  2. [SOLVED] Calculating Downtime During Hours of Operation
    By Puni in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-01-2018, 11:00 PM
  3. Downtime Tracking between Multiple Days Only Counting Working Hours
    By TandCsMom in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2014, 05:24 PM
  4. Help with creating uptime/downtime calculation/analysis
    By buerm009 in forum Excel General
    Replies: 0
    Last Post: 02-19-2013, 02:24 PM
  5. [SOLVED] Function or macro to convert string with weeks, days, hours, minutes to Hours
    By kknb0800 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2012, 11:35 AM
  6. Replies: 8
    Last Post: 03-19-2009, 11:01 PM

Bookmarks

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