Hi, this should be an easy one!!
from the attached file can the date entered into column A be extracted and each eliment put into col B & C so i have
eg:
A B C
01/01/2011 Jan 2011
i also want to be able to cop the formula down for subsequent years
Thanks in advance
Last edited by interested; 02-21-2012 at 09:08 AM.
Do you mean?
=MONTH(A2)
copied down, and formatted as Custom: MMM
and =YEAR(A2)
copied down.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
If you look at the attachement i want col b,c,d as displayed , extracted from the date input col A
So
col B =week number for date entered
col C =month
col D = year
all taken from date inputted.
What i typed in my initial post has not come out correctly for some reason
Not sure what the issue is...
See attached....
Unfortunatly when copied down to subsequent years, there is a problem at the end of 2009, where the week numbers dont match. according to this link http://www.epochconverter.com/date-a....php?year=2009
there is week 53 dec 28th - 3rd jan.
formula is not work ing for this.Any ideas?
Last edited by interested; 02-08-2012 at 09:46 AM.
Can you show that in your sample file.. and what you expect (and why)?
Sample attached with description of where it goes out of sync.
Thanks for helping
The 26th of December, 2009 was a Saturday, and the 27th was a Sunday.
If the transition is wrong there, it would be wrong in all the other Saturday/Sunday dates above... e.g. Dec 12/13 and Dec 19/20, etc...
so not sure if you've got it right... or if you want your Sat/Sun to be within same weeknum?
If that is the case then use
=WEEKNUM(A1,2) where the 2 indicates week starting Monday.
Last edited by NBVC; 02-08-2012 at 11:29 AM.
Yes i do want to include sat and sun in same week number. ill give it a go and compare to website.
Thanks
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Nope, still not correct, does the same when formula copied down. in sample file it goes out of sync around the same place.
Are you sure.. I get 52, just like the website...
Its the week number 53 onwards thats out. week 1 for 2010 is completly out.....
Jan 1st, 2010 was on Friday, Jan 2nd was on Saturday, Jan 3rd was on Sunday.. all in Week 1 of 2010.
Jan 4th - Jan 10th (Mon-Sun) occurred in Week 2 of 2010.
I am not sure how the EpochConverter sees it..
Not sure too, but looking at a callender it is out too. Ill have to do it manually.
Thanks for your time....
Ok, after a little work i have it now to do the week numbers. Although it is out by 1 week no due to sundays ( i think, so can live with that).
Now can anyone sort the next bit : at one point it goes out of sync, and i think it may be the reason the rest of the sheet is like that. and i cant seem to sort.
also if i copy the formula =month down it keeps jan in the column.
Thanks in advance..
In column H, use instead: =F4 and format as MMM.
There is a "Date expert" online that I will ask to review for the other issue....
Thanks,that worked for me. Did you find a solution to the other issue?
hello
can you try this one in Column H
=EDATE(F4,0)
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
That works for too for extracting month. I need something that gives me the week number from date.the previous attached file has an error around week 13,that maybe throwing the rest of the sheet out,with the formula used. Any help greatly appreciated. Otherwise ill have to copy it manually.
hello
can you try
=WEEKNUM(F4,21)
Sorry,that gives an #num! error.do I need to format the cell different? I tried with just 2 at the end and it goes out of sync in 2012. I basically just want the correct week number for dates as per calendar weeks. This week number is 8,but the formula used returns week 9 for this week. The left hand column in uploaded file is correct.
Thanks.
I've found the solution ! ! I'll post the formula later for anyone else. I needed ISO week number,not available in Excel.
Vlady's suggestion will give correct ISO week numbers....but that's only available in Excel 2010, for earlier versions use this formula for the same result
=INT((F4-WEEKDAY(F4,2)-DATE(YEAR(F4+4-WEEKDAY(F4,2)),1,4))/7)+2
Audere est facere
That is the correct formula to use in office 2007.I could have done with your help last weekthread will be closed.....
Thanks to all who helped....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks