Elapsed Time - working with the data in the field

[senditbry]

Hi.
I've created an elapsed time (formatted as [h]) from this formula:-

=(NETWORKDAYS.INTL(B2,C2,1,Holidays!$A$1:$A$100)-1)*("17:00"-"9:00")+IF(NETWORKDAYS.INTL(C2,C2,1,Holidays!$A$1:$A$100),MEDIAN(MOD(C2,1),"9:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS.INTL(B2,B2,1,Holidays!$A$1:$A$100)*MOD(B2,1),"9:00","17:00")+0.001

However, now I have my result - I can't work with the data in it. Excel doesn't seem to want to allow me to write an if statement on the value. Basically, I'm trying to say in another column, if elapsed time is <=8 hours then "Pass", otherwise "Fail".

Any pointers on how I can achieve this would be appreciated.
thanks.

 

[Jeffrey Mahoney]

Please tell me what the result should be for that formula. What result is it giving? What does B2 contain?. What does C2 contain? Why are you using 9:am and 5m as time differences?

Jeff

 

[senditbry]

Hi Jeff, thanks for the reply. We are trying to get the total time elapsed between two dates/times during our working hours, Excluding weekends and UK Holidays, hence why I used networkdays.INTL with 1. The result just needs to show the total hours between the two, and then I can create another formula to check if the result is greater than 8 hours (should be fail) and <=8 hours (Pass)

For 9am and 5pm this is the time we wish to count within. For example, if a record is created 4pm on Friday and ends 10am on Monday, it should be 2 hours elapsed. The formula works, but when filtering the results in pivot, excel won't work with the data, so I'm having difficulties trying to get count of Pass and Fail.

thanks.

Does that answer the query?

 

[Jeffrey Mahoney]

I'm not sure how the pivot table is showing the results and how excel can't work with the data.

Can you change the result so that it actually provides hours instead of a fraction of a day and formatting the cell as h
=HOUR((NETWORKDAYS.INTL(B2,C2,1,Holidays!$A$1:$A$100)-1)*("17:00"-"9:00")+IF(NETWORKDAYS.INTL(C2,C2,1,Holidays!$A$1:$A$100),MEDIAN(MOD(C2,1),"9:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS.INTL(B2,B2,1,Holidays!$A$1:$A$100)*MOD(B2,1),"9:00","17:00"))

Jeff

 

[senditbry]

Hi John, I'm afraid the addition of Hour didn't seem to work. once formatted to h, everything is a straight Zero (Column1)

Logged Date and Time is column B, and Resolved Date is C. Hours open is my original formula, and column1 is the formula provided.

[TABLE="width: 517"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Logged Date and Time[/TD]
[TD]Resolved Date[/TD]
[TD]Hours Open[/TD]
[TD]Column1[/TD]
[/TR]
[TR]
[TD="align: right"]1336[/TD]
[TD="align: right"]04/03/2019 12:37[/TD]
[TD="align: right"]04/03/2019 12:45[/TD]
[TD="align: right"]0:08[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1335[/TD]
[TD="align: right"]04/03/2019 12:22[/TD]
[TD="align: right"]04/03/2019 13:50[/TD]
[TD="align: right"]1:29[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1334[/TD]
[TD="align: right"]01/03/2019 16:26[/TD]
[TD="align: right"]04/03/2019 08:40[/TD]
[TD="align: right"]0:35[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1333[/TD]
[TD="align: right"]01/03/2019 16:04[/TD]
[TD="align: right"]04/03/2019 14:50[/TD]
[TD="align: right"]6:47[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1332[/TD]
[TD="align: right"]01/03/2019 15:45[/TD]
[TD="align: right"]04/03/2019 14:50[/TD]
[TD="align: right"]7:05[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1331[/TD]
[TD="align: right"]01/03/2019 15:41[/TD]
[TD="align: right"]04/03/2019 10:20[/TD]
[TD="align: right"]2:39[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1329[/TD]
[TD="align: right"]01/03/2019 14:40[/TD]
[TD="align: right"]04/03/2019 14:20[/TD]
[TD="align: right"]7:40[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1328[/TD]
[TD="align: right"]01/03/2019 14:35[/TD]
[TD="align: right"]04/03/2019 13:40[/TD]
[TD="align: right"]7:06[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1327[/TD]
[TD="align: right"]01/03/2019 14:26[/TD]
[TD="align: right"]04/03/2019 14:20[/TD]
[TD="align: right"]7:55[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1326[/TD]
[TD="align: right"]01/03/2019 14:03[/TD]
[TD="align: right"]04/03/2019 13:00[/TD]
[TD="align: right"]6:57[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1325[/TD]
[TD="align: right"]01/03/2019 12:25[/TD]
[TD="align: right"]04/03/2019 09:10[/TD]
[TD="align: right"]4:45[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1324[/TD]
[TD="align: right"]01/03/2019 10:31[/TD]
[TD="align: right"]01/03/2019 11:15[/TD]
[TD="align: right"]0:44[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1323[/TD]
[TD="align: right"]01/03/2019 10:25[/TD]
[TD="align: right"]01/03/2019 13:10[/TD]
[TD="align: right"]2:45[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1321[/TD]
[TD="align: right"]01/03/2019 10:12[/TD]
[TD="align: right"]01/03/2019 16:10[/TD]
[TD="align: right"]5:58[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1320[/TD]
[TD="align: right"]01/03/2019 09:54[/TD]
[TD="align: right"]04/03/2019 13:00[/TD]
[TD="align: right"]11:06[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1319[/TD]
[TD="align: right"]01/03/2019 09:46[/TD]
[TD="align: right"]04/03/2019 11:55[/TD]
[TD="align: right"]10:10[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1318[/TD]
[TD="align: right"]01/03/2019 09:35[/TD]
[TD="align: right"]01/03/2019 12:00[/TD]
[TD="align: right"]2:25[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1316[/TD]
[TD="align: right"]01/03/2019 09:14[/TD]
[TD="align: right"]01/03/2019 10:35[/TD]
[TD="align: right"]1:22[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jeffrey Mahoney]

Did you change the number format back to General or Number with decimals?

 

[AlanY]

try

=if(C1>(8/24),"Fail","Pass")