Formula to Calculate DATE differences

[ExcelDumb]

Hi All,

I need some help in excel with a csv file that I am working with. In one column it is "Date Expected" in the other column it is "Date Completed". The dates in these columns are given in a format with a timestamp

E.g.

08/03/06 11:43 am

I want to create a formula so that if the Date Completed is equal to or less than the Date expected then give a value of 100 otherwise give a value of 0. I have tried a few formulas but they do not seem to work. I must also add that I do not care about the time in the column I only care if the actual month-day-year is met.

My examples are (assume Date expected is column E and Date Completed is Column F)


---------------------
=IF(F2<(E2),100,0)

This does not work because if my date completed was 08/03/06 11:00pm and my date due was 08/03/06 1:00am it thinks it does not meet since 11pm is after 1am.
----------------------------


=IF(DAY(F1)<=DAY(E1),100,0)

This does not work because if my date completed was 07/31/2006 and date expected was 08/01/2006 since 01 is less than 31 it gives a false negative....It only considers the day and nothing else.


------------------------------

=IF(DATE(MONTH(F2), DAY(F2), YEAR(F2))<= DATE(MONTH(E2), DAY(E2), YEAR(F2)),100,0)

Once again if my date completed is 07/31/2006 11:00:00 PM and my date expected is 08/03/06 1:00 am it gives a false negative. In this case I do not know why it does not work.
------------------------------

Can anyone help me figure this out?

Thanks in advance.

 

[Chas17]

The CSV (*.csv) file format saves only the text and values as they are displayed in cells of the active worksheet. All rows and all characters in each cell are saved. Columns of data are separated by commas, and each row of data ends in a carriage return. If a cell contains a comma, the cell contents are enclosed in double quotation marks.

If cells display formulas instead of formula values, the formulas are converted as text. All formatting, graphics, objects, and other worksheet contents are lost. The euro symbol will be converted to a question mark.

 

[Peter_SSs]

ExcelDumb

Welcome to the Mr Excel board!

If you select E2 or F2 and go Format|Cells and click the number tab, what information about the 'Category' do you get?

 

[JohnnyTightlips]

it's a bit cumbersome but this should work:

=IF(MONTH(F2)=MONTH(E2),IF(DAY(F2)<=DAY(E2),100,0),IF(MONTH(F2)

<MONTH(E2),100,0))

Good luck!

EDIT: Does anyone know why I can't paste the formula in it's entirety? Very weird!! the end of the formula should have "Less than" MONTH(E2),100,0))

the less than sign seems to be stuffing things up

 

[Peter_SSs]

If you have actual dates in the cells, then try this. G2 (copied down):
=(INT(F2)<=INT(E2))*100
Format column G as General.

Mr Excel.xls
	D	E	F	G	H
1		Date Expected	Date Completed	Result
2		23/09/2006 9:20	23/08/2006 9:20	100
3		27/11/2005 9:20	28/11/2005 11:44	0
4		15/09/2006 5:23	15/09/2006 2:55	100
5
Sheet1

 

[barry houdini]

Does anyone know why I can't paste the formula in it's entirety? Very weird!! the end of the formula should have "Less than" MONTH(E2),100,0))

the less than sign seems to be stuffing things up

Hi johnny

The board sometimes interprets < and > signs as HTML tags, either use a space like this

=IF(MONTH(F2)=MONTH(E2),IF(DAY(F2)<=DAY(E2),100,0),IF(MONTH(F2)< MONTH(E2),100,0))

...or code tags like this

=IF(MONTH(F2)=MONTH(E2),IF(DAY(F2)<=DAY(E2),100,0),IF(MONTH(F2)<MONTH(E2),100,0))

 

[JohnnyTightlips]

Cheers Barry

 

[ExcelDumb]

Thanks

Hi All,

Thanks for the welcome and the prompt reply....I will try the recommendations and let you know.

Cheers!