Automation by formula

[James Clear]

Hi All,

I have dates starting from D3:H3 ( 1-08-2021 to 31-08-2021 )

I have to Plot few values in D4 with the help of Formula and not by vba


1. If Sunday Saturday - Value should come as " OFF"
2. 2nd Saturday - " OFF" ( 7th Aug'21)
3. Wednesday , Thursday , Friday = " Work from Office "
4. Monday , Tuesday = " Work From Home"


Pls help me to get this result with the help of formula and not by VBA

 

[Dave Patton]

What values do you want to use?

T202107a.xlsm
	D	E	F	G	H	I
1					Weekdays
2
3	Sun 01-Aug-21	Mon 02-Aug-21	Tue 03-Aug-21	Wed 04-Aug-21	Thu 05-Aug-21	Fri 06-Aug-21
4	Off	From Home	From Home	From Home	From Office	From Office
8c

Cell Formulas
Range	Formula
D4:I4	D4	=IF(WEEKDAY(D3,2)>5,"Off",IF(WEEKDAY(D3,2)>3,"From Office","From Home"))

or with a guess for numbers

T202107a.xlsm
	D	E	F	G	H	I
1					Weekdays
2
3	Sun 01-Aug-21	Mon 02-Aug-21	Tue 03-Aug-21	Wed 04-Aug-21	Thu 05-Aug-21	Fri 06-Aug-21
4	0	75	75	75	80	80
5
8c

Cell Formulas
Range	Formula
D4:I4	D4	=IF(WEEKDAY(D3,2)>5,0,IF(WEEKDAY(D3,2)>3,80,75))

 

[James Clear]

What values do you want to use?

T202107a.xlsm
	D	E	F	G	H	I
1					Weekdays
2
3	Sun 01-Aug-21	Mon 02-Aug-21	Tue 03-Aug-21	Wed 04-Aug-21	Thu 05-Aug-21	Fri 06-Aug-21
4	Off	From Home	From Home	From Home	From Office	From Office
8c

Cell Formulas
Range	Formula
D4:I4	D4	=IF(WEEKDAY(D3,2)>5,"Off",IF(WEEKDAY(D3,2)>3,"From Office","From Home"))

or with a guess for numbers

T202107a.xlsm
	D	E	F	G	H	I
1					Weekdays
2
3	Sun 01-Aug-21	Mon 02-Aug-21	Tue 03-Aug-21	Wed 04-Aug-21	Thu 05-Aug-21	Fri 06-Aug-21
4	0	75	75	75	80	80
5
8c

Cell Formulas
Range	Formula
D4:I4	D4	=IF(WEEKDAY(D3,2)>5,0,IF(WEEKDAY(D3,2)>3,80,75))

Thanks a lot Dave , formula worked , however only obstacle is 2nd Saturday

Requirement is I want to give "OFF" only on 7th Aug'21 and remaining Saturdays , it would be office

How can i achieve this? pls guide?

 

[Dave Patton]

The 7th of August is the first Sat of August.

Review the following and adapt to your actual criteria.

T202107a.xlsm
	D	E	F	G	H	I	J
1					Weekdays
2
3	Sun 01-Aug-21	Mon 02-Aug-21	Tue 03-Aug-21	Wed 04-Aug-21	Thu 05-Aug-21	Fri 06-Aug-21	Sat 07-Aug-21
4	0	75	75	80	80	80	0
5
8c

Cell Formulas
Range	Formula
D4:J4	D4	=IF(AND(WEEKDAY(D3,2)=6,DAY(D3)>6,DAY(D3)<12),0,IF(WEEKDAY(D3,2)>6,0,IF(WEEKDAY(D3,2)>2,80,75)))

 

[James Clear]

The 7th of August is the first Sat of August.

Review the following and adapt to your actual criteria.

T202107a.xlsm
	D	E	F	G	H	I	J
1					Weekdays
2
3	Sun 01-Aug-21	Mon 02-Aug-21	Tue 03-Aug-21	Wed 04-Aug-21	Thu 05-Aug-21	Fri 06-Aug-21	Sat 07-Aug-21
4	0	75	75	80	80	80	0
5
8c

Cell Formulas
Range	Formula
D4:J4	D4	=IF(AND(WEEKDAY(D3,2)=6,DAY(D3)>6,DAY(D3)<12),0,IF(WEEKDAY(D3,2)>6,0,IF(WEEKDAY(D3,2)>2,80,75)))

My bad . Yes it was a First saturday.

Problem solved, thanks for your assistance with this matter.

 

[Dave Patton]

Thanks for the feedback.