Elapsed Time - working with the data in the field

senditbry

New Member
Joined
Mar 4, 2019
Messages
3
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.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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 5:pm as time differences?

Jeff
 

senditbry

New Member
Joined
Mar 4, 2019
Messages
3
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

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 4, 2019
Messages
3

ADVERTISEMENT

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.

IDLogged Date and TimeResolved DateHours OpenColumn1
133604/03/2019 12:3704/03/2019 12:450:080
133504/03/2019 12:2204/03/2019 13:501:290
133401/03/2019 16:2604/03/2019 08:400:350
133301/03/2019 16:0404/03/2019 14:506:470
133201/03/2019 15:4504/03/2019 14:507:050
133101/03/2019 15:4104/03/2019 10:202:390
132901/03/2019 14:4004/03/2019 14:207:400
132801/03/2019 14:3504/03/2019 13:407:060
132701/03/2019 14:2604/03/2019 14:207:550
132601/03/2019 14:0304/03/2019 13:006:570
132501/03/2019 12:2504/03/2019 09:104:450
132401/03/2019 10:3101/03/2019 11:150:440
132301/03/2019 10:2501/03/2019 13:102:450
132101/03/2019 10:1201/03/2019 16:105:580
132001/03/2019 09:5404/03/2019 13:0011:060
131901/03/2019 09:4604/03/2019 11:5510:100
131801/03/2019 09:3501/03/2019 12:002:250
131601/03/2019 09:1401/03/2019 10:351:220

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
Did you change the number format back to General or Number with decimals?
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try

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

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top