Networkadys formula is not working in some scenarios - please help!

**chauhan101** · 08-03-2012, 05:08 PM

Hello,
I have a spreadsheet with the following dates

Created = 5/9/12 2:26 PM
Assigned = 5/11/2012 4:00 PM
Completed = 7/18/2012 10:30 AM

I wanted to create three formulas: Created to Completed, Created to Assigned, and Assigned to Completed

I started with Created to Completed and came up with:
=(NETWORKDAYS([@Created],[@[Completed Date]],'Do NOT DELETE Time Frames'!N$2:N$12)-1+([@[Completed Date]]-INT([@[Completed Date]]))-([@Created]-INT([@Created])))

Can someone please check it for me? It's coming out to total 16:20:03. The total days:hours:minutes (excluding weekends and holidays) should be more than 30 days. It works for some dates and not for others.

Then for the other two formulas I thought I would just substitute the cells and it would be the same concept.

Please let me know! Thanks!

**daddylonglegs** · 08-03-2012, 05:22 PM

When you use d:hh:mm format the days never go beyond 31 (because d is actually day of month not elapsed days) so that format isn't suitable for periods that might exceed 31 days. You could format the result cell as [h]:mm then that will show the total hours.....or if you want the days to display accurately try this version:

=INT(NETWORKDAYS([@Created],[@[Completed Date]],'Do NOT DELETE Time Frames'!N$2:N$12)-1+([@[Completed Date]]-INT([@[Completed Date]]))-([@Created]-INT([@Created])))&":"&TEXT([@[Completed Date]]-[@Created],"hh:mm")

**chauhan101** · 08-06-2012, 09:23 AM

Yes! This worked thank you so much. I didn't know that the formula I had doesn't work for more than 31 days so I learned something new

Have a great day!

**Cutter** · 08-06-2012, 11:23 AM

@ chauhan101

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.