Sum Weekdays and sum Weekend

[usl]

I have a sheet with alle days an date in one month

in column "A" the date (format: TTTT TT.MM.JJ)
in column "B" I have the worked hours

Below in the sheet I would to have to sums
each one for weekdays and weekends,

because my guys get more money if they work on a saturday or sunday.

I have been playing with the formulars
=sumif(weekdays(....., but cant geht the right thing

Have any of you got a good solution to this ?

Thanks
Uli

 

[Aladin Akyurek]

To add up based on Monday - Friday:

=SUMPRODUCT((WEEKDAY($A$1:$A$50,1)<6)^1,$B$1:$B$50)

For Weekends:

=SUM($B$1:$B$50) less the result of the above formula.

Regards
Jon

Is the raising to the 1 an unfamiliar (to me, at least) form of coercing?

See:

http://www.mrexcel.com/board2/viewtopic.php?t=133602

Simple math:

Number^1 ==> Number; TRUE^1 ==> 1^1 ==> 1

FALSE^1 ==> 0^1 ==> 0

It has of course a higher cost than double negation.

 

[Lewiy]

Perhaps someone can explain this then. For some reason, using double negation is the only method of coercion which does not convert the destination cell into the number format of the cell referred to (with dates at least, haven’t explored this fully):

Excel Workbook
	A	B
1	01/01/2007	2
2	02/01/2007	03/01/1900
3	03/01/2007	4
4	04/01/2007	05/01/1900
5	05/01/2007	06/01/1900
Sheet1

 

[Jon von der Heyden]

It has of course a higher cost than double negation.

Er, sorry Aladin, what do you mean by this?

 

[Lewiy]

It has of course a higher cost than double negation.

Er, sorry Aladin, what do you mean by this?

Double negation calculates faster, hence why it is now widely used in SUMPRODUCT in favour of other coercion methods.

 

[Richard Schollar]

It has of course a higher cost than double negation.

Er, sorry Aladin, what do you mean by this?

It is less efficient ie it uses more processing cycles to calculate the result.

 

[mortgageman]

Lewiy said:
Perhaps someone can explain this then. For some reason, using double negation is the only method of coercion which does not convert the destination cell into the number format of the cell referred to (with dates at least, haven’t explored this fully):

Not sure I follow you. --, +0 (and now^1) are to be used on TRUE and FALSE to coerce them into 1 and 0. You don't use them on dates.

 

[Richard Schollar]

That's an interesting observation Lewiy - no idea why though...

 

[Aladin Akyurek]

Lewiy said:
Perhaps someone can explain this then. For some reason, using double negation is the only method of coercion which does not convert the destination cell into the number format of the cell referred to (with dates at least, haven’t explored this fully):

Not sure I follow you. --, +0 (and now^1) are to be used on TRUE and FALSE to coerce them into 1 and 0. You don't use them on dates.

Try...

=(WEEKDAY(A2))^1

=(WEEKDAY(A2))+0

=(WEEKDAY(A2))*1

But, coercing WEEKDAY(A2) on its own does not make sense for the result is always an integer from the set of 1,2,...,7.

 

[mortgageman]

Lewiy said:
Perhaps someone can explain this then. For some reason, using double negation is the only method of coercion which does not convert the destination cell into the number format of the cell referred to (with dates at least, haven’t explored this fully):

Not sure I follow you. --, +0 (and now^1) are to be used on TRUE and FALSE to coerce them into 1 and 0. You don't use them on dates.

Try...

=(WEEKDAY(A2))^1

=(WEEKDAY(A2))+0

=(WEEKDAY(A2))*1

But, coercing WEEKDAY(A2) on its own does not make sense for the result is always an integer from the set of 1,2,...,7.

So Aladdin - you are agreeing with me?

 

[Lewiy]

Try...

=(WEEKDAY(A2))^1

=(WEEKDAY(A2))+0

=(WEEKDAY(A2))*1

But, coercing WEEKDAY(A2) on its own does not make sense for the result is always an integer from the set of 1,2,...,7.

Agreed, this is not coercion as such, and this method does indeed keep the result as a number rather than a date. It just seemed a rather curious phenomenon.

I would expect an automatic number format conversion with a simple "=A2" or something to that effect, but I'm truly baffled why this would happen with WEEKDAY(). Here's some further observations. Note that whilst most of them change format, not all do:

Book1
	A	B	C	D
1
2	01/01/2007	02/01/1900	date
3	39083	2	general
4	39,083	2	currency
5	0:00	0:00	time
6	3908300%	2	percentage
7	39083	2	fraction
8	4.E+04	2.E+00	scientific
9	39083.00	2.00	number(2dp)
10	39083	2	text
11	39083.00M	2.00	custom(#0.00"M")
Sheet1