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)
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 :
Would greatly appreciate it!
See the file. Yellow cell has formula.
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
"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
THANK A LOT!!!!!!!!!!!!!!! I am not sure if its working..will give it a try now and get back to you!!
oops..thanks martindwilson. any tip how to overcome it?
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.
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.
hmm how did that all get posted twice!
@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