How do I calculate a difference in work days?
 

I have created a spreadsheet in which there is a committed ship date as
opposed to the actual ship date. However, I need this calculated difference
to be in work days. Does anyone have a formula for this?

How do I calculate a difference in work days?
 

Look in help for NETWORKDAYS, it's part of ATP which comes with Excel


--

Regards,

Peo Sjoblom


"Work Days" <Work wrote in message
...
I have created a spreadsheet in which there is a committed ship date as
opposed to the actual ship date. However, I need this calculated
difference
to be in work days. Does anyone have a formula for this?

How do I calculate a difference in work days?
 

Thanks Peo. That worked. I appreciate your time and expertise:)


"Peo Sjoblom" wrote:

Look in help for NETWORKDAYS, it's part of ATP which comes with Excel


--

Regards,

Peo Sjoblom


"Work Days" <Work wrote in message
...
I have created a spreadsheet in which there is a committed ship date as
opposed to the actual ship date. However, I need this calculated
difference
to be in work days. Does anyone have a formula for this?

How do I calculate a difference in work days?
 

HI,
I need a formula to calculate the work days between 2 days, based on a
parameter. so if its 1 then the workdays formula works, but if its 2 then
only Sunday is a holiday. any suggestions?
also i want work days for each month from start date to end date.
-M

"Peo Sjoblom" wrote:

Look in help for NETWORKDAYS, it's part of ATP which comes with Excel


--

Regards,

Peo Sjoblom


"Work Days" <Work wrote in message
...
I have created a spreadsheet in which there is a committed ship date as
opposed to the actual ship date. However, I need this calculated
difference
to be in work days. Does anyone have a formula for this?

How do I calculate a difference in work days?
 

"M" wrote:

HI,
I need a formula to calculate the work days between 2 days, based on a
parameter. so if its 1 then the workdays formula works, but if its 2 then
only Sunday is a holiday. any suggestions?
also i want work days for each month from start date to end date.
-M

"Peo Sjoblom" wrote:

Look in help for NETWORKDAYS, it's part of ATP which comes with Excel


--

Regards,

Peo Sjoblom


"Work Days" <Work wrote in message
...
I have created a spreadsheet in which there is a committed ship date as
opposed to the actual ship date. However, I need this calculated
difference
to be in work days. Does anyone have a formula for this?



Hi,
The help function for workdays does not say anything about calculating work
days that includes Saturday as a work day.
-M

How do I calculate a difference in work days?
 

Does anyone know how you would calculate work days in hrs. So if the work day
is from 8am to 5pm and I want to calculate to number of hours between 2
dates. Any assistance on this would be appreciated.

"Work Days" wrote:

I have created a spreadsheet in which there is a committed ship date as
opposed to the actual ship date. However, I need this calculated difference
to be in work days. Does anyone have a formula for this?

How do I calculate a difference in work days?
 

This is exactly what we're looking for.... was there an answer?

"Inquiring Exceler" wrote:

Does anyone know how you would calculate work days in hrs. So if the work day
is from 8am to 5pm and I want to calculate to number of hours between 2
dates. Any assistance on this would be appreciated.

"Work Days" wrote:

I have created a spreadsheet in which there is a committed ship date as
opposed to the actual ship date. However, I need this calculated difference
to be in work days. Does anyone have a formula for this?

How do I calculate a difference in work days?
 

First you count the number of workdays, then multiply by 9 to get hours
worked on those days.

=NETWORKDAYS(A2,A14) where A2 is sept 11, 2008 and A14 is sept 23, 2008

Returns 9 days

Formula is =NETWORKDAYS(A2,A14)*9 returns 81 hours.

If employees get docked one hour for lunch, multiply by 8

NETWORKDAYS needs the Anaalysis Toolpak add-in loaded.


Gord Dibben MS Excel MVP

On Thu, 8 Jan 2009 15:44:50 -0800, DAH
wrote:

This is exactly what we're looking for.... was there an answer?

"Inquiring Exceler" wrote:

Does anyone know how you would calculate work days in hrs. So if the work day
is from 8am to 5pm and I want to calculate to number of hours between 2
dates. Any assistance on this would be appreciated.

"Work Days" wrote:

I have created a spreadsheet in which there is a committed ship date as
opposed to the actual ship date. However, I need this calculated difference
to be in work days. Does anyone have a formula for this?

How do I calculate a difference in work days?
 

Ok I tried that: =NETWORKDAYS(D7,E7)*8 where D7 = 10/2/2008 9:22 and E7 =
10/2/2008 11:24 and I received 192:00 as the answer.

The answer should be 2 hour and 2 minutes or 2:02.

Why did this not work? I need to make sure we are not counting weekends.
Would be nice if we could discount Holidays as well.


"Gord Dibben" wrote:

First you count the number of workdays, then multiply by 9 to get hours
worked on those days.

=NETWORKDAYS(A2,A14) where A2 is sept 11, 2008 and A14 is sept 23, 2008

Returns 9 days

Formula is =NETWORKDAYS(A2,A14)*9 returns 81 hours.

If employees get docked one hour for lunch, multiply by 8

NETWORKDAYS needs the Anaalysis Toolpak add-in loaded.


Gord Dibben MS Excel MVP

On Thu, 8 Jan 2009 15:44:50 -0800, DAH
wrote:

This is exactly what we're looking for.... was there an answer?

"Inquiring Exceler" wrote:

Does anyone know how you would calculate work days in hrs. So if the work day
is from 8am to 5pm and I want to calculate to number of hours between 2
dates. Any assistance on this would be appreciated.

"Work Days" wrote:

I have created a spreadsheet in which there is a committed ship date as
opposed to the actual ship date. However, I need this calculated difference
to be in work days. Does anyone have a formula for this?

How do I calculate a difference in work days?
 

NETWORKDAYS does not count Saturday and Sunday and there is a parameter to
discount holidays.

See help on networkdays functions.

Networkdays counts full days only.

You are trying to count hours worked during the same day.

I receive 0 from your posted values and formula.

Don't know how you get 192:00

For more on performing arithmetic with dates and time see Chip Pearson's
site.

http://www.cpearson.com/excel/datearith.htm


Gord

On Thu, 8 Jan 2009 16:32:21 -0800, DAH
wrote:


Ok I tried that: =NETWORKDAYS(D7,E7)*8 where D7 = 10/2/2008 9:22 and E7 =
10/2/2008 11:24 and I received 192:00 as the answer.

The answer should be 2 hour and 2 minutes or 2:02.

Why did this not work? I need to make sure we are not counting weekends.
Would be nice if we could discount Holidays as well.


"Gord Dibben" wrote:

First you count the number of workdays, then multiply by 9 to get hours
worked on those days.

=NETWORKDAYS(A2,A14) where A2 is sept 11, 2008 and A14 is sept 23, 2008

Returns 9 days

Formula is =NETWORKDAYS(A2,A14)*9 returns 81 hours.

If employees get docked one hour for lunch, multiply by 8

NETWORKDAYS needs the Anaalysis Toolpak add-in loaded.


Gord Dibben MS Excel MVP

On Thu, 8 Jan 2009 15:44:50 -0800, DAH
wrote:

This is exactly what we're looking for.... was there an answer?

"Inquiring Exceler" wrote:

Does anyone know how you would calculate work days in hrs. So if the work day
is from 8am to 5pm and I want to calculate to number of hours between 2
dates. Any assistance on this would be appreciated.

"Work Days" wrote:

I have created a spreadsheet in which there is a committed ship date as
opposed to the actual ship date. However, I need this calculated difference
to be in work days. Does anyone have a formula for this?

How do I calculate a difference in work days?
 

So any idea how to do both calculations within the same day and between more
than 1 day?

"Gord Dibben" wrote:

NETWORKDAYS does not count Saturday and Sunday and there is a parameter to
discount holidays.

See help on networkdays functions.

Networkdays counts full days only.

You are trying to count hours worked during the same day.

I receive 0 from your posted values and formula.

Don't know how you get 192:00

For more on performing arithmetic with dates and time see Chip Pearson's
site.

http://www.cpearson.com/excel/datearith.htm


Gord

On Thu, 8 Jan 2009 16:32:21 -0800, DAH
wrote:


Ok I tried that: =NETWORKDAYS(D7,E7)*8 where D7 = 10/2/2008 9:22 and E7 =
10/2/2008 11:24 and I received 192:00 as the answer.

The answer should be 2 hour and 2 minutes or 2:02.

Why did this not work? I need to make sure we are not counting weekends.
Would be nice if we could discount Holidays as well.


"Gord Dibben" wrote:

First you count the number of workdays, then multiply by 9 to get hours
worked on those days.

=NETWORKDAYS(A2,A14) where A2 is sept 11, 2008 and A14 is sept 23, 2008

Returns 9 days

Formula is =NETWORKDAYS(A2,A14)*9 returns 81 hours.

If employees get docked one hour for lunch, multiply by 8

NETWORKDAYS needs the Anaalysis Toolpak add-in loaded.


Gord Dibben MS Excel MVP

On Thu, 8 Jan 2009 15:44:50 -0800, DAH
wrote:

This is exactly what we're looking for.... was there an answer?

"Inquiring Exceler" wrote:

Does anyone know how you would calculate work days in hrs. So if the work day
is from 8am to 5pm and I want to calculate to number of hours between 2
dates. Any assistance on this would be appreciated.

"Work Days" wrote:

I have created a spreadsheet in which there is a committed ship date as
opposed to the actual ship date. However, I need this calculated difference
to be in work days. Does anyone have a formula for this?

How do I calculate a difference in work days?
 

He's getting 192:00 because the NETWORKDAYS function is returning one day,
and it's been multiplied by 8, so the answer is 8 (meaning 8 hours), but
instead of formatting the answer as General or Number (as you intended) the
OP has presumably formatted the result as [h]:mm, thus causing what you
intended as a number of hours to be regarded as a number of days. 8 days is
192 hours.
--
David Biddulph

Gord Dibben wrote:
NETWORKDAYS does not count Saturday and Sunday and there is a
parameter to discount holidays.

See help on networkdays functions.

Networkdays counts full days only.

You are trying to count hours worked during the same day.

I receive 0 from your posted values and formula.

Don't know how you get 192:00

For more on performing arithmetic with dates and time see Chip
Pearson's site.

http://www.cpearson.com/excel/datearith.htm


Gord

On Thu, 8 Jan 2009 16:32:21 -0800, DAH
wrote:


Ok I tried that: =NETWORKDAYS(D7,E7)*8 where D7 = 10/2/2008 9:22
and E7 = 10/2/2008 11:24 and I received 192:00 as the answer.

The answer should be 2 hour and 2 minutes or 2:02.

Why did this not work? I need to make sure we are not counting
weekends. Would be nice if we could discount Holidays as well.


"Gord Dibben" wrote:

First you count the number of workdays, then multiply by 9 to get
hours worked on those days.

=NETWORKDAYS(A2,A14) where A2 is sept 11, 2008 and A14 is sept 23,
2008

Returns 9 days

Formula is =NETWORKDAYS(A2,A14)*9 returns 81 hours.

If employees get docked one hour for lunch, multiply by 8

NETWORKDAYS needs the Anaalysis Toolpak add-in loaded.


Gord Dibben MS Excel MVP

On Thu, 8 Jan 2009 15:44:50 -0800, DAH
wrote:

This is exactly what we're looking for.... was there an answer?

"Inquiring Exceler" wrote:

Does anyone know how you would calculate work days in hrs. So if
the work day is from 8am to 5pm and I want to calculate to number
of hours between 2 dates. Any assistance on this would be
appreciated.

"Work Days" wrote:

I have created a spreadsheet in which there is a committed ship
date as opposed to the actual ship date. However, I need this
calculated difference to be in work days. Does anyone have a
formula for this?

How do I calculate a difference in work days?
 

Thanks David.

I tested with improper cell formatting.


Gord

On Fri, 9 Jan 2009 09:04:03 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

He's getting 192:00 because the NETWORKDAYS function is returning one day,
and it's been multiplied by 8, so the answer is 8 (meaning 8 hours), but
instead of formatting the answer as General or Number (as you intended) the
OP has presumably formatted the result as [h]:mm, thus causing what you
intended as a number of hours to be regarded as a number of days. 8 days is
192 hours.

How do I calculate a difference in work days?
 

I found this through further research, but I believe it doesn't take into
account when something is received on a holiday. It takes into consideration
an 8 hour day starting from 8:00 a.m. - 17:00 and taking into consideration
our company holidays (C1:C8).

=(NETWORKDAYS(A1,A2,C1:C8)-1)*(B2-B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1), B2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1)

dah I'm a she by the way... :)

"David Biddulph" wrote:

He's getting 192:00 because the NETWORKDAYS function is returning one day,
and it's been multiplied by 8, so the answer is 8 (meaning 8 hours), but
instead of formatting the answer as General or Number (as you intended) the
OP has presumably formatted the result as [h]:mm, thus causing what you
intended as a number of hours to be regarded as a number of days. 8 days is
192 hours.
--
David Biddulph

Gord Dibben wrote:
NETWORKDAYS does not count Saturday and Sunday and there is a
parameter to discount holidays.

See help on networkdays functions.

Networkdays counts full days only.

You are trying to count hours worked during the same day.

I receive 0 from your posted values and formula.

Don't know how you get 192:00

For more on performing arithmetic with dates and time see Chip
Pearson's site.

http://www.cpearson.com/excel/datearith.htm


Gord

On Thu, 8 Jan 2009 16:32:21 -0800, DAH
wrote:


Ok I tried that: =NETWORKDAYS(D7,E7)*8 where D7 = 10/2/2008 9:22
and E7 = 10/2/2008 11:24 and I received 192:00 as the answer.

The answer should be 2 hour and 2 minutes or 2:02.

Why did this not work? I need to make sure we are not counting
weekends. Would be nice if we could discount Holidays as well.


"Gord Dibben" wrote:

First you count the number of workdays, then multiply by 9 to get
hours worked on those days.

=NETWORKDAYS(A2,A14) where A2 is sept 11, 2008 and A14 is sept 23,
2008

Returns 9 days

Formula is =NETWORKDAYS(A2,A14)*9 returns 81 hours.

If employees get docked one hour for lunch, multiply by 8

NETWORKDAYS needs the Anaalysis Toolpak add-in loaded.


Gord Dibben MS Excel MVP

On Thu, 8 Jan 2009 15:44:50 -0800, DAH
wrote:

This is exactly what we're looking for.... was there an answer?

"Inquiring Exceler" wrote:

Does anyone know how you would calculate work days in hrs. So if
the work day is from 8am to 5pm and I want to calculate to number
of hours between 2 dates. Any assistance on this would be
appreciated.

"Work Days" wrote:

I have created a spreadsheet in which there is a committed ship
date as opposed to the actual ship date. However, I need this
calculated difference to be in work days. Does anyone have a
formula for this?

How do I calculate a difference in work days?
 

Rescind the foregoing.

=networkdays(d7,e7) returns 0 for me when D7 and E7 are same day with the
times as OP posted


Gord

On Fri, 09 Jan 2009 09:33:52 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Thanks David.

I tested with improper cell formatting.


Gord

On Fri, 9 Jan 2009 09:04:03 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

He's getting 192:00 because the NETWORKDAYS function is returning one day,
and it's been multiplied by 8, so the answer is 8 (meaning 8 hours), but
instead of formatting the answer as General or Number (as you intended) the
OP has presumably formatted the result as [h]:mm, thus causing what you
intended as a number of hours to be regarded as a number of days. 8 days is
192 hours.

How do I calculate a difference in work days?
 

AHA!

10/02/2008 is a Sunday so of course I get a zero.


Gord

On Fri, 09 Jan 2009 09:46:03 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Rescind the foregoing.

=networkdays(d7,e7) returns 0 for me when D7 and E7 are same day with the
times as OP posted


Gord

On Fri, 09 Jan 2009 09:33:52 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Thanks David.

I tested with improper cell formatting.


Gord

On Fri, 9 Jan 2009 09:04:03 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

He's getting 192:00 because the NETWORKDAYS function is returning one day,
and it's been multiplied by 8, so the answer is 8 (meaning 8 hours), but
instead of formatting the answer as General or Number (as you intended) the
OP has presumably formatted the result as [h]:mm, thus causing what you
intended as a number of hours to be regarded as a number of days. 8 days is
192 hours.

How do I calculate a difference in work days?
 

Maybe???

=IF(NETWORKDAYS(D7,E7)=1,((E7-D7)*24),NETWORKDAYS(D7,E7)*8)


Gord


On Thu, 8 Jan 2009 18:47:01 -0800, DAH
wrote:

So any idea how to do both calculations within the same day and between more
than 1 day?

"Gord Dibben" wrote:

NETWORKDAYS does not count Saturday and Sunday and there is a parameter to
discount holidays.

See help on networkdays functions.

Networkdays counts full days only.

You are trying to count hours worked during the same day.

I receive 0 from your posted values and formula.

Don't know how you get 192:00

For more on performing arithmetic with dates and time see Chip Pearson's
site.

http://www.cpearson.com/excel/datearith.htm


Gord

On Thu, 8 Jan 2009 16:32:21 -0800, DAH
wrote:


Ok I tried that: =NETWORKDAYS(D7,E7)*8 where D7 = 10/2/2008 9:22 and E7 =
10/2/2008 11:24 and I received 192:00 as the answer.

The answer should be 2 hour and 2 minutes or 2:02.

Why did this not work? I need to make sure we are not counting weekends.
Would be nice if we could discount Holidays as well.


"Gord Dibben" wrote:

First you count the number of workdays, then multiply by 9 to get hours
worked on those days.

=NETWORKDAYS(A2,A14) where A2 is sept 11, 2008 and A14 is sept 23, 2008

Returns 9 days

Formula is =NETWORKDAYS(A2,A14)*9 returns 81 hours.

If employees get docked one hour for lunch, multiply by 8

NETWORKDAYS needs the Anaalysis Toolpak add-in loaded.


Gord Dibben MS Excel MVP

On Thu, 8 Jan 2009 15:44:50 -0800, DAH
wrote:

This is exactly what we're looking for.... was there an answer?

"Inquiring Exceler" wrote:

Does anyone know how you would calculate work days in hrs. So if the work day
is from 8am to 5pm and I want to calculate to number of hours between 2
dates. Any assistance on this would be appreciated.

"Work Days" wrote:

I have created a spreadsheet in which there is a committed ship date as
opposed to the actual ship date. However, I need this calculated difference
to be in work days. Does anyone have a formula for this?

How do I calculate a difference in work days?
 

I see in a later post you found what you were looking for from Mike H.


Gord

On Thu, 8 Jan 2009 18:47:01 -0800, DAH
wrote:

So any idea how to do both calculations within the same day and between more
than 1 day?

"Gord Dibben" wrote:

NETWORKDAYS does not count Saturday and Sunday and there is a parameter to
discount holidays.

See help on networkdays functions.

Networkdays counts full days only.

You are trying to count hours worked during the same day.

I receive 0 from your posted values and formula.

Don't know how you get 192:00

For more on performing arithmetic with dates and time see Chip Pearson's
site.

http://www.cpearson.com/excel/datearith.htm


Gord

On Thu, 8 Jan 2009 16:32:21 -0800, DAH
wrote:


Ok I tried that: =NETWORKDAYS(D7,E7)*8 where D7 = 10/2/2008 9:22 and E7 =
10/2/2008 11:24 and I received 192:00 as the answer.

The answer should be 2 hour and 2 minutes or 2:02.

Why did this not work? I need to make sure we are not counting weekends.
Would be nice if we could discount Holidays as well.


"Gord Dibben" wrote:

First you count the number of workdays, then multiply by 9 to get hours
worked on those days.

=NETWORKDAYS(A2,A14) where A2 is sept 11, 2008 and A14 is sept 23, 2008

Returns 9 days

Formula is =NETWORKDAYS(A2,A14)*9 returns 81 hours.

If employees get docked one hour for lunch, multiply by 8

NETWORKDAYS needs the Anaalysis Toolpak add-in loaded.


Gord Dibben MS Excel MVP

On Thu, 8 Jan 2009 15:44:50 -0800, DAH
wrote:

This is exactly what we're looking for.... was there an answer?

"Inquiring Exceler" wrote:

Does anyone know how you would calculate work days in hrs. So if the work day
is from 8am to 5pm and I want to calculate to number of hours between 2
dates. Any assistance on this would be appreciated.

"Work Days" wrote:

I have created a spreadsheet in which there is a committed ship date as
opposed to the actual ship date. However, I need this calculated difference
to be in work days. Does anyone have a formula for this?