Date formulas possible?

Board Regular
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?

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

SteveO59L

Well-known Member
Where are you holding the number of sick days taken ?

Board Regular
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

Well-known Member
=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.

Board Regular
will this formula reset annually back to default of 3?

And is this it? Could it be this simple?

SteveO59L

Well-known Member
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.

Board Regular
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?

SteveO59L

Well-known Member
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

Replies
1
Views
50
Replies
3
Views
252
Replies
1
Views
63
Replies
2
Views
487
Replies
4
Views
234

1,195,582
Messages
6,010,578
Members
441,557
Latest member
Jbest23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back