Solved

# Running date formula in excelsheet

Posted on 2014-03-30
171 Views
Hello, I have a column that includes an employee start date (i.e. 1/4/2002). I was hoping to put in an allowance column, for each employee, to have a formula which automatically inserts their \$200 allowance when they have work a full year.

0
Question by:Ozwazza
• 3
• 2

LVL 80

Expert Comment

ID: 39965774
Consider a formula like the following:
=IF(DATEDIF(A1,TODAY(),"y")>=1,200,"")

It will return an empty string (looks like a blank) if the employee has worked less than one year. It will return 200 on their anniversary date and thereafter.
0

LVL 80

Expert Comment

ID: 39965779
DATEDIF is a "secret" function that has been part of Excel for a long time, but was only documented in Excel 2000. Even though it is undocumented, you can still use it for problems like this.

If you want to learn more about the DATEDIF function, Microsoft Excel MVP Chip Pearson has a good webpage on it: http://www.cpearson.com/excel/datedif.aspx
0

Author Comment

ID: 39968155
Hello excel gurus, the datedif function will not work because it is always >=1 if the year is >2013.  I can only write in pseudocode (showing my age!)

IF (start date year) <= (Year(today)-1) THEN
IF (start date month) >= (month(today)) THEN
IF (start date day) >= (day(today)) THEN
ALLOWANCE=200
END IF
END IF
ELSE
IF (start date month) >= (month(today) THEN
IF (start date day)>=(day(today)) THEN
ALLOWNCE=200
END IF
END IF
END IF

can someone give me a an excel formula for that?

Cheers
Warren
0

LVL 80

Accepted Solution

byundt earned 500 total points
ID: 39968476
Warren,
Your pseudocode results in the following formula:
=IF(YEAR(A1)<=(YEAR(TODAY())-1),IF(AND(MONTH(A1)>=MONTH(TODAY()),DAY(A1)>=DAY(TODAY())),200,""),IF(AND(MONTH(A1)>=MONTH(TODAY()),DAY(A1)>=DAY(TODAY())),200,""))

That formula returns \$200 if you are before your anniversary, and 0 afterwards. You asked for the opposite in your original question.

I still believe that DATEDIF is the right way to go, but the proof will be in testing with some sample data. Please see the attached workbook showing the two formulas in action.

If neither formula is working, please suggest some alternative test dates and give the desired results for those dates. That will surely be easier than debugging pseudocode.

OneYearAnniversaryQ28401065.xlsx
0

Author Closing Comment

ID: 39970897
Wonderful, cheers
0

## Featured Post

### Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…