Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
I am trying to figure out how to subtract a cell containing a mm/dd/yyyy
hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
This should work option 31 days
=NETWORKDAYS(A1,A2)-1+MOD(A2,1)-MOD(A1,1) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
that should be upto :-)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
Bob, that worked great except I know have one minor quirk. When the
'received' date happens to fall on a weekend day, I (understandibly) get a negative result (########). Is there anything I can add to the formula to prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE statement? THANKS! "Bob Phillips" wrote: that should be upto :-) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
I think I figured it out. This formula seems to be working -
=IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1))) Thanks for all your help Bob!!! "Diane13" wrote: Bob, that worked great except I know have one minor quirk. When the 'received' date happens to fall on a weekend day, I (understandibly) get a negative result (########). Is there anything I can add to the formula to prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE statement? THANKS! "Bob Phillips" wrote: that should be upto :-) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
There seem to be various problems with weekend dates. This seems more robust
=NETWORKDAYS(I2,J2)-((WEEKDAY(J2,2)<6)*1)+IF(WEEKDAY(I2,2)<6,MOD(J2,1)-MOD(I 2,1),0) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I think I figured it out. This formula seems to be working - =IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1)) ) Thanks for all your help Bob!!! "Diane13" wrote: Bob, that worked great except I know have one minor quirk. When the 'received' date happens to fall on a weekend day, I (understandibly) get a negative result (########). Is there anything I can add to the formula to prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE statement? THANKS! "Bob Phillips" wrote: that should be upto :-) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
That formula worked on everything but these four date ranges:
I J 8/19/2006 22:43 8/21/2006 8:22 7/30/2006 16:21 7/31/2006 15:33 7/29/2006 15:16 7/31/2006 7:48 8/27/2006 22:55 8/28/2006 9:17 These all resulted in 0 0:00 and I am not sure why. However, I have what I need for my project so thank you so much for all your help!!! I would have never figured out the MOD command on my own. Thanks! "Bob Phillips" wrote: There seem to be various problems with weekend dates. This seems more robust =NETWORKDAYS(I2,J2)-((WEEKDAY(J2,2)<6)*1)+IF(WEEKDAY(I2,2)<6,MOD(J2,1)-MOD(I 2,1),0) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I think I figured it out. This formula seems to be working - =IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1)) ) Thanks for all your help Bob!!! "Diane13" wrote: Bob, that worked great except I know have one minor quirk. When the 'received' date happens to fall on a weekend day, I (understandibly) get a negative result (########). Is there anything I can add to the formula to prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE statement? THANKS! "Bob Phillips" wrote: that should be upto :-) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
I told you weekedns were a problem <bg
I think this works for all situations =NETWORKDAYS(I2,J2)-(WEEKDAY(I2,2)<6) +(1-MOD(I2,1))*(WEEKDAY(I2,2)<6) +(MOD(J2,1)-1)*(WEEKDAY(J2,2)<6) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Diane13" wrote in message ... That formula worked on everything but these four date ranges: I J 8/19/2006 22:43 8/21/2006 8:22 7/30/2006 16:21 7/31/2006 15:33 7/29/2006 15:16 7/31/2006 7:48 8/27/2006 22:55 8/28/2006 9:17 These all resulted in 0 0:00 and I am not sure why. However, I have what I need for my project so thank you so much for all your help!!! I would have never figured out the MOD command on my own. Thanks! "Bob Phillips" wrote: There seem to be various problems with weekend dates. This seems more robust =NETWORKDAYS(I2,J2)-((WEEKDAY(J2,2)<6)*1)+IF(WEEKDAY(I2,2)<6,MOD(J2,1)-MOD(I 2,1),0) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I think I figured it out. This formula seems to be working - =IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1)) ) Thanks for all your help Bob!!! "Diane13" wrote: Bob, that worked great except I know have one minor quirk. When the 'received' date happens to fall on a weekend day, I (understandibly) get a negative result (########). Is there anything I can add to the formula to prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE statement? THANKS! "Bob Phillips" wrote: that should be upto :-) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
Hi Bob,
When I try this formula I get a name error. do you know why? "Bob Phillips" wrote: I told you weekedns were a problem <bg I think this works for all situations =NETWORKDAYS(I2,J2)-(WEEKDAY(I2,2)<6) +(1-MOD(I2,1))*(WEEKDAY(I2,2)<6) +(MOD(J2,1)-1)*(WEEKDAY(J2,2)<6) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Diane13" wrote in message ... That formula worked on everything but these four date ranges: I J 8/19/2006 22:43 8/21/2006 8:22 7/30/2006 16:21 7/31/2006 15:33 7/29/2006 15:16 7/31/2006 7:48 8/27/2006 22:55 8/28/2006 9:17 These all resulted in 0 0:00 and I am not sure why. However, I have what I need for my project so thank you so much for all your help!!! I would have never figured out the MOD command on my own. Thanks! "Bob Phillips" wrote: There seem to be various problems with weekend dates. This seems more robust =NETWORKDAYS(I2,J2)-((WEEKDAY(J2,2)<6)*1)+IF(WEEKDAY(I2,2)<6,MOD(J2,1)-MOD(I 2,1),0) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I think I figured it out. This formula seems to be working - =IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1)) ) Thanks for all your help Bob!!! "Diane13" wrote: Bob, that worked great except I know have one minor quirk. When the 'received' date happens to fall on a weekend day, I (understandibly) get a negative result (########). Is there anything I can add to the formula to prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE statement? THANKS! "Bob Phillips" wrote: that should be upto :-) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
Form XL 2003 Help when I search NETWORKDAYS:
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. Please come back if more info needed best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Colleen10" wrote in message ... Hi Bob, When I try this formula I get a name error. do you know why? "Bob Phillips" wrote: I told you weekedns were a problem <bg I think this works for all situations =NETWORKDAYS(I2,J2)-(WEEKDAY(I2,2)<6) +(1-MOD(I2,1))*(WEEKDAY(I2,2)<6) +(MOD(J2,1)-1)*(WEEKDAY(J2,2)<6) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Diane13" wrote in message ... That formula worked on everything but these four date ranges: I J 8/19/2006 22:43 8/21/2006 8:22 7/30/2006 16:21 7/31/2006 15:33 7/29/2006 15:16 7/31/2006 7:48 8/27/2006 22:55 8/28/2006 9:17 These all resulted in 0 0:00 and I am not sure why. However, I have what I need for my project so thank you so much for all your help!!! I would have never figured out the MOD command on my own. Thanks! "Bob Phillips" wrote: There seem to be various problems with weekend dates. This seems more robust =NETWORKDAYS(I2,J2)-((WEEKDAY(J2,2)<6)*1)+IF(WEEKDAY(I2,2)<6,MOD(J2,1)-MOD(I 2,1),0) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I think I figured it out. This formula seems to be working - =IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1)) ) Thanks for all your help Bob!!! "Diane13" wrote: Bob, that worked great except I know have one minor quirk. When the 'received' date happens to fall on a weekend day, I (understandibly) get a negative result (########). Is there anything I can add to the formula to prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE statement? THANKS! "Bob Phillips" wrote: that should be upto :-) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
Thank you...you're right, I forgot about that and I was recently upgrated to
Excel 2003. I still have the name error..would I need to reboot? "Bernard Liengme" wrote: Form XL 2003 Help when I search NETWORKDAYS: If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. Please come back if more info needed best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Colleen10" wrote in message ... Hi Bob, When I try this formula I get a name error. do you know why? "Bob Phillips" wrote: I told you weekedns were a problem <bg I think this works for all situations =NETWORKDAYS(I2,J2)-(WEEKDAY(I2,2)<6) +(1-MOD(I2,1))*(WEEKDAY(I2,2)<6) +(MOD(J2,1)-1)*(WEEKDAY(J2,2)<6) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Diane13" wrote in message ... That formula worked on everything but these four date ranges: I J 8/19/2006 22:43 8/21/2006 8:22 7/30/2006 16:21 7/31/2006 15:33 7/29/2006 15:16 7/31/2006 7:48 8/27/2006 22:55 8/28/2006 9:17 These all resulted in 0 0:00 and I am not sure why. However, I have what I need for my project so thank you so much for all your help!!! I would have never figured out the MOD command on my own. Thanks! "Bob Phillips" wrote: There seem to be various problems with weekend dates. This seems more robust =NETWORKDAYS(I2,J2)-((WEEKDAY(J2,2)<6)*1)+IF(WEEKDAY(I2,2)<6,MOD(J2,1)-MOD(I 2,1),0) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I think I figured it out. This formula seems to be working - =IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1)) ) Thanks for all your help Bob!!! "Diane13" wrote: Bob, that worked great except I know have one minor quirk. When the 'received' date happens to fall on a weekend day, I (understandibly) get a negative result (########). Is there anything I can add to the formula to prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE statement? THANKS! "Bob Phillips" wrote: that should be upto :-) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
Thank you so much...please disregard my previous email. When I re-typed the
formula, the name error went away however if the results are outside of 24 hours, I get ############ across the cells. Is there a special formatting I should be using? "Bernard Liengme" wrote: Form XL 2003 Help when I search NETWORKDAYS: If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. Please come back if more info needed best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Colleen10" wrote in message ... Hi Bob, When I try this formula I get a name error. do you know why? "Bob Phillips" wrote: I told you weekedns were a problem <bg I think this works for all situations =NETWORKDAYS(I2,J2)-(WEEKDAY(I2,2)<6) +(1-MOD(I2,1))*(WEEKDAY(I2,2)<6) +(MOD(J2,1)-1)*(WEEKDAY(J2,2)<6) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Diane13" wrote in message ... That formula worked on everything but these four date ranges: I J 8/19/2006 22:43 8/21/2006 8:22 7/30/2006 16:21 7/31/2006 15:33 7/29/2006 15:16 7/31/2006 7:48 8/27/2006 22:55 8/28/2006 9:17 These all resulted in 0 0:00 and I am not sure why. However, I have what I need for my project so thank you so much for all your help!!! I would have never figured out the MOD command on my own. Thanks! "Bob Phillips" wrote: There seem to be various problems with weekend dates. This seems more robust =NETWORKDAYS(I2,J2)-((WEEKDAY(J2,2)<6)*1)+IF(WEEKDAY(I2,2)<6,MOD(J2,1)-MOD(I 2,1),0) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I think I figured it out. This formula seems to be working - =IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1)) ) Thanks for all your help Bob!!! "Diane13" wrote: Bob, that worked great except I know have one minor quirk. When the 'received' date happens to fall on a weekend day, I (understandibly) get a negative result (########). Is there anything I can add to the formula to prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE statement? THANKS! "Bob Phillips" wrote: that should be upto :-) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
Try Tools | Add-in and if there is an Analysis Toolpak entry, then check its
box. Then just continue with Excel session If there is no entry: close Excel and use Control Panel | Add Delete Programs to change the Office 2003 install such that Analysis Toolpak gets installed -- might need the disk , can't remember. Restart Excel and use Tools | Add-in as above Never a need to reboot -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Colleen10" wrote in message ... Thank you...you're right, I forgot about that and I was recently upgrated to Excel 2003. I still have the name error..would I need to reboot? "Bernard Liengme" wrote: Form XL 2003 Help when I search NETWORKDAYS: If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. Please come back if more info needed best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Colleen10" wrote in message ... Hi Bob, When I try this formula I get a name error. do you know why? "Bob Phillips" wrote: I told you weekedns were a problem <bg I think this works for all situations =NETWORKDAYS(I2,J2)-(WEEKDAY(I2,2)<6) +(1-MOD(I2,1))*(WEEKDAY(I2,2)<6) +(MOD(J2,1)-1)*(WEEKDAY(J2,2)<6) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Diane13" wrote in message ... That formula worked on everything but these four date ranges: I J 8/19/2006 22:43 8/21/2006 8:22 7/30/2006 16:21 7/31/2006 15:33 7/29/2006 15:16 7/31/2006 7:48 8/27/2006 22:55 8/28/2006 9:17 These all resulted in 0 0:00 and I am not sure why. However, I have what I need for my project so thank you so much for all your help!!! I would have never figured out the MOD command on my own. Thanks! "Bob Phillips" wrote: There seem to be various problems with weekend dates. This seems more robust =NETWORKDAYS(I2,J2)-((WEEKDAY(J2,2)<6)*1)+IF(WEEKDAY(I2,2)<6,MOD(J2,1)-MOD(I 2,1),0) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I think I figured it out. This formula seems to be working - =IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1)) ) Thanks for all your help Bob!!! "Diane13" wrote: Bob, that worked great except I know have one minor quirk. When the 'received' date happens to fall on a weekend day, I (understandibly) get a negative result (########). Is there anything I can add to the formula to prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE statement? THANKS! "Bob Phillips" wrote: that should be upto :-) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
Since you're working with =networkdays(), I would think that General (or some
variation of a Number) would be best. But try widening the column first. Maybe you just can't see what it's trying to display (if you have either an error or a number in that cell and the column is too narrow, you'll see those #####'s). Colleen10 wrote: Thank you so much...please disregard my previous email. When I re-typed the formula, the name error went away however if the results are outside of 24 hours, I get ############ across the cells. Is there a special formatting I should be using? "Bernard Liengme" wrote: Form XL 2003 Help when I search NETWORKDAYS: If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. Please come back if more info needed best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Colleen10" wrote in message ... Hi Bob, When I try this formula I get a name error. do you know why? "Bob Phillips" wrote: I told you weekedns were a problem <bg I think this works for all situations =NETWORKDAYS(I2,J2)-(WEEKDAY(I2,2)<6) +(1-MOD(I2,1))*(WEEKDAY(I2,2)<6) +(MOD(J2,1)-1)*(WEEKDAY(J2,2)<6) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Diane13" wrote in message ... That formula worked on everything but these four date ranges: I J 8/19/2006 22:43 8/21/2006 8:22 7/30/2006 16:21 7/31/2006 15:33 7/29/2006 15:16 7/31/2006 7:48 8/27/2006 22:55 8/28/2006 9:17 These all resulted in 0 0:00 and I am not sure why. However, I have what I need for my project so thank you so much for all your help!!! I would have never figured out the MOD command on my own. Thanks! "Bob Phillips" wrote: There seem to be various problems with weekend dates. This seems more robust =NETWORKDAYS(I2,J2)-((WEEKDAY(J2,2)<6)*1)+IF(WEEKDAY(I2,2)<6,MOD(J2,1)-MOD(I 2,1),0) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I think I figured it out. This formula seems to be working - =IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1)) ) Thanks for all your help Bob!!! "Diane13" wrote: Bob, that worked great except I know have one minor quirk. When the 'received' date happens to fall on a weekend day, I (understandibly) get a negative result (########). Is there anything I can add to the formula to prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE statement? THANKS! "Bob Phillips" wrote: that should be upto :-) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! -- Dave Peterson |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
Thanks I formatted to general however I did not get the desired results of
displaying the difference...for example, I want to see the difference to display in hours and minutes. For example 5/19/09 14:15 and 5/19/09 16:56 shud display in a format of 2:41 (2 hours, 41 minutes) or dates if were 5/22/09 9:00 and 5/24/09 9:00 should display as 48:00 (48 hours, zero mins) Is this even possible? ave Peterson" wrote: Since you're working with =networkdays(), I would think that General (or some variation of a Number) would be best. But try widening the column first. Maybe you just can't see what it's trying to display (if you have either an error or a number in that cell and the column is too narrow, you'll see those #####'s). Colleen10 wrote: Thank you so much...please disregard my previous email. When I re-typed the formula, the name error went away however if the results are outside of 24 hours, I get ############ across the cells. Is there a special formatting I should be using? "Bernard Liengme" wrote: Form XL 2003 Help when I search NETWORKDAYS: If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. Please come back if more info needed best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Colleen10" wrote in message ... Hi Bob, When I try this formula I get a name error. do you know why? "Bob Phillips" wrote: I told you weekedns were a problem <bg I think this works for all situations =NETWORKDAYS(I2,J2)-(WEEKDAY(I2,2)<6) +(1-MOD(I2,1))*(WEEKDAY(I2,2)<6) +(MOD(J2,1)-1)*(WEEKDAY(J2,2)<6) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Diane13" wrote in message ... That formula worked on everything but these four date ranges: I J 8/19/2006 22:43 8/21/2006 8:22 7/30/2006 16:21 7/31/2006 15:33 7/29/2006 15:16 7/31/2006 7:48 8/27/2006 22:55 8/28/2006 9:17 These all resulted in 0 0:00 and I am not sure why. However, I have what I need for my project so thank you so much for all your help!!! I would have never figured out the MOD command on my own. Thanks! "Bob Phillips" wrote: There seem to be various problems with weekend dates. This seems more robust =NETWORKDAYS(I2,J2)-((WEEKDAY(J2,2)<6)*1)+IF(WEEKDAY(I2,2)<6,MOD(J2,1)-MOD(I 2,1),0) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I think I figured it out. This formula seems to be working - =IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1)) ) Thanks for all your help Bob!!! "Diane13" wrote: Bob, that worked great except I know have one minor quirk. When the 'received' date happens to fall on a weekend day, I (understandibly) get a negative result (########). Is there anything I can add to the formula to prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE statement? THANKS! "Bob Phillips" wrote: that should be upto :-) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! -- Dave Peterson |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtracting date and time formats excluding weekends
Send me a sample worksheet
Please get my email from my website -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Colleen10" wrote in message ... Thanks I formatted to general however I did not get the desired results of displaying the difference...for example, I want to see the difference to display in hours and minutes. For example 5/19/09 14:15 and 5/19/09 16:56 shud display in a format of 2:41 (2 hours, 41 minutes) or dates if were 5/22/09 9:00 and 5/24/09 9:00 should display as 48:00 (48 hours, zero mins) Is this even possible? ave Peterson" wrote: Since you're working with =networkdays(), I would think that General (or some variation of a Number) would be best. But try widening the column first. Maybe you just can't see what it's trying to display (if you have either an error or a number in that cell and the column is too narrow, you'll see those #####'s). Colleen10 wrote: Thank you so much...please disregard my previous email. When I re-typed the formula, the name error went away however if the results are outside of 24 hours, I get ############ across the cells. Is there a special formatting I should be using? "Bernard Liengme" wrote: Form XL 2003 Help when I search NETWORKDAYS: If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. Please come back if more info needed best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Colleen10" wrote in message ... Hi Bob, When I try this formula I get a name error. do you know why? "Bob Phillips" wrote: I told you weekedns were a problem <bg I think this works for all situations =NETWORKDAYS(I2,J2)-(WEEKDAY(I2,2)<6) +(1-MOD(I2,1))*(WEEKDAY(I2,2)<6) +(MOD(J2,1)-1)*(WEEKDAY(J2,2)<6) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Diane13" wrote in message ... That formula worked on everything but these four date ranges: I J 8/19/2006 22:43 8/21/2006 8:22 7/30/2006 16:21 7/31/2006 15:33 7/29/2006 15:16 7/31/2006 7:48 8/27/2006 22:55 8/28/2006 9:17 These all resulted in 0 0:00 and I am not sure why. However, I have what I need for my project so thank you so much for all your help!!! I would have never figured out the MOD command on my own. Thanks! "Bob Phillips" wrote: There seem to be various problems with weekend dates. This seems more robust =NETWORKDAYS(I2,J2)-((WEEKDAY(J2,2)<6)*1)+IF(WEEKDAY(I2,2)<6,MOD(J2,1)-MOD(I 2,1),0) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I think I figured it out. This formula seems to be working - =IF(NOT(NETWORKDAYS(I2,I2)),J2-I2,(NETWORKDAYS(I2,J2)-1+MOD(J2,1)-MOD(I2,1)) ) Thanks for all your help Bob!!! "Diane13" wrote: Bob, that worked great except I know have one minor quirk. When the 'received' date happens to fall on a weekend day, I (understandibly) get a negative result (########). Is there anything I can add to the formula to prevent these few instances? Possibly an Excel version of the IF, THEN, ELSE statement? THANKS! "Bob Phillips" wrote: that should be upto :-) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Diane13" wrote in message ... I am trying to figure out how to subtract a cell containing a mm/dd/yyyy hh:mm format from another cell with the same format and exclude the weekends. Example: A1(received): 7/28/06 15:28 A2(submitted): 7/31/06 16:07 A3 The difference in these two dates and times, excluding the weekend, to determine the total turn around time. The answer should reflect 1 day and 39 minutes in a format such as d hh:mm or any other format that would make this easier. I have the entire list of Excel functions and have tried all of them that I think are logical but I can't seem to get the correct days and time brought together into a single cell answer. Any help would be GREATLY appreciated! I am using Excel 2003. Thanks! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate A Date Excluding Weekends And Holidays | New Users to Excel | |||
Deleting time part of a Date, subtracting dates | Excel Discussion (Misc queries) | |||
Calculate A Date Excluding Weekends And Holidays | New Users to Excel | |||
Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
Subtracting time in date format | Excel Worksheet Functions |