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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
Like this?:
Excel Workbook
ABCDE
101/01/20079Weekday HoursWeekend Hours
202/01/2007910734
303/01/20079
404/01/200710
505/01/20075
606/01/20077
707/01/20077
808/01/20075
909/01/20074
1010/01/20079
1111/01/20076
1212/01/20076
1313/01/20078
1414/01/20076
1515/01/20078
1616/01/20077
1717/01/20079
1818/01/20076
1919/01/20075
2020/01/20076
Sheet3
 
Upvote 0
This looks great although,
if I use your values and copy/paste the formula,
I get an error and excel is marking this "A1:A20,2" (blue in your fomula)
is the bracket from Weekdays, what does the "2" actually mean ?





Like this?:
Excel Workbook
ABCDE
101/01/20079Weekday HoursWeekend Hours
202/01/2007910734
303/01/20079
404/01/200710
505/01/20075
606/01/20077
707/01/20077
808/01/20075
909/01/20074
1010/01/20079
1111/01/20076
1212/01/20076
1313/01/20078
1414/01/20076
1515/01/20078
1616/01/20077
1717/01/20079
1818/01/20076
1919/01/20075
2020/01/20076
Sheet3
 
Upvote 0
If you have a non-English version of excel you may need to use ; in place of , in your formulas.

WEEKDAY function gives 1 as Sunday through to 7 for Saturday but if you want to identify weekends using a second argument of 2 is better because then Monday is 1 through to 7 for Sunday so you can identify weekends when WEEKDAY(A1;2) > 5 (6 for Saturdays and 7 for Sundays)
 
Upvote 0
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?
 
Upvote 0
Thanks,

actually I do have an english version , but it helped when I swaped the , to ;
now it works :)


If you have a non-English version of excel you may need to use ; in place of , in your formulas.

WEEKDAY function gives 1 as Sunday through to 7 for Saturday but if you want to identify weekends using a second argument of 2 is better because then Monday is 1 through to 7 for Sunday so you can identify weekends when WEEKDAY(A1;2) > 5 (6 for Saturdays and 7 for Sundays)
 
Upvote 0
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?

Not something I’ve come across either. Having had a play around, the only thing I could come up with was that it converts the destination cell into the same number format as the range it is referring to but that might just be co-incidence. I would like to hear the explanation too!!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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