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

December 3, 2014 at 03:01:23
Specs: Windows 7
The entries are as follows

13.06.2014 23:59:59
30.04.2014 16:36:29
08.05.2014 15:11:49
12.05.2014 22:30:05

etc., 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

Report •

✔ Best Answer
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 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



#1
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/


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 again

message 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 do
or give a before and after example.

MIKE

http://www.skeptic.com/


Report •

#5
December 4, 2014 at 13:23:43
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


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 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


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)

MIKE

http://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 help

message 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:

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


Report •

#10
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


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 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/


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"

MIKE

http://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?


MIKE

http://www.skeptic.com/

message 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 error


message 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 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/


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


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,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.


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)  <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/


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.

MIKE

http://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.

MIKE

http://www.skeptic.com/


Report •

Ask Question