Forum home » Delegate support and help forum » Microsoft Excel Training and help » If function with dates
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
If function with dates
Resolved · Urgent Priority · Version 2003
Reply Mon 24 Nov 2008, 14:18Delegate Lennon said...
Lennon has attended:
Excel Intermediate course
Excel Advanced course
Access Introduction course
If function with dates
I have a list of over 10000 items and would like to create a simple 1 and 0 if function in a neutral cell based on a date entered in a particular cell. For example
If date in D5 is greater than 31/12/08 then 1
If date in D5 is less than 31/12/08 then 0
For upcoming training course dates see: Pricing & availability
RE: If function with dates
Hi Lennon
Before working with your live data make a backup so you can revert to your information in the event of encountering any problems.
It sounds like one of the items in your list is a date.
You'd like to add a cell for each item on your list to show 1 or 0 depending on whether this date is earlier or later than the date in D5
To do this put your date in the D5 cell
then for each record try the following formula:
=IF(B1>D$1,1,0)
where B1 in the formula above is the cell in your item that contains the date to be compared.
I hope this helps - let me know if you have any further questions.
Kind regards,
Andrew
Reply Wed 26 Nov 2008, 10:43Delegate Lennon said...
RE: If function with dates
Thanks for the reply. I am not comparing two dates.
I have a staff list which states when all members of staff completed their mandatory training. This list has over 10000 names on it.
2. In columns A-D i have the staff's names, departments and salary details.
3. In E, I have the mandatory training date.
4. In F i would like to either have a 0 or 1 depending on the dates in E.
5. So if, mandatory training was completed on or after 25 Dec 2006 then I need F to say 1 for that individual and 0 otherwise.
Thanks
RE: If function with dates
Hi Lennon
Thanks for your reply. If the date you are testing is 25/12/2006 you could try modifying the formula to read:
=IF(B1>=DATEVALUE("25/12/2006"),1,0)
and see if that produces the results you are looking for.
This uses datevalue to convert the text version of a date into a number that Excel can use for comparing with values formatted as dates.
Let me know how you get on.
Kind regards,
Andrew
|