Solved Using NETWORKDAYS() with MOD() for difference between 2 days

mmcconaghy December 3, 2014 at 04:17:21

In your formula you specify the columns J & L as containing dates but you only posted a single column of data. So, just as a guess, you could try an ARRAY formula like: =SUM(NETWORKDAYS(J2:J10000+0, L2:L10000+0)) You MUST use CTRL-SHIFT-ENTER for an Array formula. If you have done it correctly, the formula should appear with curly braces around it, like: {=SUM(NETWORKDAYS(J2:J10000+0, L2:L10000+0))} I'm using 2007, so I do not have the NETWORKDAYS.INTL() function, so I've used just plain old NETWORKDAYS() And for future reference, Excel questions are best asked in the Office Software forum. MIKE http://www.skeptic.com/

Dear Mike, Thanks a lot for the help. It works well.And yes I am using 2010. I will keep in mind the suggestions from your side. Thanks a lot once again message edited by Excelforram

If I need to calculate the sum of the number of cells containing different values of NETWORKDAYS() ie., A2: =NETTOARBEITSTAGE.INTL(J2;L2;1;0)-1&"TAGE"&STUNDE(REST(J2-L2;1))&":"&MINUTE(REST(J2-L2;1))&":"&SEKUNDE(REST(J2-L2;1))&"" B2:=NETTOARBEITSTAGE.INTL(L2;M2;1;0)-1&"TAGE"&STUNDE(REST(L2-M2;1))&":"&MINUTE(REST(L2-M2;1))&":"&SEKUNDE(REST(L2-M2;1))&"" etc., Should I use an IF statement ínorder to get the SUM of the whole days alone in cell D2? Kindly help

mmcconaghy December 4, 2014 at 10:38:49

I'm sorry, I don't understand what it is your looking for. Perhaps if you explain what it is your trying to do or give a before and after example. MIKE http://www.skeptic.com/

Sorry for the complication The entries are as follows A B C 1 04.06.2014 10.07.2014 14:30:47 28.07.2014 23:59:59 D1 =NETWORKDAYS.INTL(A1;B1;1;0)-1&"DAYS"&HOURS(MOD(A1-B1;1))&":"&MINUTES(MOD(A1-B1;1))&":"&SECONDS(MOD(A1-B1;1))&"" which yields 26 DAYS 09:29:13 E1 =NETWORKDAYS.INTL(A1;C1;1;0)-1&"DAYS"&HOURS(MOD(A1-C1;1))&":"&MINUTES(MOD(A1-C1;1))&":"&SECONDS(MOD(A1-C1;1))&"" yields 38 DAYS 00:00:01 how can I proceed to get the result for SUM(D1:E1) excluding the time factors. Also, Consider the following criteria, A 1: 13.06.2014 23:59:59 2: 30.04.2014 16:36:29 3: 08.05.2014 15:11:49 4: 12.05.2014 22:30:05 etc., etc., how can I use the Subtotal() formula for the arrays A1:A2000 for eg., Kindly help Thanks a lot message edited by Excelforram

mmcconaghy December 4, 2014 at 17:28:12

I understand the top part: A B C 1) 04.06.2014 10.07.2014 14:30:47 28.07.2014 23:59:59 Column A is your Start Date, Columns B & C your End Dates Column A - B gives: 26 DAYS 9:29:13 Column A - C gives: 38 DAYS 0:0:1 What has be confused is your last part: A 1) 13.06.2014 23:59:59 2) 30.04.2014 16:36:29 3) 08.05.2014 15:11:49 4) 12.05.2014 22:30:05 Where do these Dates/Time belong? Which is a Start Date, which is an End Date? Please understand, I can not see your spreadsheet from where I am sitting and I have no idea what it is your trying to do, so you need to be very specific with your info. If you can, post a small sample of your spreadsheet, but read this How-To, which explains how to use the < PRE > tags to alight all your data so it looks correct. http://www.computing.net/howtos/sho... Thanks. EDIT ADDED: Forgot, this formula will give you the Total Number of Days for the Top dates: A B C 1) 04.06.2014 10.07.2014 14:30:47 28.07.2014 23:59:59 Column A - B gives: 26 DAYS 9:29:13 Column A - C gives: 38 DAYS 0:0:1 In D1 enter the formula: =SUM(NETWORKDAYS(A1:A1+0, B1:C1+0)-1) Don't forget that this is an ARRAY formula and you must use CTRL-SHIFT-ENTER. As you can see it is a bit different from the original, hence, the need to see what your spreadsheet actually looks like. MIKE http://www.skeptic.com/ message edited by mmcconaghy

mmcconaghy December 4, 2014 at 18:06:08

Was playing around a bit, here is a NON-Array formula using =SUMPRODUCT() that seems to work also: =SUMPRODUCT(NETWORKDAYS($A$1:$A$1+0,$B$1:$C$1+ 0)-1) MIKE http://www.skeptic.com/

For the first formula I would like to have the sum of column D and E which is explained as below D E 1) 26 DAYS 9:29:13 38 DAYS 0:0:1 I would like to have the result for SUM(D1:E1) and not the =SUM(NETWORKDAYS(A1:A1+0, B1:C1+0)-1) Thanks a lot. For the second part, You had given the correct solution in our 1# discussion Instead of SUM() I would like to insert SUBTOTAL() for the array. i.e., A 1) 3 DAYS 3:59:59 2) 5 DAYS 2:29:29 3) 4 DAYS 0:0:1 etc., etc., 1400) =SUBTOTAL(SUM,...) Kindly help message edited by Excelforram

mmcconaghy December 5, 2014 at 08:19:24

I would like to have the result for SUM(D1:E1) and not the =SUM(NETWORKDAYS) Try this: =SUMPRODUCT(--LEFT(D1:E1,FIND(" ",D1:E1,1))) Although I don't understand why you want this, when both formulas use the same info, and do the same thing. The problem, is you are one step further away from your original data. You are introducing an intermediate step, a formula that does not work on original data, but on the results of another formula. This introduces one more thing that can go wrong. And I still don't know how this: A 1) 3 DAYS 3:59:59 2) 5 DAYS 2:29:29 3) 4 DAYS 0:0:1 etc., etc., 1400) =SUBTOTAL(SUM,...) Relates to anything else, but I'll see what I can come up with. MIKE http://www.skeptic.com/ message edited by mmcconaghy

mmcconaghy December 5, 2014 at 10:14:51

✔ Best Answer

Just noticed I posted the wrong formula in my last post. Use: =SUM(--LEFT(D1:E1,FIND(" ",D1:E1,1))) Which is an ARRAY formula instead of the =SUMPRODUCT() Also, I'm not sure that SUBTOTAL() will be able to work. The cells you want to sum are NOT numerical in nature,they are TEXT cells that must first be cut apart to get at the numbers, thus the LEFT() & FIND() functions in the above SUM. I'll work on it, but the above SUM will also work with the other list: A 1) 3 DAYS 3:59:59 2) 5 DAYS 2:29:29 3) 4 DAYS 0:0:1 =SUM(--LEFT(A1:A3,FIND(" ",A1:A3,1))) Entered as an ARRAY formula or you can also use the SUMPRODCT() =SUMPRODUCT(--LEFT(A1:A3,FIND(" ",A1:A3,1))) Which is not an array. MIKE http://www.skeptic.com/ message edited by mmcconaghy

The 1st formula works perfectly thanks for that. But for the second one i.e A 1) 3 DAYS 3:59:59 2) 5 DAYS 2:29:29 3) 4 DAYS 0:0:1 . . . . 2000) 2001) =SUBTOTAL(function number, ref1,ref2) I need to find the Subtotal instead of the Sum formula which is required. I am not able to integrate this formula for the Subtotal. Please help.

mmcconaghy December 8, 2014 at 09:27:16

SUBTOTAL formulas as best as I know can not be used with an ARRAY That is why I offered the SUMPRODUCT function. For a SUBTOTAL solution you would probably need to use a "Helper" column to hold the data and then use the SUBTOTAL function on the "Helper" column. MIKE http://www.skeptic.com/

mmcconaghy December 8, 2014 at 10:06:06

Something like this should work: A B 1) 3 DAYS 3:59:59 3 2) 5 DAYS 2:29:29 5 3) 4 DAYS 0:0:1 4 Formula for column B: =VALUE(LEFT(A1,FIND(" ",A1,1))) Drag down Then you can use the SUBTOTAL() function =SUBTOTAL(9,B1:B3) Sum =SUBTOTAL(101,B1:B3) Average You can also Hide column B so no one see's the "magic behind the curtain" MIKE http://www.skeptic.com/

For the same case, when I use the SUBTOTAL(9,B1:B2) on A2001 for example I get a #Name error. Should I use the sumproduct instead? message edited by Excelforram

mmcconaghy December 8, 2014 at 10:53:46

A #NAME error can be a lot of things. As a start, Copy the formula you are using and Paste it here. What is in cell A2001? MIKE http://www.skeptic.com/ message edited by mmcconaghy

I am using a german version of exceI. Nevertheless I am using the same formula that you had suggested. eg., A B 1) 0 DAYS 13:2:27 =VALUE(LEFT(A1,FIND("",A1,1))) 2) 1 DAYS 4:12:31 =VALUE(LEFT(A1,FIND("",A1,1))) 3) 0 DAYS 14:12:31 =VALUE(LEFT(A1,FIND("",A1,1))) . . . . 2001) =SUBTOTAL(9,B1:B2000) So in place of A2001 I get the #Name error message edited by Excelforram

mmcconaghy December 8, 2014 at 11:25:03

The only thing that look suspicious is in the FIND part of the formula It should be FIND(" ",A1,1) with a Space between the two quote marks. Here are all the reason you can get a #NAME error and how to do a Trace Error https://office.microsoft.com/en-gb/... Without having the actual sheet, it's difficult to pin point where the error is occurring. MIKE http://www.skeptic.com/

Since I am using a german system it isn't possible for me to change it into this format. How can I upload the file for you to view it or can I share a screen shot?

mmcconaghy December 8, 2014 at 14:33:34

can I upload the file I would have no idea what any of it means, I'm afraid I do not understand German. it isn't possible for me to change it into this format. I don't understand what you mean, change what, into what format? This formula: =VALUE(LEFT(A1,FIND(" ",A1,1))) <<Note the space between quotes. as best as I can translate it, comes out as: =WERT(LINKS(A1,FINDEN(" ",A1,1))) With a bit of help from this web site: http://www.piuha.fi/excel-function-... Is that what you mean? MIKE http://www.skeptic.com/ message edited by mmcconaghy

If I use the formula =WERT(LINKS(A1,FINDEN(" ",A1,1))) it returns a #WERT or #VALUE error But when I use =WERT(LINKS(A1,FINDEN("",A1,1))) it returns the value 0,0 Could you help me out. I am really sorry it takes a long time. If you want I can also upload a screen shot or the file as well for your reference. Kindly let me know how I can do it.

mmcconaghy December 9, 2014 at 03:21:29

Check the actual contents of you Date/Time list: Make sure there no space BEFORE the string. A 1) <space>0 DAYS 13:2:27 2) <space>1 DAYS 4:12:31 3) <space>0 DAYS 14:12:31 That will give you a #VALUE error. This formula: =WERT(LINKS(A1,FINDEN(" ",A1,1))) Uses the Space character as a marker to find the first number, if the Space appears before the number, the formula throws up the #VALUE error. You can use the TRIM() function ( GLÄTTEN in German ) which removes all spaces from text except for single spaces between words. =TRIM(A1) or in German =GLATTEN(A1) See how that works. MIKE http://www.skeptic.com/

It works. :) Thanks a lot for your help. I really appreciate it! This is a great forum for learning! Thanks once again

mmcconaghy December 9, 2014 at 07:41:25

Now that everything is working, can you tell me why you wanted to use the function SUBTOTAL()? All your doing with it, is to SUM() the range A1:A2000. MIKE http://www.skeptic.com/

It was an existing formula that my boss was working on. I have suggested this change to him. Since basically both give the same result.

mmcconaghy December 10, 2014 at 08:29:09

Thanks for the explanation. If you can, you might also suggest that you keep the Day/Time breakdown: A 1) 3 DAYS 3:59:59 2) 5 DAYS 2:29:29 3) 4 DAYS 0:0:1 For display, if you must, but use the ARRAY formula for the actual calculations: {=SUM(NETWORKDAYS(J2:J10000+0, L2:L10000+0))} so that you are using original data, which eliminates one helper column, keeps the sheet compact and easy to use with no hidden columns, and reduces the chance of errors, where one formula relies on the output of another, as you saw with the #VALUE error. But, as always in business: "What ever the Boss wants." :-) Good luck. MIKE http://www.skeptic.com/