Hello,
I'm trying to find a way to have a range of dates from Monday through Sunday display as the week ending date on Sunday.
For example -- today is 11/29/2010, I can use WEEKNUM(A1,2) and get week 49 but I'd like that to show as 12/5/2010.
Thanks for your help!
Mike
Format cell as date and use A1+6.
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
You could add a dummy column as "=TEXT(A1,"ddd")"
then in C1 you could use =IF(B1="Sun",B1, IF(B1="Sat",A1+1,IF(B1="Fri",A1+2,IF(B1="Thu",A1+3,IF(B1="Wed",A1+4,IF(B1="Tue", A1+5,IF(B1="Mon", A1+6)))))))
Might be you may get a easier solution to this from any of the members!!
Last edited by Vinodsralian; 11-29-2010 at 03:37 PM.
=a1+choose(weekday(a1,2),6,5,4,3,2,1,0)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Please Login or Register to view this content.
perfect! thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks