Date formulas possible?

[adavies]

How would I put this into a formula

A1 = Name
B1 = Date started
C1 = total paid sick days remaining

an employee has 3 paid sick days per year from start date, but they have to worked within the company for at least 1 year to recieve this or else they get nothing. however every year on start date the number of sick days resets to 3.


Is this possibl;e to make into a formula?

 

[SteveO59L]

Where are you holding the number of sick days taken ?

 

[adavies]

These are paid sick days, after that they are unpaid sick days.

The company tracks sick days to assess reliability of the employee. If you're sick waaay to often then the question arises "are you fit to work in this business?"

 

[SteveO59L]

=IF(TODAY()>B1+365,3,0)

Will tell you if the employee has been with the company more than 1 year (365 days actually) and hence entitled to the 3 sick days.

 

[adavies]

ADVERTISEMENT

will this formula reset annually back to default of 3?

And is this it? Could it be this simple?

 

[SteveO59L]

The formula says that if you have been with the company more than 1 year, you are entitled to 3 sick days. Doesnt need to reset, but doesnt take into account how may sick days have already been taken, and hence how many remain, which I think you may be after.

 

[adavies]

Oh...

I think you're correct on the last post. Is there any way I can alter the formula to do what you just stated?

Again Thanks in Advance

 

[SteveO59L]

Ok, so once you have been with the company 1 year, you are entitled to 3 sick days, irrespective of how many years this is.


So the original formula

=IF(TODAY()>B1+365,3,0)

check for this.

The annualised number of sick days taken (assumed to be in d1) would be reset each year (irrespective of start date ?)

So to find the number of days remaining, something like

=if(IF(TODAY()>B1+365,3,0)-d1,<0,0,IF(TODAY()>B1+365,3,0)-d1))


HTH