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))

 

[Amanda Wilso]

This error indicates that a column is not wide enough to display all of its content, or that a negative date or time is used in a cell.

Select the column by clicking the column header.
On the Home tab, in the Cells group, click Format.
Under Cell Size, click AutoFit Column Width.



If a formula that you use to calculate dates or times is correct but results in a negative value, do the following to display that value in a format that is not a date or time format:
On the Home tab, in the Number group, click the Dialog Box Launcher next to Number.
In the category box, click a number format that is not a date or time format.

 

[MARK858]

You don't need to go to the home tab. Select the column then double click the line between the column and the next next column where the column letters are.

 

[NY_excel]

Thanks for the responses. It is not an issue with the column width - no matter how wide I make the column it still displays the ###. When I covert the cell to General instead of the Custom Date I am using it does show it as a negative value. The problem is, for this spreadsheet I need it to display as [h]:mm which is how I have it formatted. Is there any way to keep it in this format and have Excel display the elapsed time correctly?

 

[MARK858]

Other than using the text function go to your options and change it to the 1904 date system.

 

[NY_excel]

Using the 1904 date system and text functions don't display the time correctly.

 

[MARK858]

Post examples where both don't.

 

[NY_excel]

So the start time I am using is 3/20/17 9:25 AM and the end time is 3/2017 12:12 PM. Using the 1904 system makes the time appear as -21:13. Using the Text function makes the time appear as -1.884027778. I want the time to appear as 2:47.

 

[Joe4]

I question the logic of your formula. It looks to me like you are doing a lot of unnecessary subtraction.
Can you explain the logic/thought behind each part of your equation?
What is it supposed to be returning? Is it just the difference between two dates, when factoring out holidays and weekends?

 

[NY_excel]

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!