Excel Forum

hi dear,
I have date 31/01/2016 i want to find Friday for this date?how can I?

Please explain more detail. What Friday? Before or After your specify date?

Before and after this specified date?

Before

=A1+LOOKUP(WEEKDAY(A1,2),{1,2,3,4,5,6,7},-{3,4,5,6,0,1,2})

After

=A1+LOOKUP(WEEKDAY(A1,2),{1,2,3,4,5,6,7},{4,3,2,1,0,6,5})

Thanks bro i shall try and revert

I test it this formula only works for month end if i change to like 01/01/2016 or 01/02/2016...it is not woorking

If you need all backward and all forward date. You can replace 0 with 7.

Ok,thanks

Could you give some explaination about this formula?if kindly could you upload file relating these?

ขอจดสูตรหน่อยครับ...

Pls give me a try.

The formula Weekday(serial number,[return_type]) return you the day of the week corresponding to a serial number (Date). [Return_type] = 2 refer to Monday is the first day of the week. So if a date is Monday the formula return you 1.

The formula Lookup(Lookup_value,Lookup_vector,[Result_vector]) looks in 'Lookup_vector' for a 'Lookup_value' and return a value in the same position in 'Result_vector'. For example;

=Lookup(Weekday('1/1/2016',2),{1,2,3,4,5,6,7},{4,3,2,1,7,6,5})
~~1/1/2016 is Friday so Weekday return 5~~
=Lookup(5,{1,2,3,4,5,6,7},{4,3,2,1,7,6,5})
=7

For additional explaination pls see link below.
Lookup
Weekday

Thank,i got it now