Hi,
I have set of dates of 200 rows in column A.I just want to find the first and last weekday of a recent completed week.
27-Jan-14
28-Jan-14
29-Jan-14
30-Jan-14
31-Jan-14
3-Feb-14
4-Feb-14
5-Feb-14
6-Feb-14
7-Feb-14
10-Feb-14
11-Feb-14
Here 3-feb-14 is monday and 7-feb-14 is friday of recent whole week.I just tried with weekday formula.But unable to fix it when i am in the middle of week now as 11-feb-14 is the last entry.Can anyone help to fix it?
regards,
Netaji
does this work for you
=IF(WEEKDAY(A1,2)=1, "Monday", IF(WEEKDAY(A1,2)=5,"Friday",""))
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Try this formula to get the last Friday
=LOOKUP(2,1/(WEEKDAY(A1:A1000)=6),A1:A1000)
Then assuming you will always also have the Monday of that week just subtract 4 from that to get the last Monday
Audere est facere
Thanks Etaf & Daddy for your quick reply.In other words ,Is it possible to find the first and last possible/available weekday of given data.Here in this example the recent completed whole week is between 4-feb-14 & 6-feb-14.Monday and friday are missing.I want the result to show that the first possible weekday = 4-feb-14
The last possible weekday = 6-feb-14.I think it is bit challenging.Can anybody help me to find a way out?Thanks in advance.
14-Jan-14
15-Jan-14
16-Jan-14
17-Jan-14
20-Jan-14
21-Jan-14
22-Jan-14
23-Jan-14
24-Jan-14
27-Jan-14
28-Jan-14
29-Jan-14
30-Jan-14
31-Jan-14
4-Feb-14
5-Feb-14
6-Feb-14
10-Feb-14
11-Feb-14
Regards,
Netaji
Is there any blank cell between 31-Jan and 4-Feb, 6-Feb and 10-Feb?Originally Posted by Netaji
Quang PT
No bebo.No blanks between rows.I just made it purposefully to view easily.Thats all.
Regards,
Netaji
If the Monday and Friday are missing in that week why is it a "whole week"? Why isn't 10th/11th a whole week?
Yes Daddy.You are right.The given data is available only till 11-feb-14.So still 3 days left in that week to qualify as a whole week.If the last row has the date of 17-feb-14 then as you said 10/11-feb-14 would automatically qualify as most recent whole week.I guess i made it clear.
Regards,
Netaji
Let's say your range of dates is A1:A1000 including dates and blank cells for growing up later.
Your last date will be:
=LOOKUP(10^10,$A$1:$A$1000) (11th-Feb)
Since Excel starts to count days from 1/1/1900 (Sunday) as 1 and go on, the last Sunday from Last Date will be:
=INT(LOOKUP(10^10,$A$1:$A$1000)/7)+1 (9th-Feb)
1) then the last Friday will be:
=INT(LOOKUP(10^10,$A$1:$A$1000)/7)-1 (7th-Feb)
Now we have the date in range that nearest last Friday:
(A)=LOOKUP(INT(LOOKUP(10^10,$A$1:$A$1000)/7)*7-1,$A$1:$A$1000) (6th-Feb)
2) then the last Monday wil be:
=INT(LOOKUP(10^10,$A$1:$A$1000)/7)*7-5 (3rd-Feb)
to find the order of the date in range that nearest the last Monday:
=MATCH(INT(LOOKUP(10^10,$A$1:$A$1000)/7)*7-5,A1:A1000,1)
then to find the date next in range:
(B)=INDEX(A1:A1000,MATCH(INT(LOOKUP(10^10,$A$1:$A$1000)/7)*7-5,A1:A1000,1)+1) (4th-Feb)
Are (A) and (B) formulas you are looking for?
Thanks mate.Your suggestion helped me really
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks