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

✔ 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

message edited by mmcconaghy

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

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

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

Sorry for the complication The entries are as follows

A B C1 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

I understand the top part: A B C 1) 04.06.2014 10.07.2014 14:30:47 28.07.2014 23:59:59Column A is your Start Date,

Columns B & C your End DatesColumn A - B gives: 26 DAYS 9:29:13

Column 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:05Where 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 thisHow-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:59Column A - B gives: 26 DAYS 9:29:13

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

message edited by mmcconaghy

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

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:1I 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# discussionInstead 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

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

message edited by mmcconaghy

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

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.

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

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 4Formula for column B: =VALUE(LEFT(A1,FIND(" ",A1,1)))

Drag downThen you can use the SUBTOTAL() function

=SUBTOTAL(9,B1:B3) Sum

=SUBTOTAL(101,B1:B3) AverageYou can also Hide column B so no one see's the

"magic behind the curtain"MIKE

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

A #NAMEerror 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

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

The only thing that look suspicious is in the FIND part of the formula It should be FIND(" ",A1,1)

with aSpacebetween 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.

MIKE

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?

can I upload the fileI 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

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.

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:31That 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

It works. :) Thanks a lot for your help. I really appreciate it!

This is a great forum for learning!

Thanks once again

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

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.

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:1For

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

Ask Your Question

Weekly Poll

Do you think state sales tax should be charged on Internet purchases?

Discuss in The Lounge

Poll History