Dates
Results 1 to 7 of 7

Thread: Dates
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2015
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dates

    I am a little confused how this works..
    Here is my example:

    Cell E3 =TEXT(DATE(2014, 1, 1)+(COLUMNS('JA Q1'!$A:D)-1)*14+19, "mm/dd/yy") =03/03/2014
    Cell E4 = 60.00
    Cell E5 = 80.00


    Cell E6 =IF(E3>=TODAY(),E4-E5,0) =(20.00)

    So basically in cell E6. Why is this returning (20.00) if my statement is greater than or equal to today's date which is 05/27/2015? Should it be returning 0?

    Thanks in advance,

    Andrew

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dates

    The TEXT function returns....well....TEXT.
    Even if it looks like a number or date, it's still just a TEXT string.

    And believe it or not, the > and < operators consider TEXT strings to be greater than numbers/dates
    So E3 > = TODAY() = TRUE

    Why do you need the TEXT function in E3 ?
    Why not just let it return a real date value, and use Cell formatting to make it appear in the format you like?

    Change E3 to =DATE(2014, 1, 1)+(COLUMNS('JA Q1'!$A:D)-1)*14+19

    OR

    Change formula in E6 to convert the E3 string to a true date value
    =IF(E3+0>=TODAY(),E4-E5,0)
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,000
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Dates

    The value in E3 is Text and not a serial date.

    Try this in E3
    =DATE(2014, 1, 1)+(COLUMNS('JA Q1'!$A:D)-1)*14+19

    Format E3 with any date format you like.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,997
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Dates

    The TEXT function returns a Text value, not a Date value.

    Do you need to TEXT function at all? Why not just use:
    Code:
    =DATE(2014, 1, 1)+(COLUMNS('JA Q1'!$A:D)-1)*14+19
    and format cell E3 as a date in "mm/dd/yyyy" format?

    Otherwise, you will need to convert it back to a date in E6 like this:
    Code:
    =IF(DATEVALUE(E3)>=TODAY(),E4-E5,0)
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Aug 2004
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dates

    You are getting the correct result. From your question, it sounds like you want to reverse the position of E3 and Today(). The formula below would yield 0.

    Cell E6 =IF(TODAY()>=E3,E4-E5,0)

  6. #6
    Board Regular
    Join Date
    Jan 2015
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dates

    Thanks everyone!

    I didn't realize I had it in text or why i did that... Oops.

  7. #7
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dates

    You're welcome
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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