Excel help

June 23, 2009 at 14:51:32
Specs: Windows XP
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)

See More: Excel help

Report •


#1
June 23, 2009 at 15:31:42
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

http://www.skeptic.com/


Report •

#2
June 24, 2009 at 05:12:18
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.

Report •

#3
June 24, 2009 at 08:16:47
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

http://www.skeptic.com/


Report •

Related Solutions

#4
June 24, 2009 at 08:22:46
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,

Report •

#5
June 24, 2009 at 08:55:19
track of vacation, sick hours

How are you tacking the Vacation time?
Are employee's given X many days per 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

http://www.skeptic.com/


Report •

#6
June 24, 2009 at 09:06:38
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

Report •

#7
June 24, 2009 at 09:43:11
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 cells have 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

http://www.skeptic.com/


Report •

#8
June 24, 2009 at 11:21:05
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.

Report •

#9
June 24, 2009 at 14:21:10
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 = Saturday

In 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 1

In 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

http://www.skeptic.com/


Report •

#10
June 24, 2009 at 14:26:49
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.

Report •

#11
June 24, 2009 at 15:11:30
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/2009

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

In 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

http://www.skeptic.com/


Report •

#12
June 24, 2009 at 15:16:18
I will try and find the site. i will paste all the formulas for the calendar if not.

Report •

#13
June 24, 2009 at 15:29:19
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


Report •

#14
June 25, 2009 at 07:01:59
http://office.microsoft.com/en-us/t...

thats is the link to the original calendar


Report •

#15
June 25, 2009 at 07:31:36
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

http://www.skeptic.com/


Report •


Ask Question