Hi i need some formula calculation.

i just want to use this formulas for payroll purpose.


For Eg. If Column B6 shows Start Date,(01/08/1980)


Column B7 Termination Date.(01/05/2008)


And if i want to find out Years of service in Column F8,


and Days pre 18/08/1993 in Column F9,


Days post 18/08/1993 in Column F10.

These will do what you asked for:

B6 = start date
B7 = end date
B8 = 18/8/1993

Years: =DATEDIF(B6,B7,"y")

Days *before* B8: =MAX(0,B8-B6)

Days *after* B8: =MAX(0,B7-B8)


--
Biff
Microsoft Excel MVP


"andy" <Nirmit.Desai@alphawest.com.au> wrote in message
news:97699072-319a-495c-a36c-3a261b51c11c@s21g2000prm.googlegroups.com...
> Hi i need some formula calculation.
>
> i just want to use this formulas for payroll purpose.
>
>
> For Eg. If Column B6 shows Start Date,(01/08/1980)
>
>
> Column B7 Termination Date.(01/05/2008)
>
>
> And if i want to find out Years of service in Column F8,
>
>
> and Days pre 18/08/1993 in Column F9,
>
>
> Days post 18/08/1993 in Column F10.
>
>
>
>

Thanks a lot

On May 27, 11:15*am, "T. Valko" <biffinp...@comcast.net> wrote:
> These will do what you asked for:
>
> B6 = start date
> B7 = end date
> B8 = 18/8/1993
>
> Years: =DATEDIF(B6,B7,"y")
>
> Days *before* B8: =MAX(0,B8-B6)
>
> Days *after* B8: =MAX(0,B7-B8)
>
> --
> Biff
> Microsoft Excel MVP
>
> "andy" <Nirmit.De...@alphawest.com.au> wrote in message
>
> news:97699072-319a-495c-a36c-3a261b51c11c@s21g2000prm.googlegroups.com...
>
>
>
> > Hi i need some formula calculation.
>
> > i just want to use this formulas for payroll purpose.
>
> > For Eg. If Column B6 * *shows Start Date,(01/08/1980)
>
> > Column B7 Termination Date.(01/05/2008)
>
> > And if i want to find out Years of service in Column F8,
>
> > and Days pre 18/08/1993 in Column F9,
>
> > Days post 18/08/1993 * * in Column F10.- Hide quoted text -
>
> - Show quoted text -

If the first day is 1 Jan and the last day is 2 Jan there are 2 day service
not 1 if you were to take one from other. Add an extra day in the formula.
This is a common trap

Bill Ridgeway
Computer Solutions

I was hesitant to suggest anything for that reason. The OP didn't specify
how that was to be handled.

Also note that they are completely excluding the "magic date", 18/8/1993.

Days before and days after *excludes* the actual date.

--
Biff
Microsoft Excel MVP


"Bill Ridgeway" <info@1001solutions.co.uk> wrote in message
news:umO2Pe%23vIHA.1936@TK2MSFTNGP04.phx.gbl...
> If the first day is 1 Jan and the last day is 2 Jan there are 2 day
> service not 1 if you were to take one from other. Add an extra day in the
> formula. This is a common trap
>
> Bill Ridgeway
> Computer Solutions
>