IF Statement with dates

1. 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?

2. 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!).

3. 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!

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

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