Google Sheet Schedule for Calling Clients

[Generix]

Hi,

I am using a simple google sheet that lets me schedule calls to clients after x number of days from a start date. Some dates fall on the weekends and I was wondering if there is a way for the date to adjust automatically to avoid a weekend. If the date falls on a Saturday, it would be ideal for it to adjust automatically a day before (Friday). And if it falls on a Sunday, it should automatically adjust a day after (Monday).
Thank you in advance.

-Generix

 

[jdellasala]

This is Excel, but will work in Google Sheets:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Start Date	7 Days	14 Days	28 Days	42 Days	55 Days	68 Days	82 Days	127 Days	172 Days	217 Days	62 Days	307 Days	351 Days
2		7	14	28	42	55	68	82	127	172	217	62	307	352
3		Thu	Thu	Thu	Thu	Wed	Tue	Tue	Fri	Mon	Thu	Wed	Wed	Fri
4	12/22/2022	12/29/2022	01/05/2023	01/19/2023	02/02/2023	02/15/2023	02/28/2023	03/14/2023	04/28/2023	06/12/2023	07/27/2023	02/22/2023	10/25/2023	12/08/2023
5		12/29/2022	01/05/2023	01/19/2023	02/02/2023	02/15/2023	02/28/2023	03/14/2023	04/28/2023	06/12/2023	07/27/2023	02/22/2023	10/25/2023	12/09/2023
6		Thu	Thu	Thu	Thu	Wed	Tue	Tue	Fri	Mon	Thu	Wed	Wed	Sat
Sheet1

Cell Formulas
Range	Formula
B1:N1	B1	=B4-$A$4&" Days"
B3:N3	B3	=B4
B4:N4	B4	=$A$4+IF(WEEKDAY($A$4+B2)=7,B2-1,IF(WEEKDAY($A$4+B2)=1,B2+1,B2))
B5:N5	B5	=$A$4+B2
B6:N6	B6	=B5

As you'll see, the number of days wanted is in row 2, and the number of actual days is in row 1. Row 5 is just the start date plus the number of days in row 2. Ironically only one day in the series falls on a Saturday or Sunday!

 

[Generix]

This is Excel, but will work in Google Sheets:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Start Date	7 Days	14 Days	28 Days	42 Days	55 Days	68 Days	82 Days	127 Days	172 Days	217 Days	62 Days	307 Days	351 Days
2		7	14	28	42	55	68	82	127	172	217	62	307	352
3		Thu	Thu	Thu	Thu	Wed	Tue	Tue	Fri	Mon	Thu	Wed	Wed	Fri
4	12/22/2022	12/29/2022	01/05/2023	01/19/2023	02/02/2023	02/15/2023	02/28/2023	03/14/2023	04/28/2023	06/12/2023	07/27/2023	02/22/2023	10/25/2023	12/08/2023
5		12/29/2022	01/05/2023	01/19/2023	02/02/2023	02/15/2023	02/28/2023	03/14/2023	04/28/2023	06/12/2023	07/27/2023	02/22/2023	10/25/2023	12/09/2023
6		Thu	Thu	Thu	Thu	Wed	Tue	Tue	Fri	Mon	Thu	Wed	Wed	Sat
Sheet1

Cell Formulas
Range	Formula
B1:N1	B1	=B4-$A$4&" Days"
B3:N3	B3	=B4
B4:N4	B4	=$A$4+IF(WEEKDAY($A$4+B2)=7,B2-1,IF(WEEKDAY($A$4+B2)=1,B2+1,B2))
B5:N5	B5	=$A$4+B2
B6:N6	B6	=B5

As you'll see, the number of days wanted is in row 2, and the number of actual days is in row 1. Row 5 is just the start date plus the number of days in row 2. Ironically only one day in the series falls on a Saturday or Sunday!

Thank you very much! It worked wonderfully. This has made my work a lot easier.