# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Returning a value if cell date is between two dates

## joeljoel

I have dates in column A for the whole year (format: Wednesday July 12, 2010).    I need a formula in column B to return a value of 1 if the date is between two specific dates.

----------


## Paul

Assuming dates are in A1:A365, and the start and end dates are in C1 and D1, in B1 use

=AND(A1>=$C$1,A1<=$D$1)*1

----------


## joeljoel

What if I want those dates to be specified in this way - March 1, 2010 and October 31, 2012.  That's what I really need to do.

Thanks!

----------


## Paul

You can format a date any way you want.  The cell can contain 1/18/2010 but be formatted to appear as "mmmm d, yyyy" and you can still check if it's in a range.

If your "dates" are actually just text strings that Excel doesn't recognize as dates, it gets a little more complicated.

----------


## joeljoel

Paul, I appreciate the help.  

here's my formula  =AND(A6>=10/1/2014,A6<=3/31/2016)*1
and it doesn't seem to be working.  Any idea what I'm doing wrong?

Thanks!!

----------


## Paul

In your formula, it is seeing '10/1/2014' as 10 divided by 1 divided by 2014, not a date.  Try:

=AND(A6>=DATE(2014,10,1),A6<=DATE(2016,3,31))*1

----------


## joeljoel

Awesome, Paul.  That's the trick, thank you.  The one thing I just couldn't get right was formatting the date.  I have problems with this.   

Thanks!!

----------


## Quasar82

Hi,

I have 4 tasks that are done in an order. First task1, then task 2 so on. Each task takes certain number of days. 

I have calculated Start & End date for each task using MIN & MAX from a list dates Sheet 1(from the beginning of task 1 till the end of last task 4). 

For example 

Sheet 2

A ---------- B --------- C
(Task) (Start Date) (End Date)


Task 1  -- 1-Jan   --- 6-Jan 
Task 2  -- 7-Jan   -- 12-Jan
Task 3  -- 13-Jan  -- 14-Jan
Task 4  -- 15-Jan  -- 16-Jan

Sheet 3 

In this sheet I have a "column D" for Dates, starting from 1-Jan till 16-Jan (beginning of Task1 - end of task 4) D2:D17 
In the same sheet I have marked "W" for working day, "H" for holiday & "A" for Absent, "S"for Sunday in "column F"

D -------- E ------- F

1-Jan   Friday ------   W
2-Jan  Saturday ---  W
3-Jan   Sunday -----   S
4-Jan  Monday  -----   H
5-Jan  Tuesday ----   A
6-Jan  Wednesday - W
.
.
.
16-Jan Saturday --- W

Now, I want to calculate the number of working days ("W") between start & end date for each task. For Task1, it should first check if each cell in column D (D2:D17) is in between 1-Jan & 6-Jan, if yes, count "W" in column F.

----------


## FoxSeaLady

Excel 2016 has a function to calculate the number of working days between 2 dates , have you looked at this to meet your needs?

----------

