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