I want to construct a formula that when excel hits a certain date (like a employees anniversary date) it will reset certain fields(such as vacation time used and sick time)

It can be done using something like: If cell A1 is the anniversary date,

enter in cell B1 the formula:=DATEDIF(A1,TODAY(),"y")

Cell B1 now contains the number of years of employment.

Cell C1 is the Vacation Time, a formula like:

for 1 year 30 days

for 2 years 60 days=IF(B1=1,30,IF(B1=2,60,))

This is only an example of what you can do.

With more info .................MIKE

mike thanks for the post, my spreadsheet keeps track of vacation, sick hours used for the whole year the problem is if the employees anniversary date falls in the middle of the year when it gets to the anniversary date I need the spreasheet to not count anything before the anniversary date and start tracking them all over from the anniversary date. let me know if you need more info. thanks again for the help.

I understand you want to zero out the numbers and start counting over again at the anniversary date. A bit more information would be helpful.

How do you have the sheet set up?

What cells have which info? etc.etc.

What other formulas/macros are involved, if any.MIKE

If you would like i could email the spreadsheet there are no macros involved. it is set up the whole year on the spreadsheet and it autopopulates the the calendar based on the year. each subject being tracked is assigned with a abbreviation that a countif formula tracks for the entire year,

track of vacation, sick hoursHow are you tacking the Vacation time?

Are employee's given X manydaysper year?

or do they accumulate so many days per month?You are tracking Sick Hours not days?

Same question as above, are they given X many days....Explain in a bit more detail how your spreadsheet is set up?

MIKE

yes they are given a certain amount based on senority and it is given out yearly and not accrued over the course of the year the way i am tracking it is by hour . vacation and sick hours both can be used in a 4,5,6,8,10 or 12 hour increment. and these are selectable in a drop down box next to the date of the occurance

What cells have which info? Giving general info is helpful in understanding what you are doing but we need more specific details.

Explain, in detail, what

cellshave what information and how you are using that information.Do you use a =SUM formula someplace? How and in what cells?

I know you are using a =COUNTIF someplace, where and how?

What formulas have you tried to solve your problem?

MIKE

the calendar was a template and i started building around. I am somewhat literate at excel but I have a hard time with dates for some reason. in cell R14 is the year in cell C1 is =concatenate ("January","-",R14) in cell C2 is =R14&"/1/1" in C3 is =IF(weekday($c$2)=1,1,0) in E3 os =IF(weekday($c$2)=5,1,0) in F2=c2+31 (for february) in H2 =IF(or((and(mod(year(f2),4)=0,mod(year(f2),100)<>0)),(mod(year(f2),400)=0)),29,28)

in I2=F2+H2 and so on..... the actual calendar starts in B16 the formula there is =IF($c$3=1,1,0) in B17=IF($l$3=1,1,IF(B16>0,B16+1,0)) and follows that trend for everday of the month. In C16 is where i would have my drop down list for the month of JAN. in D16 is where i keep track of FMLA hours in E16 is where FEB would start the formula is =IF($h$3=1,1,0)

i use a countif formula to tally everything that happens in that month =countif(c16:c54),"this is where abbreviation code is" that would total for Jan. at the top of the spread sheet is where it tally's for the entire year c16+f16+ etc+, etc. i havent used a =SUM formula anywhere but what I have tried so far is

in Y13 in put the month and day of the anniversary and i tried

in B138 =IF today()<y13,"yes","no" and in B131 is =IF today() =y13,"yes","no" i have tried a AND formula in C144=AND(B131="no"B138="no") it the used =IF(C144,0+G144,G144-C142) i used that in c142 which created a circular reference (G144 is where i total my vacation hours.) it somewhat works.

I’ve entered your info into a blank sheet and have several questions: Is this a finished product, IE your actually using it,

or is this still in the creation stage?

In C3 you use the formula=IF(weekday($c$2)=1,1,0)

Which checks the Date expressed in C2 to see if it’s a Sunday. Why?

In E3 you use the same formula to check to see if it’s Thursday. Why?=WEEKDAY(Date, Type)

With no Type, it defaults to Type 1, where

1 = Sunday

2 = Monday

3 = Tuesday

4 = Wednesday

5 = Thursday

6 = Friday

7 = SaturdayIn F2 you simply calculate the Date for the first of Febuary by including the number of days in the month within the formula, but to calculate the first of March, you actually do the math to see if you need 28 or 29 days and display that number in cell H2. Is there a reason?

and so on..... the actual calendar starts

I don’t understand what you mean by this.

Does it mean that each succeeding column is the First of the Next Month, like:

J2 = April 1

K2 = May 1

L2 = June 1In B16 you have the WEEKDAY formula again.

In B17 is the formula =IF($l$3=1,1,IF(B16>0,B16+1,0))

It’s checking for something in I3, but I have nothing in I3?Also what do you mean by:

and follows that trend for everday of the month

What days of the Month?

Where are they supposed to be?

As you can probably tell, I’m a bit confused……MIKE

me too. I didnt know how much info you need in the calendar set up. like i said the calendar is a template i just made it adjustments to fit my needs i would be more than happy to write all the formulas out for the calendar but it is alot of info. this spreadsheet is not actually in use, I am just trying to create it so it can be put to use.

in Y13 in put the month and day of the anniversary and i tried

in B138 =IF today()<y13,"yes","no" and

in B131 is =IF today() =y13,"yes","no"That won’t work, try this:

In cell A1 enter Today’s Date

06/24/2009In cell B1 enter the Anniversary Date

06/24/2007In cell C1 enter the Formula

=IF(MONTH(B1)&DAY(B1)=MONTH(A1)&DAY(A1),"Anniversary Day","Not Anniversary Day")

This will check the Month & Day of Today’s date against the Month & Day of the Anniversary Date.

Where did you get the template from?

Someplace where I could download it from?MIKE

I will try and find the site. i will paste all the formulas for the calendar if not.

C

1=CONCATENATE("January","-",R14)

2= R14&"/1/1"

3=IF(WEEKDAY($C$2)=1,1,0)

4=IF(WEEKDAY($L$2)=1,1,0)

5=IF(WEEKDAY($U$2)=1,1,0)

6=IF(WEEKDAY($AD$2)=1,1,0)E

3=IF(WEEKDAY($C$2)=5,1,0)

4=IF(WEEKDAY($L$2)=5,1,0)

5=IF(WEEKDAY($U$2)=5,1,0)

6=IF(WEEKDAY($AD$2)=5,1,0)F

1=F2

2=C2+31

3=IF(WEEKDAY($F$2)=2,1,0)

4=IF(WEEKDAY($O$2)=2,1,0)

5=IF(WEEKDAY($X$2)=2,1,0)

6=IF(WEEKDAY($AG$2)=2,1,0)

H

2=IF(OR((AND(MOD(YEAR(F2),4)=0,MOD(YEAR(F2),100)<>0)), (MOD(YEAR(F2),400)=0)), 29,28)

3=IF(WEEKDAY($F$2)=1,1,0)

4=IF(WEEKDAY($O$2)=1,1,0)

5 =IF(WEEKDAY($X$2)=1,1,0)

6=IF(WEEKDAY($AG$2)=1,1,0)I

1=I2

2=F2+H2

3=IF(WEEKDAY($I$2)=1,1,0)

4=IF(WEEKDAY($R$2)=1,1,0)

5=IF(WEEKDAY($AA$2)=1,1,0)

=IF(WEEKDAY($AJ$2)=1,1,0)K

3=IF(WEEKDAY($I$2)=5,1,0)

4=IF(WEEKDAY($R$2)=5,1,0)

5=IF(WEEKDAY($AA$2)=5,1,0)

6=IF(WEEKDAY($AJ$2)=5,1,0)L

1=L2

2=I2+31

3=IF(WEEKDAY($C$2)=2,1,0)

4=IF(WEEKDAY($L$2)=2,1,0)

5=IF(WEEKDAY($U$2)=2,1,0)

6=IF(WEEKDAY($AD$2)=2,1,0)

N

3=IF(WEEKDAY($C$2)=6,1,0)

4=IF(WEEKDAY($L$2)=6,1,0)

5=IF(WEEKDAY($U$2)=6,1,0)

6=IF(WEEKDAY($AD$2)=6,1,0)

O

1=O2

2=L2+30

3=IF(WEEKDAY($F$2)=3,1,0)

4=IF(WEEKDAY($O$2)=3,1,0)

5=IF(WEEKDAY($X$2)=3,1,0)

6=IF(WEEKDAY($AG$2)=3,1,0)Q

3=IF(WEEKDAY($F$2)=6,1,0)

4=IF(WEEKDAY($O$2)=6,1,0)

5=IF(WEEKDAY($X$2)=6,1,0)

6=IF(WEEKDAY($AG$2)=6,1,0)R

1=R2

2=O2+31

3=IF(WEEKDAY($I$2)=2,1,0)

4=IF(WEEKDAY($R$2)=2,1,0)

5=IF(WEEKDAY($AA$2)=2,1,0)

6=IF(WEEKDAY($AJ$2)=2,1,0)i will post more tomorrow but that goes until june

After looking at the copy you emailed,

I think you are going to need some type of VBA code to zero out all your numbers.Unfortunately my VBA coding skills are limited, at best,

but perhaps DerbyDad03 might be able to suggest something.Sorry, I can't help more.

MIKE

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History