Afternoon all,
Anyone know how I can convert the attached to firstly read as a normal date, then convert into week commencing?
Many thanks,
RL
Afternoon all,
Anyone know how I can convert the attached to firstly read as a normal date, then convert into week commencing?
Many thanks,
RL
To convert, in B2 dragged down (first format column B as Date)
=MID(A2,5,2)&"/"&RIGHT(A2,2)&"/"&LEFT(A2,4)+0
then you can copy and paste special > values back into A
I'm not sure what you want by "week commencing" probably a language issue across the pond here.
Last edited by ChemistB; 05-31-2012 at 01:04 PM.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
To convert select the range - Data - Text to columns - Next - Next - Select Date in Column Data format - Select YMD from ddown - Ok
I don't understand the rest of your requirment
What is needed now is to include this in the formula. What is being requested is the first day of the week in which the given dates fall. So, for instance if it is Monday then applying this to the date should work.
=A2-WEEKDAY(A2,1)+3
That's if the date is in A2
If I helped, please don't forget to add to my reputation. (click on the star below the post)
If the problem is solved, please: Select Thread Tools (on top of your 1st post) -> Mark this thread as Solved.
Failure is not falling down but refusing to get up.
In B2 put
=MID(A2,7,2)&"/"&MID(A2,5,2)&"/"&MID(A2,1,4)
In C2 put
=B2-WEEKDAY(B2,1)+3
Edit - Format as date
Thanks all but especially to Russell. Perfect.
RL
You're welcome. I was hoping to be able to combine the formulae but if you can live with it as it is (maybe hide a column) then so be it.
Cheers from just down the road!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks