IF formula referencing day of week in cell

TimeFlies1074

New Member
Joined
Jan 14, 2016
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
Reference cell A1 has formula =today(). A1 cell is formatted to display date as Wednesday, August 11, 2021. I need to write a formula that looks at cell A1 and recognizes the day of the week and returns one numerical value if it is a Saturday, and another numerical value if it shows any other day of the week. Thanks in advance for any help.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
Also what values do you want returned?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

johnny51981

New Member
Joined
Jun 8, 2015
Messages
41
You can use the WEEKDAY function to do that for you. =WEEKDAY(A1,[Select the Return Type])
 

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
532
Office Version
  1. 2013
Platform
  1. Windows
Try

for Saturday it will return 1 and for all other days 2

Excel Formula:
=IF(TEXT(A1,"dddd")="Saturday",1,2)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another option
Excel Formula:
=IF(WEEKDAY(A1)=7,1,2)
 
Solution

TimeFlies1074

New Member
Joined
Jan 14, 2016
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
What version of Excel are you using?
Also what values do you want returned?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
I am using the 2019 version of Excel on WIndows. The values I would like to have returned are 80 if a weekday and 0 if weekend. Thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for that.
Another option
Excel Formula:
=80*(WEEKDAY(A1)=7)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
It depends on what the op considers a weekend, which I based on the op
one numerical value if it is a Saturday, and another numerical value if it shows any other day of the week
But missed the fact that 80 was for weekday
 

Forum statistics

Threads
1,148,157
Messages
5,745,107
Members
423,924
Latest member
Gazzat

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