A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

workday, networkday or something else?



 
 
Thread Tools Display Modes
  #1  
Old April 14th, 2010, 03:08 PM posted to microsoft.public.excel.worksheet.functions
aj
external usenet poster
 
Posts: 333
Default workday, networkday or something else?

I have two date and time fields, the Q column is the Ordered Date and the T
colum is the Date Closed. We work 8 hours a day from 8-5. Im trying to
figure out how many hours / minutes it took someone to do the work from the
time it went ordered to the point it closed. Taking into account our work
day, which function should i use one of the ones i listed or other ones?

  #2  
Old April 14th, 2010, 03:55 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default workday, networkday or something else?

Hi,

Try this.

the formula 'assumes' that you won't take/close any orders outside of the
workday. i.e. if you working days starts at 08:00 you won't take an order at
07:00 on that day.

Holidays is a named range of any holiday dates to exclude

=((NETWORKDAYS(Q1,T1,Holidays)-1)*("16:00"-"08:00")+MOD(T1,1)-MOD(Q1,1))*24
--
Mike

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


"AJ" wrote:

I have two date and time fields, the Q column is the Ordered Date and the T
colum is the Date Closed. We work 8 hours a day from 8-5. Im trying to
figure out how many hours / minutes it took someone to do the work from the
time it went ordered to the point it closed. Taking into account our work
day, which function should i use one of the ones i listed or other ones?

  #3  
Old April 15th, 2010, 01:45 PM posted to microsoft.public.excel.worksheet.functions
aj
external usenet poster
 
Posts: 333
Default workday, networkday or something else?

Mike,

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

I would expect 4 hours 16 minutes and 34 seconds and change but what i get
is 16:06



"Mike H" wrote:

Hi,

Try this.

the formula 'assumes' that you won't take/close any orders outside of the
workday. i.e. if you working days starts at 08:00 you won't take an order at
07:00 on that day.

Holidays is a named range of any holiday dates to exclude

=((NETWORKDAYS(Q1,T1,Holidays)-1)*("16:00"-"08:00")+MOD(T1,1)-MOD(Q1,1))*24
--
Mike

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


"AJ" wrote:

I have two date and time fields, the Q column is the Ordered Date and the T
colum is the Date Closed. We work 8 hours a day from 8-5. Im trying to
figure out how many hours / minutes it took someone to do the work from the
time it went ordered to the point it closed. Taking into account our work
day, which function should i use one of the ones i listed or other ones?

  #4  
Old April 15th, 2010, 01:48 PM posted to microsoft.public.excel.worksheet.functions
aj
external usenet poster
 
Posts: 333
Default workday, networkday or something else?

Mike,

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 H" wrote:

Hi,

Try this.

the formula 'assumes' that you won't take/close any orders outside of the
workday. i.e. if you working days starts at 08:00 you won't take an order at
07:00 on that day.

Holidays is a named range of any holiday dates to exclude

=((NETWORKDAYS(Q1,T1,Holidays)-1)*("16:00"-"08:00")+MOD(T1,1)-MOD(Q1,1))*24
--
Mike

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


"AJ" wrote:

I have two date and time fields, the Q column is the Ordered Date and the T
colum is the Date Closed. We work 8 hours a day from 8-5. Im trying to
figure out how many hours / minutes it took someone to do the work from the
time it went ordered to the point it closed. Taking into account our work
day, which function should i use one of the ones i listed or other ones?

  #5  
Old April 20th, 2010, 02:50 AM posted to microsoft.public.excel.worksheet.functions
Jakob[_2_]
external usenet poster
 
Posts: 3
Default workday, networkday or something else?

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

--
Med hilsen


Jakob Austgulen
http://www.pointshop.no/austgulen


"AJ" skrev i melding
...
Mike,

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 H" wrote:

Hi,

Try this.

the formula 'assumes' that you won't take/close any orders outside of the
workday. i.e. if you working days starts at 08:00 you won't take an order
at
07:00 on that day.

Holidays is a named range of any holiday dates to exclude

=((NETWORKDAYS(Q1,T1,Holidays)-1)*("16:00"-"08:00")+MOD(T1,1)-MOD(Q1,1))*24
--
Mike

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


"AJ" wrote:

I have two date and time fields, the Q column is the Ordered Date and
the T
colum is the Date Closed. We work 8 hours a day from 8-5. Im trying
to
figure out how many hours / minutes it took someone to do the work from
the
time it went ordered to the point it closed. Taking into account our
work
day, which function should i use one of the ones i listed or other
ones?


  #6  
Old April 20th, 2010, 07:46 AM posted to microsoft.public.excel.worksheet.functions
Bernd P
external usenet poster
 
Posts: 613
Default workday, networkday or something else?

Hello,

I suggest to use my UDF count_hours:
http://sulprobil.com/html/count_hours.html

Regards,
Bernd
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.