LinkBack URL
About LinkBacks
Dear all,
would appreciate any help!
I need to calculate the number of weeks which have passed since a certain day for Project Planning.
Kindly look into the attached attachment.
This is urgent and I am quite a novice in Excel!
Cheers!
billy
Dear all,
would appreciate any help!
I need to calculate the number of weeks which have passed since a certain day for Project Planning.
Kindly look into the attached attachment.
This is urgent and I am quite a novice in Excel!
Cheers!
billy
Hi,
Not sure but try:
=IF(B4="r",WEEKNUM(TODAY(),2)-WEEKNUM(A4,2)+1)
Would greatly appreciate it!
Thanks a million for your quick reply. Just one small request if you can kindly insert thus formal in my attached excel sheet and upload it or mail it to me. I am really a beginner :
See the file. Yellow cell has formula.
oops..thanks martindwilson. any tip how to overcome it?
depends
using say
=IF($B$4="r",(TODAY()-$A$4)/7,"") where a4 =02/08/2014 and today() is 9/1/2015
then with a decimal value for part week
=IF($B$4="r",(today()-$A$4)/7,"") = 22.85714286
you can round that down to 22 with int()
=IF($B$4="r",INT((today()-$A$4)/7),"") =22
or up to 23 with roundup()
=IF($B$4="r",ROUNDUP((today()-$A$4)/7,0),"") =23
or simply round() to nearest whole week
=IF($B$4="r",ROUND((today()-$A$4)/7,0),"")
Last edited by martindwilson; 10-21-2014 at 06:53 AM.
"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
hmm how did that all get posted twice!
that will create problems at the end/beginning of the year
assume today()=03/01/2015
and a4=25/11/2014
then you'd get -46
THANK A LOT!!!!!!!!!!!!!!! I am not sure if its working..will give it a try now and get back to you!!
edited
2 posts see post #11
Last edited by martindwilson; 10-21-2014 at 06:58 AM.
See if this formula overcomes it:
=IF(B4="r",IF((YEAR(A4)=YEAR(TODAY()))*(TODAY()>=A4),WEEKNUM(TODAY(),2)-WEEKNUM(A4,2)+1,IF(YEAR(A4)<YEAR(TODAY()),(WEEKNUM(DATE(YEAR(A4),12,31),2)-WEEKNUM(A4,2))+WEEKNUM(TODAY(),2))),"")
See the file.
@misrasomendra and martindwilson: Thanks a trillion both of you.
I guess the latest solution from misrasomendra should work.
No idea why it got posted twice..though they seems to be slightly different
BIlly Do check my formula on your live data and if it's work that's gr8. Thanks for the feedback.
Sure misrasomendra! Will test it now and get back to you. Thanks a lot!!!!
Thanks a lot misrasomendra! It works perfectly fine!
Really appreciate your help!
And many thanks to you too martin.
Cheers
Billy
Glad to help you and thanks for the feedback, You can mark the thread as solved and if you feel the solution helped you, than please click the star of Add reputation below the comments.
Hi
Have a look at this, in cell C6. It looks like it will solve your problem.
Hope it helps.
Regards.
oops, it looks like it was already solved
Thanks Bob! It works like i wanted!
And many thanks to you too misrasomendra, clicked on add reputation star.
thank you guys and i really appreciate all your help!
Cheers
Billy
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks