CODEWITHSUNDEEP
excelif-statementtime
Georgemichaels1
Georgemichaels1

Reputation: 1

Find length of time within range

I'm trying to understand length of an outage that occurs within time range.

In once cell I have the support period: Level 1 Monday-Saturday 0800-1800

In two other cells I have the start time and end time of the outage

Example: Outage Start: Wednesday, 24 August 2016 16:47

Outage End: Monday, 29 August 2016 10:15

Result should return: 33:28 hours Workings:

Wednesday, 24 August 2016 16:47 - 18:00 = + 01:13
Thursday , 25 August 2016 08:00 - 18:00 = + 10:00
Friday   , 26 August 2016 08:00 - 18:00 = + 10:00
Saturday , 27 August 2016 08:00 - 18:00 = + 10:00
Monday   , 29 August 2016 08:00 - 10:15 = + 02:15

Any advice appreciated!

Updated

Upvotes: 0

Views: 72

Answers (2)

Karpak
Karpak

Reputation: 1927

Sample Excel

Please check the above image, I hope you want something similar to this.

Enter the following formula in the column C2

=INT(IF(DAYS(B2,A2)=0,IF(TEXT(A2,"ddd")="Sun",0,ROUND((B2-A2)/(1/24/60),0)
  ),IF(DAYS(B2,A2)=1,IF(TEXT(A2,"ddd")="Sun",0,(DATE(YEAR(A2),MONTH(A2),DAY(A2))+1/24*18)-A2
     )/(1/24/60) +
     IF(TEXT(B2,"ddd")="Sun",0,B2-(DATE(YEAR(B2),MONTH(B2),DAY(B2))+1/24*8)
       )/(1/24/60),IF(TEXT(A2,"ddd")="Sun",0,(DATE(YEAR(A2),MONTH(A2),DAY(A2))+1/24*18)-A2
     )/(1/24/60) +
     IF(TEXT(B2,"ddd")="Sun",0,B2-(DATE(YEAR(B2),MONTH(B2),DAY(B2))+1/24*8)
       )/(1/24/60) +
      (IF(DAYS((DATE(YEAR(B2-1),MONTH(B2-1),DAY(B2-1))),A2)<0,0,DAYS((DATE(YEAR(B2-1),MONTH(B2-1),DAY(B2-1))),A2))-
         INT(((DATE(YEAR(B2-1),MONTH(B2-1),DAY(B2-1)))-(DATE(YEAR(A2),MONTH(A2),DAY(A2)) + MOD(DATE(YEAR(A2),MONTH(A2),DAY(A2)),7)))/7+1)) * 10 * 60
  )
))

Enter the following formula in the cell D2

=INT(C2/60) &":" & INT(MOD(C2,60)+0.5)

Then copy the C2 and D2 to all the cells that you want, it will give the outage value that you are looking for.

Upvotes: 1

Delta_zulu
Delta_zulu

Reputation: 1610

For example if cell A2 contains the Outage Start date Wednesday, 24 August 2016 16:47 and cell B2 contains the Outage End date Monday, 29 August 2016 10:15, then you can use the following:

=DAY(B2-A2)

This will return 4 days. 

=HOUR(B2-A2)

Will return 17 hours

=MINUTE(B2-A2)

Will return 28 minutes.

Then you could use the following:

="The outage lasted for "& DAY(B2-A2) & " days, " & HOUR(B2-A2) & " hours, and " & MINUTE(B2-A2) & " minutes."

To produce this:

The outage lasted for 4 days, 17 hours, and 28 minutes.

Upvotes: 0

Related Questions