subtracting Dates

[paulrm906]

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

 

[Ron Rosenfeld]

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

 

[Bob Phillips]

See response in public.excel

--

HTH

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

 

[paulrm906]

=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