 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008Date problem
This small equation is kicking my butt. Could someone enlighten me. I am simply trying to compare 2 dates if the date in cell c2 is less that "12/31/08" then put the value , or else put " future " This is what I came up with =if(c2<12/31/08, c2,"future") c2 is in the form of 5/17/08 How do I format the comparison date? I have tried it a few ways, with no luck. Appreciate any help  SignatureKWB You could use: =IF(C2<DATEVALUE("12/31/08"),C2,"future") or =IF(C2<DATE(2008,12,31),C2,"future") Tyro > This small equation is kicking my butt. Could someone enlighten me. > [quoted text clipped - 8 lines] > > Appreciate any help The date() worked. Thanks for the tip. Bill I will give your suggestion a try as well Thanks for all the input, Very helpful. SignatureKWB > You could use: =IF(C2<DATEVALUE("12/31/08"),C2,"future") or > =IF(C2<DATE(2008,12,31),C2,"future") [quoted text clipped - 13 lines] > > > > Appreciate any help =IF(C2<39813, C2,"future") excel date system is based on numbers of days after 0 jan 1900 12/31/08 number of days can be found by formatting the date cell as a number 39813 the formula needs that number to be able to do a comparison. you probably want 39814 being the date for 1 jan 09. or have <=39813 for futher info on dates see http://www.cpearson.com/excel/datetime.htm SignatureGreetings from New Zealand > This small equation is kicking my butt. Could someone enlighten me. > [quoted text clipped - 8 lines] > > Appreciate any help "Bill Kuunders" <bill.kuund...@xtra.co.nz> wrote... >=IF(C2<39813, C2,"future") >excel date system is based on numbers of days after 0 jan 1900 [quoted text clipped - 3 lines] >you probably want 39814 being the date for 1 jan 09. >or have <=39813 ... Not exactly readable, and it's specific to the 1900 date system, so would fubar if the OP were using the 1904 date system. Using the DATE function, which other respondents did, is far more robust AND easier to read and understand. FTHOI, the OP's formula failed because Excel treats 12/31/08 as (12 divided by 31) divided by 8. Alternatives that are more readable, =IF("12/31/08"-C2>0, C2,"future") =IF(C2<DATEVALUE("12/31/08"), C2,"future") =IF(C2<VALUE("12/31/08"), C2,"future") That is, under standard formula evaluation, Excel will convert date strings into dates in arithmetic expressions, and it also provides functions that convert date strings into dates. One way: =IF(C2<DATE(2008,12,31),C2,"Future") If C2 is empty then the logical test will be TRUE and the result will be 0. This version is more robust: =IF(COUNT(C2),IF(C2<DATE(2008,12,31),C2,"Future"),"") SignatureBiff Microsoft Excel MVP > This small equation is kicking my butt. Could someone enlighten me. > [quoted text clipped - 8 lines] > > Appreciate any help Thanks T, again, thanks for all the helpful input. SignatureKWB > One way: > [quoted text clipped - 17 lines] > > > > Appreciate any help You're welcome! SignatureBiff Microsoft Excel MVP > Thanks T, > [quoted text clipped - 25 lines] >> > >> > Appreciate any help |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.