+ Reply to Thread
Results 1 to 14 of 14

Finding a date from a row based on cell

  1. 06-04-2014, 07:16 AM #1
    cantona_lives
    cantona_lives is offline
    Registered User
    Join Date
    06-14-2013
    Location
    Essex, England
    MS-Off Ver
    Excel 2013
    Posts
    21

    Question Finding a date from a row based on cell

    Hi,
    Producing a Gantt chart with dates running across the top row. Would like to pull back the earliest date and latest date based on what is in the cell below.

    Basically if 1 is in the cell, it would pull back the corresponding date.


    I am sure this is indexing and maybe matching but advice please....


    Thanks,


    -CL
    Reply With QuoteRegister To Reply
  2. 06-04-2014, 07:19 AM #2
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Finding a date from a row based on cell

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


    Something like this?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  3. 06-04-2014, 07:36 AM #3
    cantona_lives
    cantona_lives is offline
    Registered User
    Join Date
    06-14-2013
    Location
    Essex, England
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: Finding a date from a row based on cell

    Thank you. That's interesting and partially works. I tried to adapt it to <>"", so instead of 1 it searched for not empty. However that did not work.

    Also using similar is there a way to find the last date in the sequence? So a start date and an end date?

    Thanks again...

    -CL,
    Reply With QuoteRegister To Reply
  4. 06-04-2014, 07:40 AM #4
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Finding a date from a row based on cell

    Upload a small sample workbook pls to see how your data looks like.
    Reply With QuoteRegister To Reply
  5. 06-04-2014, 07:54 AM #5
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Finding a date from a row based on cell

    Try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. after placing the formula in the cell, hold down the Ctrl+Shift and then press Enter.)

    Please Login or Register  to view this content.
    Regards
    sktneer

    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.
    Reply With QuoteRegister To Reply
  6. 06-04-2014, 07:55 AM #6
    cantona_lives
    cantona_lives is offline
    Registered User
    Join Date
    06-14-2013
    Location
    Essex, England
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: Finding a date from a row based on cell

    Hi,
    Cannot upload due to restrictions, however here is an example image.

    image.jpg

    So start date would be 08/01/14, finish would be 14/01/14

    Thanks,

    -CL
    Reply With QuoteRegister To Reply
  7. 06-04-2014, 08:00 AM #7
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Finding a date from a row based on cell

    according TO your pic

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

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


    OR


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


    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 06-04-2014, 08:01 AM #8
    cantona_lives
    cantona_lives is offline
    Registered User
    Join Date
    06-14-2013
    Location
    Essex, England
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: Finding a date from a row based on cell

    Sadly this did not work either... - Sktneer.

    Thanks,

    -CL.
    Reply With QuoteRegister To Reply
  9. 06-04-2014, 08:02 AM #9
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Finding a date from a row based on cell

    Try these Array Formulas. (See post#5 for how to confirm an array formula)

    In B2
    Please Login or Register  to view this content.
    In C2
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 06-04-2014, 08:04 AM #10
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Finding a date from a row based on cell

    That will work if you confirm the formula correctly. See the attached sheet.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 06-04-2014, 08:07 AM #11
    cantona_lives
    cantona_lives is offline
    Registered User
    Join Date
    06-14-2013
    Location
    Essex, England
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: Finding a date from a row based on cell

    Quote Originally Posted by Fotis1991 View Post
    according TO your pic

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

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




    OR


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


    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Thanks - very close I think. However that does not work when numbers change in scope for instance - see attached.Attachment 322860
    Reply With QuoteRegister To Reply
  12. 06-04-2014, 08:09 AM #12
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Finding a date from a row based on cell

    Sorry. Not able to follow you and even worst to follow a picture.

    Good luck.
    Reply With QuoteRegister To Reply
  13. 06-04-2014, 08:16 AM #13
    cantona_lives
    cantona_lives is offline
    Registered User
    Join Date
    06-14-2013
    Location
    Essex, England
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: Finding a date from a row based on cell

    Quote Originally Posted by sktneer View Post
    That will work if you confirm the formula correctly. See the attached sheet.

    Spot on - thank you!!

    -CL.
    Reply With QuoteRegister To Reply
  14. 06-04-2014, 08:30 AM #14
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Finding a date from a row based on cell

    Glad it helped. Thanks for the feedback and rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to change colour of cell based on finding a specific word in the cell
    By Shelby761 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-24-2014, 09:46 AM
  2. [SOLVED] Function for finding weekly average hours based on date
    By psa916 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-15-2013, 03:22 PM
  3. Help with Finding Date based on User Input
    By micmcgee in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-08-2013, 05:22 PM
  4. [SOLVED] Finding the Monday date based on a different date in same week
    By dandiehl in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-11-2006, 01:10 PM
  5. Finding average price based on date
    By Nyanko in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-19-2005, 04:49 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1