date calculations

[nitecat]

How do I calculate date completed late =1

for example

due date = 01/03/2012
date completed = 28/02/2012
　
IF True return 1, IF false return 0 and ignore IF date completed cell is blank
I've tried this but only works if the months are the same and does not work if the month completed is earlier than the due date month

=SUMPRODUCT((DATE COMPLETED>DUE DATE)*(DATE COMPELETED<>"N/A")))

 

[Rick Rothstein]

How do I calculate date completed late =1

for example

due date = 01/03/2012
date completed = 28/02/2012
　
IF True return 1, IF false return 0 and ignore IF date completed cell is blank
I've tried this but only works if the months are the same and does not work if the month completed is earlier than the due date month

=SUMPRODUCT((DATE COMPLETED>DUE DATE)*(DATE COMPELETED<>"N/A")))

I think you may be asking for something like this...

=IF(DATE COMPLETED="","",1*(DATE COMPLETED>DATE DUE))

 

[Dryver14]

Sheet1

	A	B	C	D
1	Due date	completed date
2	31/03/2013	29/03/2013		1

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D2 =IF(B2="","",IF(B2<A2,1,0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[MGORIYA]

A B C
Due Date Completion Date
1 01/03/2013 28/02/2013 1

2 31/03/2013 05/04/2013 0

3 31/03/2013 Blank


C1 = if($B1<=$A1, "1",if($B1>$A1,"0",""))

 

[nitecat]

Thanks guys, </SPAN>

The first and third replies worked in excel date format only
but doesn't seem to work when you import external data via edit query (sql) and return data to MS office excel.</SPAN>


Do I need to reformat the date in SQL MS query to something other than TO_CHAR(xxxDATE,'DD/MM/YYYY')?</SPAN>

 

[Rick Rothstein]

Thanks guys, </SPAN>

The first and third replies worked in excel date format only
but doesn't seem to work when you import external data via edit query (sql) and return data to MS office excel.</SPAN>

Do I need to reformat the date in SQL MS query to something other than TO_CHAR(xxxDATE,'DD/MM/YYYY')?</SPAN>

It sounds like your "dates" are actually text strings that just look like dates. If that is the case, then this variation on my previously posted formula should work for you...

=IF(DateCompleted="","",1*(1*DateCompleted>1*DateDue))

Just to make that clearer, assume A1 contains DateDue and B1 contains DateCompleted...

=IF(B1="","",1*(1*B1>1*A1))

 

[nitecat]

Many Thanks Rick - it worked