Days counter (excluding weekends)

[msword]

Hi. I wonder is Excel powerful enough to create days counter that skips weekends(gray squares in a pic):

Let's imagine that my goal is to do 2 push ups a day and I am using excel table to track the progress. Since July 13th is the 1st day of my workout program, by July 20 I need to make 6*2=12 push ups total (excluding Saturday(July 17) and Sunday(July 18)) etc.
There is no problem with calculation of the real number of push ups just utilizing the SUM() function. But how to determine the July 20 (or respectively any other white cell) as a 6th day and bound it somehow to current date to observe my progress in % in real time. Of course it should be updated automatically each day.
For example by July 20 I need to make 12 push ups. But I skipped 1 day and made only 10 push ups which is only 83% of the planned work.
Basically I want to made Excel to calculate that 83% for me.

 

[Crystalyzer]

Is this 2 pushups a day cumulative so that on day 2 you should be doing 4 pushups and on day 3 you should do 6 pushups?

 

[Crystalyzer]

The short answer is yes, Excel can do this using the NETWORKDAYS function to get the denominator of your percent calculation as below

=NETWORKDAYS(7/13/2021,TODAY())*2

 

[Crystalyzer]

PushUps.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	Start Date	7/7/2021
2	Target per Day	2
3	Target to Date	12
4	Total Completed	10
5	Total Completed Today	2
6	Pct Completed	83%
7
8		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
10	July							2	2	2			0	2	2
12	August
14	September
16	October
18	November
20	December
21
Sheet2

Cell Formulas
Range	Formula
C3	C3	=NETWORKDAYS(StartDate,TODAY())*TargetPerDay
C4	C4	=SUM(C10:AG10,C12:AG12,C14:AG14,C16:AG16,C18:AG18,C20:AG20)
C5	C5	=INDEX(Data,MATCH(MONTH(TODAY()),Months,0),MATCH(DAY(TODAY()),Days,0))
C6	C6	=+TotalCompleted/TargetToDate
B10	B10	=DATE(YEAR($C$1),MONTH($C$1),1)
B12,B20,B18,B16,B14	B12	=+B10+31

 

[msword]

Thanks a lot for this response. Sounds like a magic to me. I really appreciate you created this sheet for me.
But when I tried to reproduce this "Target to Date" returning me just a bulk date like 3-jan-00, not a number.

routine 2021.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
2		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
3	JULY	x	x	x	x	x	x	x	x	x	x	x	x	1				x	x						x	x						x
4	AUGUST	x						x	x						x	x						x	x						x	x
5	SEPTEMBER				x	x						x	x						x	x						x	x					x
6	OCTOBER		x	x						x	x						x	x						x	x						x	x
7	NOVEMBER						x	x						x	x						x	x						x	x			x
8	DECEMBER				x	x						x	x						x	x						x	x
9
10	start day	13-Jul-21
11	target per day	2
12	target to date	3-Jan-00
13	total completed	1
coding

Cell Formulas
Range	Formula
B12	B12	=NETWORKDAYS(B10, TODAY())
B13	B13	=SUM(B3:AF8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:AF3	Cell Value	beginning with "ex"	text	NO
B8:AF8	Cell Value	beginning with "ex"	text	NO
B8:AF8	Cell Value	beginning with "x"	text	NO
B8:AF8	Cell Value	beginning with "0"	text	NO
B8:AF8	Cell Value	beginning with "1"	text	NO
B7:AF7	Cell Value	beginning with "ex"	text	NO
B7:AF7	Cell Value	beginning with "x"	text	NO
B7:AF7	Cell Value	beginning with "0"	text	NO
B7:AF7	Cell Value	beginning with "1"	text	NO
B6:AF6	Cell Value	beginning with "ex"	text	NO
B6:AF6	Cell Value	beginning with "x"	text	NO
B6:AF6	Cell Value	beginning with "0"	text	NO
B6:AF6	Cell Value	beginning with "1"	text	NO
B5:AF5	Cell Value	beginning with "ex"	text	NO
B5:AF5	Cell Value	beginning with "x"	text	NO
B5:AF5	Cell Value	beginning with "0"	text	NO
B5:AF5	Cell Value	beginning with "1"	text	NO
B4:AF4	Cell Value	beginning with "ex"	text	NO
B4:AF4	Cell Value	beginning with "x"	text	NO
B4:AF4	Cell Value	beginning with "0"	text	NO
B4:AF4	Cell Value	beginning with "1"	text	NO
B3:AF3	Cell Value	beginning with "x"	text	NO
B3:AF3	Cell Value	beginning with "0"	text	NO
B3:AF3	Cell Value	=1	text	NO
B3:AF3	Cell Value	=2	text	NO

 

[msword]

Is this 2 pushups a day cumulative so that on day 2 you should be doing 4 pushups and on day 3 you should do 6 pushups?

They are not, it's just 2 push ups a day every day.

 

[Crystalyzer]

Thanks a lot for this response. Sounds like a magic to me. I really appreciate you created this sheet for me.
But when I tried to reproduce this "Target to Date" returning me just a bulk date like 3-jan-00, not a number.

routine 2021.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
2		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
3	JULY	x	x	x	x	x	x	x	x	x	x	x	x	1				x	x						x	x						x
4	AUGUST	x						x	x						x	x						x	x						x	x
5	SEPTEMBER				x	x						x	x						x	x						x	x					x
6	OCTOBER		x	x						x	x						x	x						x	x						x	x
7	NOVEMBER						x	x						x	x						x	x						x	x			x
8	DECEMBER				x	x						x	x						x	x						x	x
9
10	start day	13-Jul-21
11	target per day	2
12	target to date	3-Jan-00
13	total completed	1
coding

Cell Formulas
Range	Formula
B12	B12	=NETWORKDAYS(B10, TODAY())
B13	B13	=SUM(B3:AF8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:AF3	Cell Value	beginning with "ex"	text	NO
B8:AF8	Cell Value	beginning with "ex"	text	NO
B8:AF8	Cell Value	beginning with "x"	text	NO
B8:AF8	Cell Value	beginning with "0"	text	NO
B8:AF8	Cell Value	beginning with "1"	text	NO
B7:AF7	Cell Value	beginning with "ex"	text	NO
B7:AF7	Cell Value	beginning with "x"	text	NO
B7:AF7	Cell Value	beginning with "0"	text	NO
B7:AF7	Cell Value	beginning with "1"	text	NO
B6:AF6	Cell Value	beginning with "ex"	text	NO
B6:AF6	Cell Value	beginning with "x"	text	NO
B6:AF6	Cell Value	beginning with "0"	text	NO
B6:AF6	Cell Value	beginning with "1"	text	NO
B5:AF5	Cell Value	beginning with "ex"	text	NO
B5:AF5	Cell Value	beginning with "x"	text	NO
B5:AF5	Cell Value	beginning with "0"	text	NO
B5:AF5	Cell Value	beginning with "1"	text	NO
B4:AF4	Cell Value	beginning with "ex"	text	NO
B4:AF4	Cell Value	beginning with "x"	text	NO
B4:AF4	Cell Value	beginning with "0"	text	NO
B4:AF4	Cell Value	beginning with "1"	text	NO
B3:AF3	Cell Value	beginning with "x"	text	NO
B3:AF3	Cell Value	beginning with "0"	text	NO
B3:AF3	Cell Value	=1	text	NO
B3:AF3	Cell Value	=2	text	NO

Format the cell to a number format for Target Date

 

[msword]

Format the cell to a number format for Target Date

Lol. It worked like a charm! I still can not believe it's possible! You made my year.

 

[Crystalyzer]

You're welcome! Please mark my post as the solution to your question.

 

[msword]

What if I want to go even deeper, and see changes history for each day in % on a chart, which also should be updated automatically is it possible?