Automation by formula

James Clear

New Member
Joined
Jul 12, 2021
Messages
19
Office Version
  1. 2016
  2. 2013
  3. 2010
Platform
  1. Windows
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,964
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What values do you want to use?

T202107a.xlsm
DEFGHI
1Weekdays
2
3Sun 01-Aug-21Mon 02-Aug-21Tue 03-Aug-21Wed 04-Aug-21Thu 05-Aug-21Fri 06-Aug-21
4OffFrom HomeFrom HomeFrom HomeFrom OfficeFrom Office
8c
Cell Formulas
RangeFormula
D4:I4D4=IF(WEEKDAY(D3,2)>5,"Off",IF(WEEKDAY(D3,2)>3,"From Office","From Home"))


or with a guess for numbers
T202107a.xlsm
DEFGHI
1Weekdays
2
3Sun 01-Aug-21Mon 02-Aug-21Tue 03-Aug-21Wed 04-Aug-21Thu 05-Aug-21Fri 06-Aug-21
407575758080
5
8c
Cell Formulas
RangeFormula
D4:I4D4=IF(WEEKDAY(D3,2)>5,0,IF(WEEKDAY(D3,2)>3,80,75))
 

James Clear

New Member
Joined
Jul 12, 2021
Messages
19
Office Version
  1. 2016
  2. 2013
  3. 2010
Platform
  1. Windows
What values do you want to use?

T202107a.xlsm
DEFGHI
1Weekdays
2
3Sun 01-Aug-21Mon 02-Aug-21Tue 03-Aug-21Wed 04-Aug-21Thu 05-Aug-21Fri 06-Aug-21
4OffFrom HomeFrom HomeFrom HomeFrom OfficeFrom Office
8c
Cell Formulas
RangeFormula
D4:I4D4=IF(WEEKDAY(D3,2)>5,"Off",IF(WEEKDAY(D3,2)>3,"From Office","From Home"))


or with a guess for numbers
T202107a.xlsm
DEFGHI
1Weekdays
2
3Sun 01-Aug-21Mon 02-Aug-21Tue 03-Aug-21Wed 04-Aug-21Thu 05-Aug-21Fri 06-Aug-21
407575758080
5
8c
Cell Formulas
RangeFormula
D4:I4D4=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

Well-known Member
Joined
Feb 15, 2002
Messages
4,964
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The 7th of August is the first Sat of August.

Review the following and adapt to your actual criteria.

T202107a.xlsm
DEFGHIJ
1Weekdays
2
3Sun 01-Aug-21Mon 02-Aug-21Tue 03-Aug-21Wed 04-Aug-21Thu 05-Aug-21Fri 06-Aug-21Sat 07-Aug-21
4075758080800
5
8c
Cell Formulas
RangeFormula
D4:J4D4=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)))
 
Solution

James Clear

New Member
Joined
Jul 12, 2021
Messages
19
Office Version
  1. 2016
  2. 2013
  3. 2010
Platform
  1. Windows
The 7th of August is the first Sat of August.

Review the following and adapt to your actual criteria.

T202107a.xlsm
DEFGHIJ
1Weekdays
2
3Sun 01-Aug-21Mon 02-Aug-21Tue 03-Aug-21Wed 04-Aug-21Thu 05-Aug-21Fri 06-Aug-21Sat 07-Aug-21
4075758080800
5
8c
Cell Formulas
RangeFormula
D4:J4D4=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.
 

Forum statistics

Threads
1,143,836
Messages
5,721,082
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top