+ Reply to Thread
Results 1 to 7 of 7

Putting dates into week commencing

  1. #1
    Registered User
    Join Date
    08-25-2009
    Location
    Leeds, England
    MS-Off Ver
    Excel 2003
    Posts
    72

    Putting dates into week commencing

    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
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,576

    Re: Putting dates into week commencing

    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

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,904

    Re: Putting dates into week commencing

    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

  4. #4
    Forum Contributor Russell Dawson's Avatar
    Join Date
    03-01-2012
    Location
    Yorkshire, England
    MS-Off Ver
    2007
    Posts
    608

    Re: Putting dates into week commencing

    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.

  5. #5
    Forum Contributor Russell Dawson's Avatar
    Join Date
    03-01-2012
    Location
    Yorkshire, England
    MS-Off Ver
    2007
    Posts
    608

    Re: Putting dates into week commencing

    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

  6. #6
    Registered User
    Join Date
    08-25-2009
    Location
    Leeds, England
    MS-Off Ver
    Excel 2003
    Posts
    72

    Re: Putting dates into week commencing

    Thanks all but especially to Russell. Perfect.

    RL

  7. #7
    Forum Contributor Russell Dawson's Avatar
    Join Date
    03-01-2012
    Location
    Yorkshire, England
    MS-Off Ver
    2007
    Posts
    608

    Re: Putting dates into week commencing

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1