+ Reply to Thread
Results 1 to 3 of 3

IF Statement with dates

  1. #1
    Forum Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    Microsoft 365
    Posts
    152

    IF Statement with dates

    Hi All,

    I'm attempting to create an if statement in order to figure out some prorations. I do not think my formula is calculating the date as the date. Right now it's:

    =IF(B2=2/1/2019,(ROUNDUP(1000*11/12,2)),IF(B2=3/1/2019,(ROUNDUP(1000*10/12,2)),IF(B2=1/1/2019,(ROUNDUP(1000*12/12,2)),"")))

    I don't think I'm trying to do anything terribly complicated - just prorate based on month, so if the date in column B is 2/1/2019, then I want 1000 prorated to equal 916.xx.

    I've tried several things, but none of them have been successful.

    Can someone help?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: IF Statement with dates

    Try

    =IF(B2=DATEVALUE("2/1/2019"),(ROUNDUP(1000*11/12,2)),IF(B2=DATEVALUE("3/1/2019"),(ROUNDUP(1000*10/12,2)),IF(B2=(DATEVALUE("1/1/2019"),(ROUNDUP(1000*12/12,2)),"")))

    Consider how you would write 2 divided by 1 divided by 2019 and compare it to B2 in a formula.

    I'd write

    IF(B2=2/1/2019...

    which is what you've done.
    So 2/1/2019 won't be interpreted as a date (I think!).
    Last edited by Special-K; 02-22-2019 at 12:28 PM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    Microsoft 365
    Posts
    152

    Re: IF Statement with dates

    I try that formula and it's still giving the 'false' ("") instead of the proration. What you wrote made me realize that the 2/1/2019 is being divided instead of performing like 'if date then'.

    With that in mind, this works:

    =IF(B2=DATEVALUE("2/1/2019"),(ROUNDUP(1000*11/12,2)),IF(B2=DATEVALUE("3/1/2019"),(ROUNDUP(1000*10/12,2)),IF(B2=DATEVALUE("1/1/2019"),(ROUNDUP(1000*12/12,2)),"")))

    Thank you!

+ 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. If statement with dates
    By itshere in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-09-2015, 11:33 AM
  2. If statement between dates
    By Burt_100 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-08-2014, 12:08 PM
  3. HELP! IF statement containing DATES
    By ohiamluis in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-08-2013, 01:05 PM
  4. If statement with Dates
    By ktkreeger in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-02-2013, 04:26 PM
  5. Excel 2007 : Using Dates in an IF Statement
    By The_Snook in forum Excel General
    Replies: 3
    Last Post: 05-11-2012, 05:55 AM
  6. [SOLVED] IF statement with two dates
    By jbormann in forum Excel General
    Replies: 2
    Last Post: 07-18-2006, 12:32 PM
  7. [SOLVED] If Statement and Dates
    By Toys in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-21-2005, 08:35 AM

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