Tech Support Guy banner
Status
Not open for further replies.

Excel - Check for Cell Data

1K views 10 replies 2 participants last post by  snoozee 
#1 ·
Hi

i’m trying to find a formula that would look at cells in a row & if the cell contained a certain text WITHIN a week then it would return a “Yes’ result.

my data contains entries for everyday of the week yet I only need it to check mon-fri data .

Example

table contains multiple data for every day in June, what I need to check is

within every week in June did a particular cell value appear in another colum

is this possible ?
 
#2 ·
will you have dates that are sat/sun and do you want to ignore those

you can test day of week using =weekday(cell with date,2)<6
that will do Mon to Fri

then an IF()

=IF( weekday(cell with date,2)<6, "YES", "NO") or instead of NO put "" for a blank

Then you need to add the text
cell contained a certain text WITHIN a week
- but not sure what you need here or what column , but you can add an AND() with the text =IF( AND(weekday(cell with date,2)<6, Cell with text = "Certain Text needed"), "YES", "NO")

Perhaps
Upload a sample data file with expected results
 
#3 ·
Hi

Thanks for the quick response !!

I've attached an example of my spreadsheet so you can understand what i need.

Column A contains the date the entry was made
Column B contains the name of the area that made the entry
Column C contains the response as to whether the required action was taken

The requirement is that the "Action" is completed on a weekly basis by each area, so what I need is a formula that will check this :)

Is this possible ?
 

Attachments

#4 ·
can you give some examples of where its met and not met in say column D

i have added the day of the week and weeknumber m, and can see a yes in the first few names
But if you could setup where its not correct and highlight

for example row 44 - shows a yes on a saturday , for week 24 and not during the Mon-Fri for that week, so is this incorrect ? and does this need to be flagged?
 

Attachments

#6 ·
but there is a yes on 26th June the Friday of that week, as shown in my attachment with a RED fill
same as monday the 8th is NO but friday 12th June is a yes
Sorry I'm not getting your requirement

The 29th & 30th have a no - but we dint have the rest of that week data -
so this would highlight with what i'm thinking , using a countifs()
 
#7 ·
i have set up a sheet with some helper and indicator columns
We can probably do it without using sumproduct()
But you now have a highlight column False / True

if you look at the highlight column TRUE - should be rows where the week has not a YES during Mon-Fri
 

Attachments

#9 ·
i have set up a sheet with some helper and indicator columns
We can probably do it without using sumproduct()
But you now have a highlight column False / True

if you look at the highlight column TRUE - should be rows where the week has not a YES during Mon-Fri
Hi

How do i change this so it looks at 7 days (Mon-Sun) rather than 5 (Mon-Fri) as i thought i'd fixed it yet it didn't work :-(
 
#8 ·
Hi

Thank you soooo much for helping me with this as I tried to do it in Power BI yet couldn't get it to work.

I've changed it so it is across 7 days rather than 5, I'm going to have to work out how i manage "part" weeks as the rest of the week would roll into the previous/next month.

I could just delete the formula for these as i do a report on a monthly basis, what do you think ? :)
 
#10 ·
change the formula to ignore the count of WeekDay
from
=COUNTIFS(B:B,B3,C:C,"yes",D : D,D3,E:E,"<"&6)=0 (ignore the space in D otherwise we get a face here

to

=COUNTIFS(B:B,B2,C:C,"yes",D : D,D2)=0

this bit E:E,"<"&6 is looking at the helper column E with this formula =WEEKDAY(A2,2) starts with a 1 for monday, which counts only Weekday less than six ie Mon 1 to Fri 5
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top