date calculations

nitecat

New Member
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")))

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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))

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>

 Cell Formula D2 =IF(B2="","",IF(B2

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

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",""))

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>

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))

Many Thanks Rick - it worked

Replies
3
Views
377
Replies
2
Views
179
Replies
0
Views
119
Replies
3
Views
276
Replies
0
Views
156

1,218,540
Messages
6,143,100
Members
450,463
Latest member
DaveG999

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back