Formula to Calculate DATE differences

ExcelDumb

New Member
Joined
Sep 22, 2006
Messages
3
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,430
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 13, 2006
Messages
94
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,430
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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))
 

ExcelDumb

New Member
Joined
Sep 22, 2006
Messages
3
Thanks

Hi All,

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

Cheers!
 

Forum statistics

Threads
1,136,420
Messages
5,675,751
Members
419,585
Latest member
popsin

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