How to Count Only Weekdays but have the total Calmative For each week.

[Jelsey]

Hello Again,
I am working on a Horizontal calendar to track days off work. I want to use the weekend columns to total the number of Days Off each week. However I want it to be cumulative. So in column H, would have the off days totaled from column C - G. and then in Column O would have the total days off from Column C - D and J - N, and column V would have the total days Off from Column C - D and J - N and Q - U (and so on). What would be a good approach for that?

 

[awoohaw]

Try this:

mr excel questions 27.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Sun 01-Jan	Mon 02-Jan	Tue 03-Jan	Wed 04-Jan	Thu 05-Jan	Fri 06-Jan	Sat 07-Jan	Sun 08-Jan	Mon 09-Jan	Tue 10-Jan	Wed 11-Jan	Thu 12-Jan	Fri 13-Jan	Sat 14-Jan	Sun 15-Jan	Mon 16-Jan	Tue 17-Jan	Wed 18-Jan	Thu 19-Jan	Fri 20-Jan	Sat 21-Jan
2		Off					1			Off				2				Off			3
Jelsey

Cell Formulas
Range	Formula
G2,U2,N2	G2	=COUNTIF($B$2:F2,"Off")

you did not give your day off indicator, I am using typed in word of "Off" in the cell. But this formula should work in many other ways of identifying the day off.

 

[Jelsey]

Try this:

mr excel questions 27.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Sun 01-Jan	Mon 02-Jan	Tue 03-Jan	Wed 04-Jan	Thu 05-Jan	Fri 06-Jan	Sat 07-Jan	Sun 08-Jan	Mon 09-Jan	Tue 10-Jan	Wed 11-Jan	Thu 12-Jan	Fri 13-Jan	Sat 14-Jan	Sun 15-Jan	Mon 16-Jan	Tue 17-Jan	Wed 18-Jan	Thu 19-Jan	Fri 20-Jan	Sat 21-Jan
2		Off					1			Off				2				Off			3
Jelsey

Cell Formulas
Range	Formula
G2,U2,N2	G2	=COUNTIF($B$2:F2,"Off")

you did not give your day off indicator, I am using typed in word of "Off" in the cell. But this formula should work in many other ways of identifying the day off.

You are correct. Apologies. I have the sheet set up like this (see below)


Each cell under a date is populated with .5 (half day off) 1 (full day off). I have table set to change color based on the value entered. 1 = light green. (First picture)

I received assistance in being able to total the calendar year with only Weekdays and excluding the weekend., Now, I'm attempting to use the column where the weekend is located to calculate the number of days off each week so it's a cumulative weekly total.

The issue I'm having is that the Grand total at the end of the calendar only calculates current/expected days off. For example, if someone puts in vacation time. It will apprea in the grand total, but more than half of thoes dates have not happened yet, so it is not completely accurate., However, if I do weekly totals, I can have the calendar accurate of all days off to todays current date.

Does that make sense?

 

[awoohaw]

It doesn't make complete sense.
But let me try to ask some questions that may help.
Do you want half vs full days off separately (and displayed in the two columns of each week)?
What I gave you give cumulative weekly totals for OFF.
In one column of the "weekend" you can get full days off with

=COUNTIF($B$2:F2,1)

and half days off in the other column with

=COUNTIF($B$2:F2,.5)

and instead of Off use 1 for Full, and .5 for half.

 

[Jelsey]

Sorry if there is confusion but I am more than happy to answer your questions for better clarity.

Yes, I would like to have half days and full days off expressed in the weekend columns.
So Saturday column shows the Full Day Off totals and Sunday column shows the Half Day totals..

The way I would like the sheet to work is at the end of week 1, in the weekend columns, I have the total days off for week 1.
Then at the end of week 2, in the weekend columns, I would like to have the total days off in week 2 + the total days off in week 1.

The idea is to not have to scroll back to the beginning of the calendar to check week by week, how many days someone took of. I would like to be able to check the current week (like the end of this week) and know from week #1 until now, how many days off were taken.

Ex. Today, right now, we are in the 17th week of the year 2023. We have completed 16 full weeks in the year so far. I would like to look at the end week #16 and know how many days off were taken from week #1 to week #16. However, I do not want to have to scroll back to week #1 and sum all the weekly totals up to week #16.

Similarly, If I looked at the end of week #8 I would like that number to reflect the number of days off from week #1 to week #8. Basically the end of each week should add the totals from all the prior weeks.


Does that help clear things up?

 

[awoohaw]

yes, cleared up. ANd that is what I thought, and what those formulas should do. I'll post a mini in a minute.

 

[awoohaw]

mr excel questions 27.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Sun 01-Jan	Mon 02-Jan	Tue 03-Jan	Wed 04-Jan	Thu 05-Jan	Fri 06-Jan	Sat 07-Jan	Sun 08-Jan	Mon 09-Jan	Tue 10-Jan	Wed 11-Jan	Thu 12-Jan	Fri 13-Jan	Sat 14-Jan	Sun 15-Jan	Mon 16-Jan	Tue 17-Jan	Wed 18-Jan	Thu 19-Jan	Fri 20-Jan	Sat 21-Jan	Sun 22-Jan
2		0.5	1	1		0.5	2	2		1	0.5	0.5		3	4			1			4	4
3
Jelsey

Cell Formulas
Range	Formula
G2,U2,N2	G2	=SUM(IFERROR(--(WEEKDAY($B$1:F$1)={2;3;4;5;6})*(--($B2:E$2=1)),0))
H2,V2,O2	H2	=SUM(IFERROR(--(WEEKDAY($B$1:G$1)={2;3;4;5;6})*(--($B2:F$2=0.5)),0))

 

[Dave Patton]

You could try a formula like =SUM((WEEKDAY($B$1:F$1,2)<6)*($B2:F$2=1))

 

[awoohaw]

You could try a formula like =SUM((WEEKDAY($B$1:F$1,2)<6)*($B2:F$2=1))

Dave, my system setting have Sunday as 1. But your formula is much shorter! and For some reason my formula got an error with the weekday check that I had to edit out.
Strange that yours doesn't.

 

[Dave Patton]

Weekday per Excel's help see Return Type

WEEKDAY function​

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 More...
This article describes the formula syntax and usage of the WEEKDAY function in Microsoft Excel.

Description​

Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.

Syntax​

WEEKDAY(serial_number,[return_type])

The WEEKDAY function syntax has the following arguments:

• Serial_number Required. A sequential number that represents the date of the day you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.
• Return_type Optional. A number that determines the type of return value.

Return_type	Number returned
1 or omitted	Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
2	Numbers 1 (Monday) through 7 (Sunday).