Network Days help (Difference in time and formatting)

[Mystafet]

OK, I'm helping another team work on a spreadsheet, I generally don't deal wiht days/times in my work, so I'm slightly losing.

I've searched the forums and found some helpful information, but it appears that most of this isn't calculation correctly, and I can't get the formatting how they would like it.

That data they have is extracted straight out of a database and they do not want to have to change or reformat any of it if possible.

My columns are set up as:
Column H : Entered Date
Formatting of H : mm/dd/yyyy h:mm:ss

Column L : End Date
Formatting of L : mm/dd/yyyy h:mm:ss

Column Q is where I currently have the following formula:

=TEXT((NETWORKDAYS(H2,L2)-1)/3+MOD(L2,1)-MOD(H2,1),"d:hh:mm")

First off, for the formatting, they are looking for something along the lines of:
#d:#h:#m
they would like the d, h, and m so they can easily differentiate between the numbers. Or any other variations that would make the numbers easily read would be very beneficial.

The second issue and more important one is that it doesn't appear to be calculating correctly.

Here are some Instances:
<table style="border-collapse: collapse; width: 372pt;" border="0" cellpadding="0" cellspacing="0" width="495"><col style="width: 145pt;" width="193"> <col style="width: 142pt;" width="189"> <col style="width: 85pt;" width="113"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 145pt;" width="193" height="17">Entered Date</td> <td class="xl66" style="width: 142pt;" width="189">End Date</td> <td class="xl67" style="width: 85pt;" width="113">Time To Complete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">12/28/2010 4:09:21 PM</td> <td class="xl68">01/04/2011 9:33:53 AM</td> <td class="xl65">1:09:24</td> </tr> </tbody></table>

This example shows 1 day 9 hours and 24 minutes. There are many more days between these dates than shown.

<table style="border-collapse: collapse; width: 372pt;" border="0" cellpadding="0" cellspacing="0" width="495"><col style="width: 145pt;" width="193"> <col style="width: 142pt;" width="189"> <col style="width: 85pt;" width="113"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 145pt;" width="193" height="17">Entered Date</td> <td class="xl66" style="width: 142pt;" width="189">End Date</td> <td class="xl67" style="width: 85pt;" width="113">Time To Complete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">12/31/2010 1:39:19 PM</td> <td class="xl68">01/04/2011 9:56:13 AM</td> <td class="xl65">0:12:16</td> </tr> </tbody></table>
This shows 0 days 12 hours and 16 minutes, which is also slightly off.


<table style="border-collapse: collapse; width: 372pt;" border="0" cellpadding="0" cellspacing="0" width="495"><col style="width: 145pt;" width="193"> <col style="width: 142pt;" width="189"> <col style="width: 85pt;" width="113"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 145pt;" width="193" height="17">Entered Date</td> <td class="xl66" style="width: 142pt;" width="189">End Date</td> <td class="xl67" style="width: 85pt;" width="113">Time To Complete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">01/06/2011 3:56:28 PM</td> <td class="xl68">01/07/2011 7:26:51 AM</td> <td class="xl65">#VALUE!</td> </tr> </tbody></table>
I mean, there is only like 30 minutes between these 2, but it returns a #VALUE, I have numerous lines on the spreadsheet like this. Is there anything to do for this situation?


<table style="border-collapse: collapse; width: 372pt;" border="0" cellpadding="0" cellspacing="0" width="495"><col style="width: 145pt;" width="193"> <col style="width: 142pt;" width="189"> <col style="width: 85pt;" width="113"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 145pt;" width="193" height="17">Entered Date</td> <td class="xl66" style="width: 142pt;" width="189">End Date</td> <td class="xl67" style="width: 85pt;" width="113">Time To Complete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">12/02/2010 1:02:29 PM</td> <td class="xl68">01/10/2011 1:00:59 PM</td> <td class="xl65">8:23:58</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">12/06/2010 3:05:41 PM</td> <td class="xl68">01/10/2011 1:05:43 PM</td> <td class="xl65">8:06:00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">12/08/2010 12:19:42 PM</td> <td class="xl68">01/07/2011 3:45:07 PM</td> <td class="xl65">7:11:25</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">12/08/2010 2:17:23 PM</td> <td class="xl68">01/07/2011 2:33:24 PM</td> <td class="xl65">7:08:16</td> </tr> </tbody></table> <table style="border-collapse: collapse; width: 372pt;" border="0" cellpadding="0" cellspacing="0" width="495"><col style="width: 145pt;" width="193"> <col style="width: 142pt;" width="189"> <col style="width: 85pt;" width="113"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 145pt;" width="193" height="17">01/18/2011 10:08:00 AM</td> <td class="xl67" style="width: 142pt;" width="189">01/19/2011 10:53:43 AM</td> <td class="xl65" style="width: 85pt;" width="113">0:08:45</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt;" height="17">01/18/2011 10:09:35 AM</td> <td class="xl67">01/19/2011 3:01:15 PM</td> <td class="xl65">0:12:51</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt;" height="17">01/18/2011 10:19:26 AM</td> <td class="xl67">01/19/2011 11:11:20 AM</td> <td class="xl65">0:08:51</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt;" height="17">01/18/2011 11:50:42 AM</td> <td class="xl67">01/25/2011 2:27:22 PM</td> <td class="xl65">1:18:36</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt;" height="17">01/18/2011 12:54:56 PM</td> <td class="xl67">01/25/2011 12:46:32 PM</td> <td class="xl65">1:15:51</td> </tr> </tbody></table>
There are a few more examples. Now that I actually look at it, a ton of them are off, with the exception of a few.

This is how the date/timestamps look in the cell, when extracted from their database:
1/18/2011 10:08:00 AM
1/19/2011 3:01:15 PM

There are 2 spaces between the date and time, I'm wondering if this is messing things up.

Any help would be greatly appreciated.

 

[jasonb75]

Not sure what you're trying to to with /3 in the formula.

Does =TEXT((NETWORKDAYS(H2,L2)-1)+MOD(L2,1)-MOD(H2,1),"d\d:hh\h:mm\m") give the desired result?

 

[Mystafet]

Not sure what you're trying to to with /3 in the formula.

Does =TEXT((NETWORKDAYS(H2,L2)-1)+MOD(L2,1)-MOD(H2,1),"d\d:hh\h:mm\m") give the desired result?

That appears to have corrected most of the results. I want to say the /3 was to base it off an 8 hour workday as apposed to a 24 hour day.

It still appears that I am getting the #VALUE! for a few as well, and I don't understand why.

<table style="border-collapse: collapse; width: 372pt;" border="0" cellpadding="0" cellspacing="0" width="495"><col style="width: 145pt;" width="193"> <col style="width: 142pt;" width="189"> <col style="width: 85pt;" width="113"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt; width: 145pt;" width="193" height="17">Entered Date</td> <td class="xl64" style="width: 142pt;" width="189">End Date</td> <td class="xl65" style="width: 85pt;" width="113">Time To Complete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">01/14/2011 11:04:33 AM</td> <td class="xl66">01/15/2011 8:38:21 AM</td> <td class="xl63">#VALUE!</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">01/14/2011 1:59:13 PM</td> <td class="xl66">01/15/2011 8:39:34 AM</td> <td class="xl63">#VALUE!</td> </tr> </tbody></table>

 

[jasonb75]

Because your start (entered) date is a sunday, it's not counted in the networkdays calculation, this is ultimately producing a negative result which causes the error.

If you're working with a 7 day week then =TEXT(B1-A1,"d\d:hh\h:mm\m") is probably a better solution.

 

[barry houdini]

Hello Mystafet,

There are at least 2 problems here

1. Using "d:hh:mm" format still treats a day as 24 hours. Your formula counts a Monday to Friday working day as 8 hours but it doesn't really make much sense then to use that format because it still treats a day as 24 hours, e.g. if you have start time Tuesday at 11:00 AM and end time exactly 3 days later also at 11:00 AM then your formula will return 1:00:00 because the 3 working days x 8 hours = 24 hours = 1 day. What do you want to show in those circumstances, 3 days?

2. That formula only works properly if start and end dates are within working hours and your working day is defined here by the /3, meaning it's 8 hours long...so your original #VALUE! error is caused by having an end time of 7:26 AM with a start time of 3:56 PM, those times are 8:30 apart so that's calculated as a negative time...

I'm assuming that you have a working day from 07:00 to 16:00, which is, of course, 9 hours. Are you taking a lunch break into account? If so you really should define that too.

Assuming that meal break starts at noon and is one hour long you can use this formula

=(NETWORKDAYS(H2,L2)-1)/3+MOD(L2,1)-MOD(H2,1)+((MOD(L2,1)<"12:00"+0)-(MOD(H2,1)<"12:00"+0))*("1:00")

format result cell as [h]:mm and you'll get total working hours like 18:30

If you would rather see that as "2 days 2:30" try this formula

=NETWORKDAYS(H2,L2)-1-(MOD(H2,1)>MOD(L2,1))&" days "&TEXT(MOD(MOD(L2,1)-MOD(H2,1)+((MOD(L2,1)<"12:00"+0)-(MOD(H2,1)<"12:00"+0))*("1:00"),"8:00"),"h:mm")