Complex Date Formula

[kermitforney]

Working on a sheet that I need date calculations formulas.

1.) Need it to calculate the difference between two dates.
(Pretty much have this one nailed)

2.) Once the calculation is made I need for it to give me a value back based
on the difference between the two dates.

Ex. Vacation accrues over time, let's say every six months you get six vacation days. I want to put a hiring date into one cell (6/1/05) and then have the calculation refer back to that cell to get the result. So based on the hire date (6/1/05) and the current date (7/18/06) I would get a result of
12 vacation days.

3.) I also need for thier to be a ceiling or a limit you could call it. So that it cannot go over maybe fifteen days.

I am currently working on it now but just thought I would post it and see what other options are out there. Thanks in advance guys and gals.

[Ragdyer]

Why wouldn't it be 13 days?

6/1/05 to 7/18/06 is 13 months plus!

Or, are you accruing vacation time in 6 day (month) increments?

If so, then where is the break point to arrive at your max of 15 days?

Need some more info.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"kermitforney" <[email protected]>
wrote in message
news:[email protected]...
>
> Working on a sheet that I need date calculations formulas.
>
> 1.) Need it to calculate the difference between two dates.
> (Pretty much have this one nailed)
>
> 2.) Once the calculation is made I need for it to give me a value back
> based
> on the difference between the two dates.
>
> Ex. Vacation accrues over time, let's say every six months you get six
> vacation days. I want to put a hiring date into one cell (6/1/05) and
> then have the calculation refer back to that cell to get the result. So
> based on the hire date (6/1/05) and the current date (7/18/06) I would
> get a result of
> 12 vacation days.
>
> 3.) I also need for thier to be a ceiling or a limit you could call it.
> So that it cannot go over maybe fifteen days.
>
> I am currently working on it now but just thought I would post it and
> see what other options are out there. Thanks in advance guys and gals.
>
>
>
> --
> kermitforney
> ------------------------------------------------------------------------
> kermitforney's Profile:
> http://www.excelforum.com/member.php...o&userid=32536
> View this thread: http://www.excelforum.com/showthread...hreadid=562681
>

[Ron Rosenfeld]

On Tue, 18 Jul 2006 19:32:42 -0400, kermitforney
<[email protected]> wrote:

>
>Working on a sheet that I need date calculations formulas.
>
>1.) Need it to calculate the difference between two dates.
>(Pretty much have this one nailed)
>
>2.) Once the calculation is made I need for it to give me a value back
>based
>on the difference between the two dates.
>
>Ex. Vacation accrues over time, let's say every six months you get six
>vacation days. I want to put a hiring date into one cell (6/1/05) and
>then have the calculation refer back to that cell to get the result. So
>based on the hire date (6/1/05) and the current date (7/18/06) I would
>get a result of
>12 vacation days.
>
>3.) I also need for thier to be a ceiling or a limit you could call it.
>So that it cannot go over maybe fifteen days.
>
>I am currently working on it now but just thought I would post it and
>see what other options are out there. Thanks in advance guys and gals.
>


=MIN(15,INT(DATEDIF(HireDate,CurrentDate,"m")/6)*6)


--ron