IF Formula with dates (98)

Topic

New Reply

Use the DATE function to to this: DATE(year,month,day) returns the date value for the given year, month and day.

=IF(A1=DATE(1998,1,1),80,0)

Reply | Quote

Replies

Edited by JLC on 26-Jun-02 15:25.

Let me add to the statement below… we need the formula to include several date RANGES in it. Our database is downloaded from ADP so all employees hire dates are already in the spreadsheet… the formula would read from this column. Please see the attachment for an example. Thanks!

We are using an Excel spreadsheet to calculate vacation hours based on years of employment. We would like to use the “IF” formula to determine how many vacation hours an employee would be granted. For example, if an employee started working here on January 01, 1998 we want to put 80 hours into the cell. The IF formula doesn’t seem to like the date format inside the formula… it sees 01/01/98 as formula itself and divides 01 by 01 by 98. Does anyone have any suggestions? Thanks!

Reply | Quote

Here’s two suggestions:

You can use this function to represent a date in your formula:
DATE(1998,1,1)

You could put the date in a cell and not in the formula. Do you have a column with the start date? Formulas can reference dates in cells without any problem and the same formula will work for every employee row. Typing each employee’s start date into an individual formula sounds like a lot of work!

Reply | Quote

Enter a table like this instead of the one you have now:

	Col A		ColB
12	Hired after	Vac Hrs
13	1/1/65		160 
14	1/1/88		120 
15	1/1/93		80 
16	1/1/01		40 
17	7/1/01		0 

Then use this formula to choose the number of vacation days. Cell I4 is the start date.

=VLOOKUP(I4,A$13:B$17,2,TRUE)

This means, lookup the start date in the table (A13:B17) and get the value in the second column. The “TRUE” parameter means, “If you can’t find an exact match use the highest value without being greater than the start date.” This is the same kind of lookup you’d use to figure out your tax rate on your income taxes. You lookup your income in a table that has cutoff points like this and pick the highest value without being greater than your income.

Put the dollar signs in the formula to prevent that range from “slipping” when you copy the formula down the column for other employees.

Reply | Quote

Thanks! Your formula worked.

Reply | Quote

JLC,

I would use a nested IF statement, much like is found in the Excel help explaining grading schedules.

Please see the attachment for an example of how to use it in your situation…I extended it out for a long range of dates to verify that it works like you wanted it to.

HTH

Reply | Quote

JLC
Please see the attachment .

Reply | Quote