Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Running date formula in excelsheet

Posted on 2014-03-30
5
Medium Priority
?
181 Views
Last Modified: 2014-04-01
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.

Look forward to your solution
0
Comment
Question by:Ozwazza
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 81

Expert Comment

by:byundt
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 81

Expert Comment

by:byundt
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

by:Ozwazza
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 81

Accepted Solution

by:
byundt earned 2000 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.

Brad
OneYearAnniversaryQ28401065.xlsx
0
 

Author Closing Comment

by:Ozwazza
ID: 39970897
Wonderful, cheers
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question