Trying to learn Workday but Saturday is being stubborn

[Rex987456]

I am trying to track some averages during the week, and then on each weekend day. After some searching on the internet I found this forumla that worked for the M-F and Sunday but for some odd reason, Saturday is messed up. Can't figure out why.

For M-F I used

=SUM((WEEKDAY($E$2:$EE$2,2)<6)*($E4:$EE4)/SUM(1*(WEEKDAY($E$2:$EE$2,2)<6)))

For Saturday I used

=SUM((WEEKDAY($E$2:$EE$2,2)=6)*($E4:$EE4)/SUM(1*(WEEKDAY($E$2:$EE$2,2)=6)))

For Sunday I used

=SUM((WEEKDAY($E$2:$EE$2,2)=7)*($E4:$EE4)/SUM(1*(WEEKDAY($E$2:$EE$2,2)=7)))

Does anyone know what I am doing wrong? I have been playing with it for hours now, and cannot get SAturday to calculate legit averages. Or is there an easier way?

Thank you!

 

[Micron]

How that function works depends on your regional settings in Windows. Where I am, day one is Sunday, not day 7, and 6 in your second example would be Friday, not Saturday. What day is the leftmost day of any calendar that you use? If it is Sunday, then none of your results are accurate I'd say.

 

[6StringJazzer]

The default may depend on regional settings but the formula explicitly uses 2 for the optional second argument, which specifies Monday=1 -> Sunday=7. This overrides any localization default.

 

[Micron]

Sorry, overlooked that. Thanks

 

[6StringJazzer]

Can you post your actual data for testing? E:EE is a lot of data but maybe you can post part of it. XL2BB is the best way but you could also just copy from Excel and paste into a post.

How are your dates in row 2 entered? Are they all truly Excel date values? For example, when you select G2 what is in the Formula bar?

 

[Rex987456]

Can you post your actual data for testing? E:EE is a lot of data but maybe you can post part of it. XL2BB is the best way but you could also just copy from Excel and paste into a post.

How are your dates in row 2 entered? Are they all truly Excel date values? For example, when you select G2 what is in the Formula bar?

Tracking.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1					Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday
2	Interval Start Time		Saturday	Sunday	8/29/2024	8/30/2024	8/31/2024	9/1/2024	9/2/2024	9/3/2024	9/4/2024	9/5/2024	9/6/2024	9/7/2024	9/8/2024	9/9/2024	9/10/2024
3		Average	Average	Average
4	1200-0100	1.8	0.0	2.0	0	1	3	1	4	3	2	0	1	1	3	2	3
5	0100-0200	1.7	0.0	1.5	3	1	1	2	2	1	2	2	1	1	1	2	1
6	0200-0300	1.9	0.0	2.5	2	4	1	1	1	1	1	3	2	2	4	2	1
7	0300-0400	0.6	0.0	0.0	1	0	2	0	2	2	0	0	0	1	0	0	0
8	0400-0500	1.3	0.0	0.5	4	0	2	0	2	4	2	0	0	1	1	0	0
9	0500-0600	1.4	0.0	0.5	2	0	1	0	2	1	1	1	2	1	1	1	3
10	0600-0700	1.9	0.1	2.0	0	2	1	1	2	1	3	3	2	5	3	0	4
11	0700-0800	10.3	0.1	3.0	11	14	4	4	2	8	12	15	13	5	2	7	11
12	0800-0900	6.4	0.3	6.5	8	11	22	5	3	5	8	5	7	13	8	7	4
13	0900-1000	9.6	0.7	30.0	8	7	48	27	13	10	6	15	8	35	33	11	8
14	1000-1100	10.9	0.4	13.5	14	9	27	9	13	6	9	8	16	17	18	14	9
15	1100-1200	8.3	0.4	12.0	5	8	20	5	7	7	11	18	6	24	19	3	10
16	1200-1300	9.9	0.3	10.0	11	10	22	9	13	3	11	11	8	18	11	11	11
17	1300-1400	8.7	0.2	9.5	9	10	10	9	11	5	9	8	6	13	10	11	9
18	1400-1500	10.8	0.3	10.0	18	9	18	7	15	8	8	7	7	13	13	12	13
19	1500-1600	10.7	0.2	7.0	9	10	17	8	13	9	11	14	7	4	6	12	11
20	1600-1700	10.7	0.3	10.0	11	9	13	9	11	9	9	10	11	17	11	15	11
21	1700-1800	18.9	0.1	8.0	17	20	12	10	5	27	17	16	23	5	6	26	19
22	1800-1900	15.2	0.2	9.0	19	12	10	11	11	6	25	20	9	12	7	16	19
23	1900-2000	12.3	0.2	5.0	7	10	12	6	10	15	15	10	17	7	4	10	17
24	2000-2100	14.1	0.1	13.0	15	10	8	15	9	17	28	11	9	8	11	16	12
25	2100-2200	23.1	0.3	11.5	13	11	23	12	7	11	123	10	12	10	11	11	10
26	2200-2300	7.2	0.1	5.0	13	14	6	1	8	5	14	4	2	1	9	2	3
27	2300-2400	3.9	0.0	1.0	5	6	1	2	1	4	4	7	3	3	0	2	3
28		201.6	4.2	173.0	205	188	284	154	167	168	331	198	172	217	192	193	192
29
30	57.8%				75.6%	59.0%	35.5%	70.8%	75.4%	54.8%	31.4%	54.0%	59.9%	53.5%	52.6%	65.8%	62.5%
Sheet1

Cell Formulas
Range	Formula
B4:B28	B4	=SUM((WEEKDAY($E$2:$EE$2,2)<6)*($E4:$EE4)/SUM(1*(WEEKDAY($E$2:$EE$2,2)<6)))
C4:C28	C4	=SUM((WEEKDAY($E$2:$EE$2,2)=6)*($E4:$EE4)/SUM(1*(WEEKDAY($E$2:$EE$2,2)=6)))
D4:D28	D4	=SUM((WEEKDAY($E$2:$EE$2,2)=7)*($E4:$EE4)/SUM(1*(WEEKDAY($E$2:$EE$2,2)=7)))
A30	A30	=AVERAGE(E30:Q30)

 

[Rex987456]

Sorry hit post before responding. The EE is just to factor in future additions, but I am only what I showed in screenshot far into the spreadsheet. How I have tried testing with Column Q being the end column, which does work for all of them, but any further than columns that have data in them, M-F works and Sunday works, but for some strange reason Saturday breaks. Why only Saturday? lol. The formulas for Saturdays looks same as the other days, that's why its boggling my mind, but I know nothing about the Workday command until today, so figured I was just doing something incorrect.

 

[RoryA]

A blank cell will return 6 for weekday (31/12/1899 was a Saturday apparently), so you'll need to test for those additionally:

=SUM((WEEKDAY($E$2:$EE$2,2)=6)*($E4:$EE4)/SUM(1*(WEEKDAY($E$2:$EE$2,2)=6)*($E$2:$EE$2<>"")))

 

[Rex987456]

A blank cell will return 6 for weekday (31/12/1899 was a Saturday apparently), so you'll need to test for those additionally:

=SUM((WEEKDAY($E$2:$EE$2,2)=6)*($E4:$EE4)/SUM(1*(WEEKDAY($E$2:$EE$2,2)=6)*($E$2:$EE$2<>"")))

Thank you so much! Wow that was completely random and explains why it was only Saturday that was giving issues!