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

December 3, 2014 at 03:01:23
Specs: Windows 7
 The entries are as follows13.06.2014 23:59:5930.04.2014 16:36:2908.05.2014 15:11:4912.05.2014 22:30:05etc., etc.,Now after using the formula =NETTOARBEITSTAGE.INTL(J2;L2;1;0)-1&"TAGE"&STUNDE(REST(J2-L2;1))&":"&MINUTE(REST(J2-L2;1)) for a number of entries, say around 10000 and getting the result could I use SUMIF to get the sum of all the entries considering only the whole days and not the hours:minutes?Kindly help.Thanks in advance

See More: Using NETWORKDAYS() with MOD() for difference between 2 days

December 5, 2014 at 10:14:51
 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 formulaor you can also use the SUMPRODCT()=SUMPRODUCT(--LEFT(A1:A3,FIND(" ",A1:A3,1)))Which is not an array.MIKEmessage edited by mmcconaghy

#1
December 3, 2014 at 04:17:21
 In your formula you specify the columns J & L as containing datesbut 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 bracesaround 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.MIKEhttp://www.skeptic.com/

Report •

#2
December 4, 2014 at 01:48:01
 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 againmessage edited by Excelforram

Report •

#3
December 4, 2014 at 02:12:59
 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

Report •

Related Solutions

#4
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 door give a before and after example.MIKEhttp://www.skeptic.com/

Report •

#5
December 4, 2014 at 13:23:43
 Sorry for the complicationThe entries are as follows A B C1 04.06.2014 10.07.2014 14:30:47 28.07.2014 23:59:59D1 =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:13E1 =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:01how can I proceed to get the result for SUM(D1:E1) excluding the time factors.Also,Consider the following criteria, A1: 13.06.2014 23:59:592: 30.04.2014 16:36:293: 08.05.2014 15:11:494: 12.05.2014 22:30:05etc., etc.,how can I use the Subtotal() formula for the arrays A1:A2000 for eg., Kindly helpThanks a lotmessage edited by Excelforram

Report •

#6
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 DatesColumn A - B gives: 26 DAYS 9:29:13Column A - C gives: 38 DAYS 0:0:1What 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:13Column A - C gives: 38 DAYS 0:0:1In D1 enter the formula:=SUM(NETWORKDAYS(A1:A1+0, B1:C1+0)-1)Don't forget that this is an ARRAY formula and you mustuse CTRL-SHIFT-ENTER.As you can see it is a bit different from the original,hence, the need to see what your spreadsheet actuallylooks like.MIKEmessage edited by mmcconaghy

Report •

#7
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)MIKEhttp://www.skeptic.com/

Report •

#8
December 5, 2014 at 00:50:38
 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 helpmessage edited by Excelforram

Report •

#9
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: A1) 3 DAYS 3:59:592) 5 DAYS 2:29:293) 4 DAYS 0:0:1etc., etc.,1400) =SUBTOTAL(SUM,...)Relates to anything else, but I'll see what I can come up with.MIKEmessage edited by mmcconaghy

Report •

#10
December 5, 2014 at 10:14:51
 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 formulaor you can also use the SUMPRODCT()=SUMPRODUCT(--LEFT(A1:A3,FIND(" ",A1:A3,1)))Which is not an array.MIKEmessage edited by mmcconaghy

Report •

#11
December 8, 2014 at 09:20:18
 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.

Report •

#12
December 8, 2014 at 09:27:16
 SUBTOTAL formulas as best as I know can not be used with an ARRAYThat 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.MIKEhttp://www.skeptic.com/

Report •

#13
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" MIKEhttp://www.skeptic.com/

Report •

#14
December 8, 2014 at 10:23:33
 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

Report •

#15
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?MIKEmessage edited by mmcconaghy

Report •

#16
December 8, 2014 at 11:11:05
 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 errormessage edited by Excelforram

Report •

#17
December 8, 2014 at 11:25:03
 The only thing that look suspicious is in the FIND part of the formulaIt 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 Errorhttps://office.microsoft.com/en-gb/...Without having the actual sheet, it's difficult to pin point where the error is occurring.MIKEhttp://www.skeptic.com/

Report •

#18
December 8, 2014 at 14:15:48
 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?

Report •

#19
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))) <

Report •

#20
December 9, 2014 at 00:20:45
 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,0Could 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.

Report •

#21
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) 0 DAYS 13:2:27 2) 1 DAYS 4:12:31 3) 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 upthe #VALUE error.You can use the TRIM() function ( GLÄTTEN in German ) whichremoves all spaces from text except for single spaces between words. =TRIM(A1) or in German =GLATTEN(A1)See how that works.MIKEhttp://www.skeptic.com/

Report •

#22
December 9, 2014 at 05:35:01
 It works. :)Thanks a lot for your help. I really appreciate it! This is a great forum for learning! Thanks once again

Report •

#23
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. MIKEhttp://www.skeptic.com/

Report •

#24
December 10, 2014 at 05:06:11
 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.

Report •

#25
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.MIKEhttp://www.skeptic.com/

Report •