If Statement - Returning Quarter based on Specific Date

J

Jamie

Am using the following formula to determine which quarter a payment was made.
The first statement returns true, however, the second returns false and the
date is 1/21/10.
=IF(AND(G15>12/31/09,G15<4/1/10),"1Q","Continue")
Thanks!
 
T

T. Valko

=IF(AND(G15>12/31/09,G15<4/1/10),"1Q","Continue")

Your dates aren't being evaluated as dates. They're being evaluated as the
math operation:

12 divided by 31 divided by 9 = 0.043010752688172
4 divided by 1 divided by 10 = 0.4

So, if G15 = the date 1/21/2010 then:

AND(G15>12/31/09,G15<4/1/10)

Will *never* be TRUE.

Use cells to hold the dates:

A1 = 12/31/2009
B1 = 4/1/2010

=IF(AND(G15>A1,G15<B1),"1Q","Continue")
 
J

Joe User

Jamie said:
Am using the following formula to determine
which quarter a payment was made. The first
statement returns true, however, the second
returns false and the date is 1/21/10.
=IF(AND(G15>12/31/09,G15<4/1/10),"1Q","Continue")
Click to expand...

In that form, 12/31/09 is interpreted as 12 divided by 31 divided by 9, not
a date.

You can write:

=IF(AND(G15>--"12/31/09",G15<--"4/1/10"),
"1Q","Continue")

But that is consider poor form. It is better to write:

=IF(AND(G15>date(2009,12,31),G15<date(2010,4,1)),
"1Q","Continue")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Function for returning quarter based on current date? 4
Quarter function based on current date 1
Page numbering with Merged Document and IF statements 1
End of Quarter Based on Fiscal Year 2
Calculate Start and End Date of a Quarter 11
Multiple If statements, Calculating quarterly % holding costs 0
Return the current Yearly Quarter based on date 2
Calculation of Quarter 8

Top