date calculations

nitecat

New Member
Joined
Mar 28, 2013
Messages
6
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,054
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
Sheet1

ABCD
1Due datecompleted date
231/03/201329/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
CellFormula
D2=IF(B2="","",IF(B2<A2,1,0))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

MGORIYA

New Member
Joined
Oct 7, 2010
Messages
33
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

New Member
Joined
Mar 28, 2013
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,054
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,133,536
Messages
5,659,376
Members
418,500
Latest member
Guru Prasad S

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top