SOLVED
Home

If a cell contains specific text, return this specific text otherwise leave blank

%3CLINGO-SUB%20id%3D%22lingo-sub-778304%22%20slang%3D%22en-US%22%3EIf%20a%20cell%20contains%20specific%20text%2C%20return%20this%20specific%20text%20otherwise%20leave%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778304%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20a%20pre-made%20excel%20spreadsheet%20for%20time%20sheets%20at%20work.%20I%20work%2032%20hours%20a%20week%20with%20Monday%20off.%20I%20am%20trying%20to%20insert%20an%20IF%20function%20that%20if%20the%20Day%20is%20'Monday'%2C%20return%20'Non-Work%20Day'.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20currently%20using%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(B12%3D%22Monday%22%2C%22Non-Work%20Day%2C%22%20%22)%26nbsp%3B%3C%2FP%3E%3CP%3Ehowever%20all%20i%20get%20is%20a%20blank%20cell%2C%20even%20if%20the%20day%20is%20Monday.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20the%20problem%20is%26nbsp%3B%20%3A%20the%20date%20column%20sets%20the%20day%2C%20so%201%20July%202019%20in%20cell%20C11%20automatically%20changes%20the%20day%20in%20cell%20B11%20to%20Monday%2C%20so%20the%20value%20in%20B11%20is%20still%20equal%20to%201%20July%202019%2C%20not%20Monday.%20(formula%20in%20B11%20is%20'%3Dc11)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20get%20excel%20to%20read%20the%20output%20of%20'Monday'%20instead%20of%20the%20input%20of%20'%3Dc11'%20so%20I%20can%20match%20to%20'Monday'%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-778304%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-778362%22%20slang%3D%22en-US%22%3ERe%3A%20If%20a%20cell%20contains%20specific%20text%2C%20return%20this%20specific%20text%20otherwise%20leave%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778362%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384006%22%20target%3D%22_blank%22%3E%40Richie_M%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EThe%20setup%20is%20not%20clear%20to%20me.%20However%20to%20evaluate%20the%20Weekday%20of%20any%20date%20you%20can%20use%20one%20of%202%20functions%3A%3C%2FP%3E%3CUL%3E%3CLI%3EWeekday%20function%20%26gt%3B%26gt%3B%20Returns%20a%20number%20from%201-7%20(or%200%20to%206)%3C%2FLI%3E%3CLI%3ETEXT%20function%20%26gt%3B%26gt%3B%20Returns%20text%20based%20upon%20the%20formatting%20you%20specify%20in%20the%20second%20argument.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EIn%20your%20case%20%2C%20if%20you%20are%20evaluating%20the%20date%20in%20cell%20B12%2C%20then%20your%20function%20will%20be%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(TEXT(B12%2C%22dddd%22)%3D%22Monday%22%2C%22Non%20Working%20Day%22%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20other%20issue%20of%20a%20cell%20referencing%20a%20wrong%20one%2C%20I%20need%20to%20check%20and%20audit%20your%20worksheet%20to%20fix%20it.%3C%2FP%3E%3CP%3EHope%20that%20Helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-778393%22%20slang%3D%22en-US%22%3ERe%3A%20If%20a%20cell%20contains%20specific%20text%2C%20return%20this%20specific%20text%20otherwise%20leave%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778393%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20that%20formula%20works.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20was%20my%20only%20issue%2C%20I%20must%20have%20explained%20my%20issue%20poorly%2C%20thank%20you%20for%20your%20help.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Richie_M
New Contributor

Hi,

 

I am using a pre-made excel spreadsheet for time sheets at work. I work 32 hours a week with Monday off. I am trying to insert an IF function that if the Day is 'Monday', return 'Non-Work Day'. 

 

I am currently using: 

=IF(B12="Monday","Non-Work Day," ") 

however all i get is a blank cell, even if the day is Monday. 

 

I believe the problem is  : the date column sets the day, so 1 July 2019 in cell C11 automatically changes the day in cell B11 to Monday, so the value in B11 is still equal to 1 July 2019, not Monday. (formula in B11 is '=c11)

 

Is there a way to get excel to read the output of 'Monday' instead of the input of '=c11' so I can match to 'Monday'? 

 

Many thanks 

 

 

2 Replies
Solution

@Richie_M 

Hi

The setup is not clear to me. However to evaluate the Weekday of any date you can use one of 2 functions:

  • Weekday function >> Returns a number from 1-7 (or 0 to 6)
  • TEXT function >> Returns text based upon the formatting you specify in the second argument.

In your case , if you are evaluating the date in cell B12, then your function will be

=IF(TEXT(B12,"dddd")="Monday","Non Working Day","")

 

Your other issue of a cell referencing a wrong one, I need to check and audit your worksheet to fix it.

Hope that Helps

Nabil Mourad

@nabilmourad 

Thank you, that formula works. 

 

That was my only issue, I must have explained my issue poorly, thank you for your help. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies