subtracting Dates

Could someone please help me with the below task.

A1 = 01-Jan-05 = The first day of the year.
B1 = 16-June 05 = Their starting date.
C1 = 10-Oct 05 = Their finishing date.

example I have a program for my staff when they resign to count how many days they have worked here this year and to make sure they have not take too many days holiday and in cell A1 I have 01-Jan-05 and in cell B1 I have their starting date for example 16-June-05 and in cell C1 I have their finishing date for example 10-Oct-05. Now I am trying to work out a formula in D1 to tell me how many days the employee has worked here this year, so what I want is a formula to count the days between cell C1 (their finishing date) and A1 but if B1 is greater then A1 I want the formula to count the number of days between C1 and B1. I have been trying to work this out now for sometime.

And many thanks in advance if you can help me. Paul

On Sun, 13 Nov 2005 05:59:19 -0600, paulrm906
<[email protected]> wrote:

>
>Could someone please help me with the below task.
>
>A1 = 01-Jan-05 = The first day of the year.
>B1 = 16-June 05 = Their starting date.
>C1 = 10-Oct 05 = Their finishing date.
>
>example I have a program for my staff when they resign to count how
>many days they have worked here this year and to make sure they have
>not take too many days holiday and in cell A1 I have 01-Jan-05 and in
>cell B1 I have their starting date for example 16-June-05 and in cell
>C1 I have their finishing date for example 10-Oct-05. Now I am trying
>to work out a formula in D1 to tell me how many days the employee has
>worked here this year, so what I want is a formula to count the days
>between cell C1 (their finishing date) and A1 but if B1 is greater then
>A1 I want the formula to count the number of days between C1 and B1. I
>have been trying to work this out now for sometime.
>
>And many thanks in advance if you can help me. Paul

=c1-max(a1,b1)

If you are interested in counting only weekdays that are not also holidays
than:

=networkdays(max(a1,b1),c1,holidays)

where holidays is a range containing a list of holiday dates for the year.

You'll need to have the analysis tool pack installed for the networkdays
function. See HELP for that function if it gives you a NAME error.


--ron

See response in public.excel

--

HTH

RP
(remove nothere from the email address if mailing direct)


"paulrm906" <[email protected]> wrote
in message news:[email protected]...
>
> Could someone please help me with the below task.
>
> A1 = 01-Jan-05 = The first day of the year.
> B1 = 16-June 05 = Their starting date.
> C1 = 10-Oct 05 = Their finishing date.
>
> example I have a program for my staff when they resign to count how
> many days they have worked here this year and to make sure they have
> not take too many days holiday and in cell A1 I have 01-Jan-05 and in
> cell B1 I have their starting date for example 16-June-05 and in cell
> C1 I have their finishing date for example 10-Oct-05. Now I am trying
> to work out a formula in D1 to tell me how many days the employee has
> worked here this year, so what I want is a formula to count the days
> between cell C1 (their finishing date) and A1 but if B1 is greater then
> A1 I want the formula to count the number of days between C1 and B1. I
> have been trying to work this out now for sometime.
>
> And many thanks in advance if you can help me. Paul
>
>
> --
> paulrm906
> ------------------------------------------------------------------------
> paulrm906's Profile:
http://www.excelforum.com/member.php...o&userid=28776
> View this thread: http://www.excelforum.com/showthread...hreadid=484644
>

Originally Posted by Ron Rosenfeld

On Sun, 13 Nov 2005 05:59:19 -0600, paulrm906
<[email protected]> wrote:

>
>Could someone please help me with the below task.
>
>A1 = 01-Jan-05 = The first day of the year.
>B1 = 16-June 05 = Their starting date.
>C1 = 10-Oct 05 = Their finishing date.
>
>example I have a program for my staff when they resign to count how
>many days they have worked here this year and to make sure they have
>not take too many days holiday and in cell A1 I have 01-Jan-05 and in
>cell B1 I have their starting date for example 16-June-05 and in cell
>C1 I have their finishing date for example 10-Oct-05. Now I am trying
>to work out a formula in D1 to tell me how many days the employee has
>worked here this year, so what I want is a formula to count the days
>between cell C1 (their finishing date) and A1 but if B1 is greater then
>A1 I want the formula to count the number of days between C1 and B1. I
>have been trying to work this out now for sometime.
>
>And many thanks in advance if you can help me. Paul

=c1-max(a1,b1)

If you are interested in counting only weekdays that are not also holidays
than:

=networkdays(max(a1,b1),c1,holidays)

where holidays is a range containing a list of holiday dates for the year.

You'll need to have the analysis tool pack installed for the networkdays
function. See HELP for that function if it gives you a NAME error.


--ron

Thanks Ron for the very speedy response I will try this tomorrow when I am back behind the desk. Paul