Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Date
hi,
I received an Excel file with a formula inserted to compare a day with a date; if the day is more than or equal to 15, then the formula will return Yes; i think so. The formula is =IF(Day(A1)=DATE(2005,1,15),"Yes","No") & the date in A1 is 1/15/06 & the value returned is No. Actually, the answer sounds weird to me cause it should return Yes instead of No. Plus, I did try to change the sign in the IF function to < sign then the answer returned was Yes. So, what is all these mean? Is it possible to compare in this way or the formula is built up with logical error? Could anyone help? Thanking in advanced. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Date
The answer from your DAY() function is 15 (as it gives an answer from 1 to
31. The value of DATE(2005,1,15) is 36905. Look up the details of the functions, and of Excel's methods of expressing dates, in Excel's help. -- David Biddulph "Jac" wrote in message ... hi, I received an Excel file with a formula inserted to compare a day with a date; if the day is more than or equal to 15, then the formula will return Yes; i think so. The formula is =IF(Day(A1)=DATE(2005,1,15),"Yes","No") & the date in A1 is 1/15/06 & the value returned is No. Actually, the answer sounds weird to me cause it should return Yes instead of No. Plus, I did try to change the sign in the IF function to < sign then the answer returned was Yes. So, what is all these mean? Is it possible to compare in this way or the formula is built up with logical error? Could anyone help? Thanking in advanced. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Date
To explain the formula as given, just disect it.....
With 1/5/06 in A1, then =day(a1) will return 5. =date(2005,1,15) will return 1/15/2005, which Excel sees as the number 38367, so the logic follows that the value in A1 (ie 5), is not greater than or equal to 38367 so the formula correctly returns the result "NO". The logic is flawed, it is trying to compare a DAY to a DATE. Post back with exactly what you would like, and someone will construct the correct formula for you. hth Vaya con Dios, Chuck, CABGx3 "Jac" wrote: hi, I received an Excel file with a formula inserted to compare a day with a date; if the day is more than or equal to 15, then the formula will return Yes; i think so. The formula is =IF(Day(A1)=DATE(2005,1,15),"Yes","No") & the date in A1 is 1/15/06 & the value returned is No. Actually, the answer sounds weird to me cause it should return Yes instead of No. Plus, I did try to change the sign in the IF function to < sign then the answer returned was Yes. So, what is all these mean? Is it possible to compare in this way or the formula is built up with logical error? Could anyone help? Thanking in advanced. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Date
Ok, thanks, David! : )
"David Biddulph" wrote: The answer from your DAY() function is 15 (as it gives an answer from 1 to 31. The value of DATE(2005,1,15) is 36905. Look up the details of the functions, and of Excel's methods of expressing dates, in Excel's help. -- David Biddulph "Jac" wrote in message ... hi, I received an Excel file with a formula inserted to compare a day with a date; if the day is more than or equal to 15, then the formula will return Yes; i think so. The formula is =IF(Day(A1)=DATE(2005,1,15),"Yes","No") & the date in A1 is 1/15/06 & the value returned is No. Actually, the answer sounds weird to me cause it should return Yes instead of No. Plus, I did try to change the sign in the IF function to < sign then the answer returned was Yes. So, what is all these mean? Is it possible to compare in this way or the formula is built up with logical error? Could anyone help? Thanking in advanced. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Date
ok, get it!
Thanks for the explaination; anyway, i have got what I want already. : ) "CLR" wrote: To explain the formula as given, just disect it..... With 1/5/06 in A1, then =day(a1) will return 5. =date(2005,1,15) will return 1/15/2005, which Excel sees as the number 38367, so the logic follows that the value in A1 (ie 5), is not greater than or equal to 38367 so the formula correctly returns the result "NO". The logic is flawed, it is trying to compare a DAY to a DATE. Post back with exactly what you would like, and someone will construct the correct formula for you. hth Vaya con Dios, Chuck, CABGx3 "Jac" wrote: hi, I received an Excel file with a formula inserted to compare a day with a date; if the day is more than or equal to 15, then the formula will return Yes; i think so. The formula is =IF(Day(A1)=DATE(2005,1,15),"Yes","No") & the date in A1 is 1/15/06 & the value returned is No. Actually, the answer sounds weird to me cause it should return Yes instead of No. Plus, I did try to change the sign in the IF function to < sign then the answer returned was Yes. So, what is all these mean? Is it possible to compare in this way or the formula is built up with logical error? Could anyone help? Thanking in advanced. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
How to return a value between date ranges | Excel Worksheet Functions | |||
count between start date and end date | Excel Discussion (Misc queries) | |||
Determining Date X based on Other dates | Excel Worksheet Functions | |||
Another Date issue. | Excel Worksheet Functions |