Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Excel Formula trouble - any help appreciated

  • 26-05-2011 11:06am
    #1
    Avien
    Registered Users, Registered Users 2 Posts: 37 ✭


    OK in a nutshell, I work in an office environment which uses week numbers for a lot of reports.

    I want to set up a cell on my spreadsheet that will calculate today’s date and display the current week number based on that date.

    I can visualise what I will need to do this namely a cell with todays date using =today() function. And then a selection of dates with the corresponding week number for reference

    By way of example
    So there are two columns one with week numbers and the other with the relevant dates
    COLUM A COLUMN B
    Week 1 24/05/2011
    Week 1 25/05/2011
    Week 1 26/05/2011
    Week 1 27/05/2011
    Week 1 28/05/2011
    Week 1 29/05/2011
    Week 1 30/05/2011
    Week 2 31/05/2011
    Week 2 01/06/2011
    Week 2 02/06/2011
    Week 2 03/06/2011
    Week 2 04/06/2011
    Week 2 05/06/2011
    Week 2 06/06/2011

    I want the formula to search column B to match =today() and then display the corresponding week number in Column A.

    I’ve tried adding a third column with the formula =if(B1=today(),A1,””) which will match the date and display the week number in column C, but how do I then get a cell to pull the info from one cell that ends up with a value displayed when that cell will be different each day.

    Maybe I’ve just been trying too long and need to take a break but I can’t for the life of me figure this out!

    If anyone can help or has a simpler way of doing this please - I'd love to hear it!


Comments

  • #2
    twerg_85
    Registered Users, Registered Users 2 Posts: 391 ✭✭twerg_85


    Switch the order of the columns and then use vlookup.

    (Or do a formula based on date difference between today and start date of week 1 divided by 7.)


  • #3
    Avien
    Registered Users, Registered Users 2 Posts: 37 ✭Avien


    twerg_85 wrote: »
    Switch the order of the columns and then use vlookup.

    (Or do a formula based on date difference between today and start date of week 1 divided by 7.)

    Worked a treat! Cheers

    I had tried vlookup and couldn't figure out why it wasn't working!?! Simple oversight!


Advertisement