networkdays issue

This is my Q838 field: 3/3/2010 4:06:08 PM
This is my T838 field: 3/3/2010 8:22:42 PM
This is my function:
=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))*24

What I get is 16:06
what I would expect to get is 4 hours 16 minutes and 34 seconds

What did I do wrong in the function to get such a difference?

Hi,

The *24 at the end is making the formula return a decimal so delete it and
then format the cell as time and you will get

4 hours 16 minutes and 34 seconds

=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:


This is my Q838 field: 3/3/2010 4:06:08 PM
This is my T838 field: 3/3/2010 8:22:42 PM
This is my function:
=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))*24

What I get is 16:06
what I would expect to get is 4 hours 16 minutes and 34 seconds

What did I do wrong in the function to get such a difference?

Mike,

thank you very much again for the help on this (again lol)

"Mike H" wrote:

Hi,

The *24 at the end is making the formula return a decimal so delete it and
then format the cell as time and you will get

4 hours 16 minutes and 34 seconds

=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:


This is my Q838 field: 3/3/2010 4:06:08 PM
This is my T838 field: 3/3/2010 8:22:42 PM
This is my function:
=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))*24

What I get is 16:06
what I would expect to get is 4 hours 16 minutes and 34 seconds

What did I do wrong in the function to get such a difference?

Mike,

It doesn't seem to be spanning days

for example Q32636 is 5/4/2010 01:00am (march 5 2010 1am start of business)
T32636 is 09/04/2010 10:00 (end of business day)

using that function I get 9:00:00 its only subtracting the two times. I
would expect 40:00:00 for 40 work hours for the week. Or should I be looking
at a macro to do this? Remember im trying to find out how many work hours a
ticket has been opened only figuring in 8 hours a work day.

AJ

"Mike H" wrote:

Hi,

The *24 at the end is making the formula return a decimal so delete it and
then format the cell as time and you will get

4 hours 16 minutes and 34 seconds

=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:


This is my Q838 field: 3/3/2010 4:06:08 PM
This is my T838 field: 3/3/2010 8:22:42 PM
This is my function:
=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))*24

What I get is 16:06
what I would expect to get is 4 hours 16 minutes and 34 seconds

What did I do wrong in the function to get such a difference?

Hi,

I'm not really a liker of that formula you used but as it 'seemed' to be
doing what you wanted I never altered it. I prefer this formatted as [h]:mm:ss

Now at the start of the formula you will se 17.5 and this refers to the end
of your working day 17:30 or 5:30 PM

About a 1/3rd of the way through the formula note the 8.5, this is the start
of your day 08:30.

Change these to to meet your requirements

=SUM(17.5/24-MOD(Q838,INT(Q838)),MOD(T838,INT(B1))-8.5/24)+(NETWORKDAYS(Q838,T838)-2)*9/24
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:

Mike,

It doesn't seem to be spanning days

for example Q32636 is 5/4/2010 01:00am (march 5 2010 1am start of business)
T32636 is 09/04/2010 10:00 (end of business day)

using that function I get 9:00:00 its only subtracting the two times. I
would expect 40:00:00 for 40 work hours for the week. Or should I be looking
at a macro to do this? Remember im trying to find out how many work hours a
ticket has been opened only figuring in 8 hours a work day.

AJ

"Mike H" wrote:

Hi,

The *24 at the end is making the formula return a decimal so delete it and
then format the cell as time and you will get

4 hours 16 minutes and 34 seconds

=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:


This is my Q838 field: 3/3/2010 4:06:08 PM
This is my T838 field: 3/3/2010 8:22:42 PM
This is my function:
=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))*24

What I get is 16:06
what I would expect to get is 4 hours 16 minutes and 34 seconds

What did I do wrong in the function to get such a difference?

Hi,

This is longer but I think it's a lot more robust

=((NETWORKDAYS(Q838,T838)-1)*("17:30"-"8:30")+IF(WEEKDAY(T838,2)5,"17:30",MEDIAN(MOD(T8 38,1),"17:30","8:30"))-IF(WEEKDAY(Q838,2)5,"8:30",MEDIAN(MOD(Q838,1),"17 :30","8:30")))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

I'm not really a liker of that formula you used but as it 'seemed' to be
doing what you wanted I never altered it. I prefer this formatted as [h]:mm:ss

Now at the start of the formula you will se 17.5 and this refers to the end
of your working day 17:30 or 5:30 PM

About a 1/3rd of the way through the formula note the 8.5, this is the start
of your day 08:30.

Change these to to meet your requirements

=SUM(17.5/24-MOD(Q838,INT(Q838)),MOD(T838,INT(B1))-8.5/24)+(NETWORKDAYS(Q838,T838)-2)*9/24
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:

Mike,

It doesn't seem to be spanning days

for example Q32636 is 5/4/2010 01:00am (march 5 2010 1am start of business)
T32636 is 09/04/2010 10:00 (end of business day)

using that function I get 9:00:00 its only subtracting the two times. I
would expect 40:00:00 for 40 work hours for the week. Or should I be looking
at a macro to do this? Remember im trying to find out how many work hours a
ticket has been opened only figuring in 8 hours a work day.

AJ

"Mike H" wrote:

Hi,

The *24 at the end is making the formula return a decimal so delete it and
then format the cell as time and you will get

4 hours 16 minutes and 34 seconds

=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:


This is my Q838 field: 3/3/2010 4:06:08 PM
This is my T838 field: 3/3/2010 8:22:42 PM
This is my function:
=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))*24

What I get is 16:06
what I would expect to get is 4 hours 16 minutes and 34 seconds

What did I do wrong in the function to get such a difference?

Mike this is awesome and exactly what im looking for but im geting an extra
hour per day which I would expect since 8-5 is 9 hours. Now I need to
figure out how to account for lunch thanks again this script is much better
than we originally started. and im for the most part understanding it all,
the If is a new curve that i need to logically go through in my head thanks
again.

"Mike H" wrote:

Hi,

This is longer but I think it's a lot more robust

=((NETWORKDAYS(Q838,T838)-1)*("17:30"-"8:30")+IF(WEEKDAY(T838,2)5,"17:30",MEDIAN(MOD(T8 38,1),"17:30","8:30"))-IF(WEEKDAY(Q838,2)5,"8:30",MEDIAN(MOD(Q838,1),"17 :30","8:30")))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

I'm not really a liker of that formula you used but as it 'seemed' to be
doing what you wanted I never altered it. I prefer this formatted as [h]:mm:ss

Now at the start of the formula you will se 17.5 and this refers to the end
of your working day 17:30 or 5:30 PM

About a 1/3rd of the way through the formula note the 8.5, this is the start
of your day 08:30.

Change these to to meet your requirements

=SUM(17.5/24-MOD(Q838,INT(Q838)),MOD(T838,INT(B1))-8.5/24)+(NETWORKDAYS(Q838,T838)-2)*9/24
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:

Mike,

It doesn't seem to be spanning days

for example Q32636 is 5/4/2010 01:00am (march 5 2010 1am start of business)
T32636 is 09/04/2010 10:00 (end of business day)

using that function I get 9:00:00 its only subtracting the two times. I
would expect 40:00:00 for 40 work hours for the week. Or should I be looking
at a macro to do this? Remember im trying to find out how many work hours a
ticket has been opened only figuring in 8 hours a work day.

AJ

"Mike H" wrote:

Hi,

The *24 at the end is making the formula return a decimal so delete it and
then format the cell as time and you will get

4 hours 16 minutes and 34 seconds

=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:


This is my Q838 field: 3/3/2010 4:06:08 PM
This is my T838 field: 3/3/2010 8:22:42 PM
This is my function:
=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))*24

What I get is 16:06
what I would expect to get is 4 hours 16 minutes and 34 seconds

What did I do wrong in the function to get such a difference?

Glad I could help. Not sure how/if this can be solved for a lunchbreak, I'll
have to think about it.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:

Mike this is awesome and exactly what im looking for but im geting an extra
hour per day which I would expect since 8-5 is 9 hours. Now I need to
figure out how to account for lunch thanks again this script is much better
than we originally started. and im for the most part understanding it all,
the If is a new curve that i need to logically go through in my head thanks
again.

"Mike H" wrote:

Hi,

This is longer but I think it's a lot more robust

=((NETWORKDAYS(Q838,T838)-1)*("17:30"-"8:30")+IF(WEEKDAY(T838,2)5,"17:30",MEDIAN(MOD(T8 38,1),"17:30","8:30"))-IF(WEEKDAY(Q838,2)5,"8:30",MEDIAN(MOD(Q838,1),"17 :30","8:30")))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

I'm not really a liker of that formula you used but as it 'seemed' to be
doing what you wanted I never altered it. I prefer this formatted as [h]:mm:ss

Now at the start of the formula you will se 17.5 and this refers to the end
of your working day 17:30 or 5:30 PM

About a 1/3rd of the way through the formula note the 8.5, this is the start
of your day 08:30.

Change these to to meet your requirements

=SUM(17.5/24-MOD(Q838,INT(Q838)),MOD(T838,INT(B1))-8.5/24)+(NETWORKDAYS(Q838,T838)-2)*9/24
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:

Mike,

It doesn't seem to be spanning days

for example Q32636 is 5/4/2010 01:00am (march 5 2010 1am start of business)
T32636 is 09/04/2010 10:00 (end of business day)

using that function I get 9:00:00 its only subtracting the two times. I
would expect 40:00:00 for 40 work hours for the week. Or should I be looking
at a macro to do this? Remember im trying to find out how many work hours a
ticket has been opened only figuring in 8 hours a work day.

AJ

"Mike H" wrote:

Hi,

The *24 at the end is making the formula return a decimal so delete it and
then format the cell as time and you will get

4 hours 16 minutes and 34 seconds

=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:


This is my Q838 field: 3/3/2010 4:06:08 PM
This is my T838 field: 3/3/2010 8:22:42 PM
This is my function:
=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))*24

What I get is 16:06
what I would expect to get is 4 hours 16 minutes and 34 seconds

What did I do wrong in the function to get such a difference?

initially i was thinking of just doing a -1 somewhere to off set the hours
for the day but then i thought why do that its one hour per day figure out
how many total days then minus that from the end result. But if you think of
a way please send me an email to jessopataoldotcom thanks again for all the
help.


"Mike H" wrote:

Glad I could help. Not sure how/if this can be solved for a lunchbreak, I'll
have to think about it.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:

Mike this is awesome and exactly what im looking for but im geting an extra
hour per day which I would expect since 8-5 is 9 hours. Now I need to
figure out how to account for lunch thanks again this script is much better
than we originally started. and im for the most part understanding it all,
the If is a new curve that i need to logically go through in my head thanks
again.

"Mike H" wrote:

Hi,

This is longer but I think it's a lot more robust

=((NETWORKDAYS(Q838,T838)-1)*("17:30"-"8:30")+IF(WEEKDAY(T838,2)5,"17:30",MEDIAN(MOD(T8 38,1),"17:30","8:30"))-IF(WEEKDAY(Q838,2)5,"8:30",MEDIAN(MOD(Q838,1),"17 :30","8:30")))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

I'm not really a liker of that formula you used but as it 'seemed' to be
doing what you wanted I never altered it. I prefer this formatted as [h]:mm:ss

Now at the start of the formula you will se 17.5 and this refers to the end
of your working day 17:30 or 5:30 PM

About a 1/3rd of the way through the formula note the 8.5, this is the start
of your day 08:30.

Change these to to meet your requirements

=SUM(17.5/24-MOD(Q838,INT(Q838)),MOD(T838,INT(B1))-8.5/24)+(NETWORKDAYS(Q838,T838)-2)*9/24
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:

Mike,

It doesn't seem to be spanning days

for example Q32636 is 5/4/2010 01:00am (march 5 2010 1am start of business)
T32636 is 09/04/2010 10:00 (end of business day)

using that function I get 9:00:00 its only subtracting the two times. I
would expect 40:00:00 for 40 work hours for the week. Or should I be looking
at a macro to do this? Remember im trying to find out how many work hours a
ticket has been opened only figuring in 8 hours a work day.

AJ

"Mike H" wrote:

Hi,

The *24 at the end is making the formula return a decimal so delete it and
then format the cell as time and you will get

4 hours 16 minutes and 34 seconds

=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"AJ" wrote:


This is my Q838 field: 3/3/2010 4:06:08 PM
This is my T838 field: 3/3/2010 8:22:42 PM
This is my function:
=((NETWORKDAYS(Q838,T838)-1)*("10:00"-"01:00")+MOD(T838,1)-MOD(Q838,1))*24

What I get is 16:06
what I would expect to get is 4 hours 16 minutes and 34 seconds

What did I do wrong in the function to get such a difference?