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
- 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
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
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?
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
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()
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
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
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 ?
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
Status
Not open for further replies.
You have insufficient privileges to reply here.
Related Threads
?
?
?
?
?
Tech Support Guy
9.9M posts
859.7K members
Since 1998
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!