Solved Excel - Check for Cell Data

snoozee

Thread Starter
Joined
Apr 10, 2006
Messages
270
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 ?
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,442
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
 

snoozee

Thread Starter
Joined
Apr 10, 2006
Messages
270
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

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,442
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

snoozee

Thread Starter
Joined
Apr 10, 2006
Messages
270
Thanks :)

If we look at Botany we can see that a check was completed for all weeks apart from the week beginning 22nd June, this is what i need to highlight - if possible
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,442
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()
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,442
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

snoozee

Thread Starter
Joined
Apr 10, 2006
Messages
270
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 ? :)
 

snoozee

Thread Starter
Joined
Apr 10, 2006
Messages
270
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 :-(
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,442
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
 

snoozee

Thread Starter
Joined
Apr 10, 2006
Messages
270
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
Thanks and now i understand how it works, I really appreciate you explaining it to me :)
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top