+ Reply to Thread
Results 1 to 11 of 11

Working with specific dates against Week Commencing dates

  1. #1
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft Office 365
    Posts
    127

    Working with specific dates against Week Commencing dates

    Evening all ..

    Basically im setting up a workbook to assist with weekly performance management but have encountered a wall I need help to climb.

    For Example

    I will have a master sheet that pulls in all data which is going to work off dates

    On the slave sheet In cell A2 I will type in today's date so 01/04/2015 and the persons name in Cell B2

    My Master sheet is working off week commencing dates for example this week is WC 30/03/2015

    Is there a formula that will look in the sheet and say well 1st April 2015 (Wednesday) is in the week starting the 30th March 2015 (Monday) and pull in the values from that column based on a name in Column B as mentioned above .. The weeks will start in Column G onwards for all 52 weeks.

    Not forgetting that the next column will need any information for the following week (Monday to Friday)

    Thanks
    Dave

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Working with specific dates against Week Commencing dates

    As you are using Excel 2003, according to your profile, you can't use COUNTIFS and SUMIFS. You can, however, use SUMPRODUCT to compare dates to week start dates and week end dates (week start date + 6)

    Suggest that you post a sample workbook.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft Office 365
    Posts
    127

    Re: Working with specific dates against Week Commencing dates

    Quote Originally Posted by TMS View Post
    As you are using Excel 2003, according to your profile, you can't use COUNTIFS and SUMIFS. You can, however, use SUMPRODUCT to compare dates to week start dates and week end dates (week start date + 6)

    Suggest that you post a sample workbook.

    Regards, TMS
    Hello

    I have updated my profile to reflect the new version of Excel I now have access to at work.

    For some reason im unable to upload the file as an attachment here so have uploaded to Tiny Upload and can be accessed here >> http://s000.tinyupload.com/index.php...58492798925135

    Basically on the Manager1 sheet this will be updated with the example data I have entered in the cells and then on the overview sheet I want this data to be displayed in the correct row for that User and in the correct cell in regards to the week commencing date based on the date entered in the Manager1 sheet.

    Thanks

    Dave

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Working with specific dates against Week Commencing dates

    OK, let's start with some basics. Having "WC 23-Mar", etc., as column headers is not helpful. You need to put real dates in with a Custom Format if you want it to look pretty. For example, 23/03/2015 formatted as
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    will look just the same but will allow you to date comparisons. Other than that, I'm not quite sure what it is you want to do.

    Regards, TMS

  5. #5
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft Office 365
    Posts
    127

    Re: Working with specific dates against Week Commencing dates

    Quote Originally Posted by TMS View Post
    OK, let's start with some basics. Having "WC 23-Mar", etc., as column headers is not helpful. You need to put real dates in with a Custom Format if you want it to look pretty. For example, 23/03/2015 formatted as
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    will look just the same but will allow you to date comparisons. Other than that, I'm not quite sure what it is you want to do.

    Regards, TMS
    Ok i have changed the date formats to be in "dd/mm/yyyy".

    Basically what im looking for is ... The Manager1 sheet is were for example when i do a review for someone i will put in the persons name and the current date on that sheet.

    What i want to happen is i need a formula in the Overview sheet which will look at the Manager1 sheet and if there is a value in this sheet with a date within the week commencing date then i want it to put a Y in the cell inline with that persons name and in the correct week commencing box.

    So for example ...

    I fill in the first row on the Manager sheet with the name User1 and i put todays date in 03/04/2015 i then want the overview sheet to pick that up and put a Y in the row inline with User1 and in the column for Week Commencing 30/03/2015.

    I hope that makes sense

    Thanks
    Dave

  6. #6
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft Office 365
    Posts
    127

    Re: Working with specific dates against Week Commencing dates

    Quote Originally Posted by TMS View Post
    OK, let's start with some basics. Having "WC 23-Mar", etc., as column headers is not helpful. You need to put real dates in with a Custom Format if you want it to look pretty. For example, 23/03/2015 formatted as
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    will look just the same but will allow you to date comparisons. Other than that, I'm not quite sure what it is you want to do.

    Regards, TMS
    Hi TMS

    Any updates on this so i can finish this project ?

    Thanks
    Dave

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Working with specific dates against Week Commencing dates

    G2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copied across and down.

    That assumes you make the dates into true dates and format them as described. You also need to make the user IDs consistent.

    See the updated example.


    Regards, TMS
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft Office 365
    Posts
    127

    Re: Working with specific dates against Week Commencing dates

    Quote Originally Posted by TMS View Post
    G2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copied across and down.

    That assumes you make the dates into true dates and format them as described. You also need to make the user IDs consistent.

    See the updated example.


    Regards, TMS
    That works perfectly !!!

    Thanks TMS

    Quick Question though ... due to this being related to team members and the managers which can change at anytime I was wondering if it was possible to do the following .....

    The file is laid out in format of Column A being the Managers name and B being the Staff Members name rather than the formula itself containing the managers name to the relevant tab could this be fetched from the cell in column A ?

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    So as above instead of Manager1 it could say A2 which would contain the managers name which is an exact copy of the tab names

    So at least then if Managers or Team Members do change this can be updated within Column A and / or B which would automatically update the vlookup value in the formula ?

    Thanks
    Dave

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Working with specific dates against Week Commencing dates

    For that you'd need to use INDIRECT which makes the whole thing messy AND volatile ... which can mean slow. Personally, I'd add a Manager column to the data and have everything on one sheet. Then you can make the data into a structured table and sort it and filter it, etc. A lot simpler to manage. Unless you have matrix management, an employee will only be in the list once so the manager is largely irrelevant for this anyway.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    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


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  10. #10
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft Office 365
    Posts
    127

    Re: Working with specific dates against Week Commencing dates

    Quote Originally Posted by TMS View Post
    For that you'd need to use INDIRECT which makes the whole thing messy AND volatile ... which can mean slow. Personally, I'd add a Manager column to the data and have everything on one sheet. Then you can make the data into a structured table and sort it and filter it, etc. A lot simpler to manage. Unless you have matrix management, an employee will only be in the list once so the manager is largely irrelevant for this anyway.
    Yeah that's true.

    Thanks for your help on this TMS

    Dave

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Working with specific dates against Week Commencing dates

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need to get week commencing forumla working *Pleaaaaase help*
    By s786 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-16-2013, 03:24 PM
  2. [SOLVED] Putting dates into week commencing
    By Redder Lurtz in forum Excel General
    Replies: 6
    Last Post: 06-01-2012, 05:03 AM
  3. dates for 4 day working week help
    By interested in forum Excel General
    Replies: 10
    Last Post: 02-05-2012, 12:34 PM
  4. Populate cell with Week Commencing dates
    By lucraft in forum Excel General
    Replies: 6
    Last Post: 09-07-2011, 09:41 AM
  5. Replies: 3
    Last Post: 07-17-2010, 12:56 PM

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