|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
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 <DAH@discussions.microsoft.com>
> 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?
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
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 <DAH@discussions.microsoft.com>
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 <DAH@discussions.microsoft.com>
>> 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?
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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 <DAH@discussions.microsoft.com>
> 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 <DAH@discussions.microsoft.com>
> >> 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?
> >>
> >>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Maybe???
=IF(NETWORKDAYS(D7,E7)=1,((E7-D7)*24),NETWORKDAYS(D7,E7)*8)
Gord
On Thu, 8 Jan 2009 18:47:01 -0800, DAH <DAH@discussions.microsoft.com>
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 <DAH@discussions.microsoft.com>
>> 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 <DAH@discussions.microsoft.com>
>> >> 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?
>> >>
>> >>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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 <DAH@discussions.microsoft.com>
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 <DAH@discussions.microsoft.com>
>> 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 <DAH@discussions.microsoft.com>
>> >> 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?
>> >>
>> >>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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 <DAH@discussions.microsoft.com>
> 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
>>> <DAH@discussions.microsoft.com> 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?
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
Question
Answer
Notify me of replies