1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Formula - Dates

Discussion in 'Business Applications' started by sk_rizwan, Apr 17, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. sk_rizwan

    sk_rizwan Thread Starter

    Joined:
    Apr 17, 2012
    Messages:
    3
    Hi,

    Need Urgent help. Need a formula, explained in detail in attachment and trying to explain here

    Column A (Inv Value) Column B (Col.Date) week1 (14/4/2012 - 18/4/2012)
    4500 1/5/2012 0 (formula)
    225000 23/4/2012 0
    55393 17/4/2012 55393 (formula)

    * Column A and B are Data field from where we will take data.
    * Column C and D are the fields where i need to put formula

    **** If column C date range is in Column B date, then put the value of Column A
    I can split Column c in two cells with start date and end date if needed (then merge remaining cells)

    Appreciate your help and support on urgent bases to solve my issue. < removed email address - etaf moderator >
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,767
    First Name:
    Wayne
    i have removed the email address to avoid spam
    also we only provide support in posts and not via email or chat programs etc

    I will have a look at the spreadsheet - this is something I carried out a couple of weeks ago for a project gantt chart -
     
  3. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,767
    First Name:
    Wayne
    OK - so I changed the date structure
    i had to change the dates in column C as 1) they where not recognised as a date and some where out the range of the sample
    so i retyped them in - not sure why that is - you may need to check the dates

    if you have a lot of dates - try this
    Put
    =value(c2)
    in a cell away from your data - say AA2
    copy down to cover all rows with data
    this should result in a number
    now
    Copy the range AA2 - AA#
    to C2
    paste special
    value
    into C2
    now format as Date
    and that should fix the date issue - worked on my spreadsheet OK

    I change the header - so that you just have the week starting date and uses cell in last week +7 to work out the next week date


    now the formula uses AND to test if the date in column C is less than the next week and greater or = to this week
    then i just use an if

    so in D2 =IF(AND($C2<E$1,$C2>=D$1),$B2,"")

    i have attached the spreadsheet - sheet 2 with the calculation

    I think you may need to retype all the collections dates again

    have a look see what you think and ask any questions
     

    Attached Files:

  4. sk_rizwan

    sk_rizwan Thread Starter

    Joined:
    Apr 17, 2012
    Messages:
    3
    Dear Etaf,

    Thanks for the formula. It is working fine but it seems problem with date formats as you said.

    Actually, I am exporting this data from another source. Now, the problem arises with date formats. Even i check your solution copying =value(c2) then paste special and formatting dates but it is Not working with me. I have hundreds of rows and need to fix this date formatting. I will try and could you please also find a way.

    Really appreciate your immediate response that you have provided with formula.

    Thank you,
    Rizwan.
     
  5. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,767
    First Name:
    Wayne
    working OK for me and on your original data - if i do a =value(c2) I get numbers for all the dates
    BUT
    when I copy back into the original C2 cells and down - only the first three display a date and so i change to format as date and it works OK

    exactly what are you doing step by step

    perhaps post a spreadsheet with more of the dates in
     
  6. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Just make sure column C is in date format.
    I have include formula in Col J to flip reverse dates.
    Make sure J1 is not deleted.
    Just copy from J1 to the last column and then paste value back into Col C will do.
    If ur dates are in mixed formats, then u have to find a way to control user input.
    Upload more dates so that I can come up with solutions.
     

    Attached Files:

  7. sk_rizwan

    sk_rizwan Thread Starter

    Joined:
    Apr 17, 2012
    Messages:
    3
    Dear,

    Please find the attached sheet that has two columns for dates. i will get the DATA in this format.

    When i select =value(C3) it will work, but when i do for rest of the fields it will not work. I think the whole problem is about formats. Work on the attached sheet and you will know in detail.

    Thanks for your help and support.
     

    Attached Files:

  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,767
    First Name:
    Wayne
    try this
    =IF(ISERROR(DATE(YEAR(C2),MONTH(C2),DAY(C2))),"",(DATE(YEAR(C2),MONTH(C2),DAY(C2))))
    put it in a cell some where on the sheet
    and then
    copy down to match all the rows you have
    then select all the rows in that column
    copy
    paste special
    value
    back into c2
    format cell to date
    works for me
     

    Attached Files:

  9. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455

    I'm seeing this, are you?
    I assume the first date in B2 is 1 Jun 2009

    Is your short date format dd/mm/yy or mm/dd/yy or some other else?
    Try give us in CSV or print screen.
     

    Attached Files:

  10. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    :D:D:D
     

    Attached Files:

  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1049667

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice