+ Reply to Thread
Results 1 to 4 of 4

subtracting Dates

  1. #1
    Registered User
    Join Date
    11-13-2005
    Location
    Moscow
    Posts
    41

    Smile 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

  2. #2
    Ron Rosenfeld
    Guest

    Re: subtracting Dates

    On Sun, 13 Nov 2005 05:59:19 -0600, paulrm906
    <paulrm906.1yfp1y_1131883200.6845@excelforum-nospam.com> 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

  3. #3
    Bob Phillips
    Guest

    Re: subtracting Dates

    See response in public.excel

    --

    HTH

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


    "paulrm906" <paulrm906.1yfp1y_1131883200.6845@excelforum-nospam.com> wrote
    in message news:paulrm906.1yfp1y_1131883200.6845@excelforum-nospam.com...
    >
    > 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
    >




  4. #4
    Registered User
    Join Date
    11-13-2005
    Location
    Moscow
    Posts
    41

    thankyou

    Quote Originally Posted by Ron Rosenfeld
    On Sun, 13 Nov 2005 05:59:19 -0600, paulrm906
    <paulrm906.1yfp1y_1131883200.6845@excelforum-nospam.com> 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1