Elapsed time showing as ####

[NY_excel]

Hello,

I am using the following formula to compute elapsed time. When the elapsed time is within the same day (i.e.; start time is 4/3/2017 10:00 AM and end time is 4/3/2017 1:00 PM) it is producing a negative number which displays as #######. This is strange since the formula works fine when the elapsed time spans different days. Any suggestions? Thank you!!

=F13161-E13161-((INT(F13161)-INT(E13161))-NETWORKDAYS(E13161+1,F13161))-(COUNTIF(HolidayList,">="&E13161)-COUNTIF(HolidayList,">"&F13161))

 

[NY_excel]

BTW, the end time should read 3/20/17 12:12 PM not 3/2017 12:12 PM.

 

[snjpverma]

When the time went onto the other day, I would use this formula.

However, in my case it only went ahead by a day.

=F13161-E13161+1

 

[snjpverma]

*	A	B
Start Time	End time
*	*
*	*
without adding 1
*	*
after adding 1

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:134px;"><col style="width:134px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]22:00:00[/TD]
[TD="align: right"]01:00:00[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]##################[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]03:00:00[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B5 =B2-A2 B7 =B2-A2+1 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[NY_excel]

Thanks for that. When I try removing the one and the formula doesn't go into the next day then it gives me 26:47 which isn't right.

 

[snjpverma]

using plus one only when ERROR works? I mean use IFERROR

 

[Tetra201]

Yes - difference in hours between the two dates / times factoring out weekends and holidays. I have the holidays listed on a separate list. I got the formula from another forum so if you have another way I could write the formula that would work better I would definitely be interested! Thank you!

Is this what you need?

=MOD(F13161-E13161,1)+NETWORKDAYS(E13161,F13161,HolidayList)-1