Sum Weekdays and sum Weekend

usl

Board Regular
Joined
Jan 28, 2005
Messages
71
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
 
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.
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
AB
101/01/20072
202/01/200703/01/1900
303/01/20074
404/01/200705/01/1900
505/01/200706/01/1900
Sheet1
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
ABCD
1
201/01/200702/01/1900date
3390832general
439,0832currency
50:000:00time
63908300%2percentage
7390832fraction
84.E+042.E+00scientific
939083.002.00number(2dp)
10390832text
1139083.00M2.00custom(#0.00"M")
Sheet1
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top