Date formulas possible?

adavies

Board Regular
Joined
Nov 15, 2005
Messages
61
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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?"
 
Upvote 0
=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.
 
Upvote 0
will this formula reset annually back to default of 3?

And is this it? Could it be this simple?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top