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
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
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
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