CODEWITHSUNDEEP
excel
Ban Dodoy
Ban Dodoy

Reputation: 11

excel: how to determine if date is within a range of dates

I have following data in my Excel worksheet:

A1: dec. 25, 2017
A2: feb. 25, 2018
A3: jan. 27, 2018
A4: 1
A5: 2
A6: (this is where result should be)

My current formula for A6 is:

if(and(datevalue(text(A2,"mm/dd/yy")) >= datevalue(text(A1,"mm/dd/yy")), datevalue(text(A2,"mm/dd/yy")) <= datevalue(text(A3,"mm/dd/yy"))), A4/A5, 0)

And it genereates 0 with the data provided above which is wrong.

The equation works in dates with the same year but it doesn't work with different year like the example above. I'm working on my scheduling work and I really appreciate if you can help me. thanks

Upvotes: 1

Views: 153

Answers (1)

Sangbok Lee
Sangbok Lee

Reputation: 2229

If A1:A3 are properly formatted to date(time) value, it should not be so difficult to manipulate them. =if(and(a2>=a1, a2<=a3), ...) is enough.

If not, first format them properly. For example, if A1 is string 'dec. 25, 2017', next formula will convert A1 to a valid date value.

=DATE(RIGHT(A1,4), MONTH(1&LEFT(A1,3)), MID(A1,6,2))

Getting a month number from a month string is from this article.

Upvotes: 1

Related Questions