Week Formula = Yes

[ashani]

Hi,
Please can someone help me to put formula for week. What I'm looking for is in Sheet1 A4:H599 any entry in the last week (Week is Sunday to Saturday) entered than with the name of "ashani" then in Sheet 2 column A4 shows Yes or No.

Thanks

 

[etaf]

is that a specific date - Sat 22nd March to 28th March
OR just last week no matter what date you open the spreadsheet - so it will change
Do you have the dates entered in a cell when the info is added , if so which cell

 

[ashani]

hi etaf
yeah every last week
the date is entered in cell E4:E599

thanks

 

[etaf]

This will give the last sunday
=(INT((A2-1)/7)*7+1)
So today will report 29/3/18
This will give the previous Sunday
=(INT((A2-1)/7)*7+1)-7
so 22/3/20
AND this will give the next saturday
=(INT((A2-1)/7)*7)
so 28/3/20

Is that what you want
So yesterday 28/3/20
it would be
15/3/20 to 21/3/20

we can then replace the A2 with TODAY()
so that you get the previous week

try putting those 2 formulas into cells and testing with different dates to see if thats whats needed

if so then you can use that to complete a formula for the lookup
BUT where is the name entered
"ashani"

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[ashani]

hi Etaf,

thank you for your help.

I may have confused you - sorry.

so I want current week for example 23/03/2020 to 28/03/2020 anywhere is says "ashani" then the cell shows Yes
same as each week so this week for example 29/03/2020 to 04/04/2020

Hope it makes sense.

thank you once again.

 

[etaf]

=SUMPRODUCT((Sheet1!A4:H599="ashina")*(Sheet1!E4:E599>=(INT((TODAY()-1)/7)*7+1)-7)*(Sheet1!E4:E599<=(INT((TODAY()-1)/7)*7)))
should give the count and then can use the IF and the sumproduct >0 to get a Y or N
But still confused on what dates you want
perhaps you could show some dates and what week you want
Is that what you want
So yesterday 28/3/20
it would be
15/3/20 to 21/3/20

also did you try the spreadsheet and test with some date in A2 to see if it returned the correct week !!!!

 

[ashani]

Thanks for your reply - i have copied it here the example to make it easier, so if A has this week date in Sheet 2 then sheet 1 it should show Yes otherwise No

 

[Fluff]

Cross posted Date/Week = Yes or No

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[ashani]

apologise admin - that was someone else, as 3 of us working on the same project. however here is the link.

Date/Week = Yes or No

Hi, i have attached a sheet here as demo = i'm looking for vba code/formula that if in Sheet 2 - A date is showing for current week than automatically Sheet 1 shows yes otherwise show no. This is not for a specific week, it's ongoing. I wonder if someone can please guide me. thanks

www.excelforum.com

 

[etaf]

so you dont want LAST BUt this forthcoming week
so if it was Monday 30/3
then only Sunday 29/3 and Monday 30/3 should be included
Tuesday 31/3
then
Sunday 29/3 and Monday 30/3 and Tuesday 31/3 should be included
ON SUNDAY 5th April we start again
And only the forth coming week is needed
SO only dates of 5th April are used
if not then, i really dont know what you are after - as you said Last week in post 1 and next coming week in 7
I have also posted a link to a spreadsheet showing the date, which you have not said if right or wrong, and if wrong why and what you need