Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate difference between 2 date and times with average | Excel Discussion (Misc queries) | |||
How to calculate in weeks and days? | Excel Discussion (Misc queries) | |||
need help with formula | Excel Discussion (Misc queries) | |||
Function to calculate the number of years, months and days between | Excel Worksheet Functions | |||
how do I calculate the days between dates? | Excel Discussion (Misc queries) |