# Formula to Calculate DATE differences

#### ExcelDumb

##### New Member
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?

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.

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?

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

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
DEFGH
1Date ExpectedDate CompletedResult
223/09/2006 9:2023/08/2006 9:20100
327/11/2005 9:2028/11/2005 11:440
415/09/2006 5:2315/09/2006 2:55100
5
Sheet1

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

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

Cheers Barry

Thanks

Hi All,

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

Cheers!

Replies
1
Views
476
Replies
2
Views
528
Replies
2
Views
289
Replies
5
Views
860
Replies
2
Views
378

1,219,798
Messages
6,150,318
Members
450,951
Latest member
kh198

### 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.

### Which adblocker are you using?

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