1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Date Formula

Discussion in 'Business Applications' started by t long, Sep 13, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. t long

    t long Thread Starter

    Joined:
    Jan 19, 2004
    Messages:
    52
    In the military we offer our members what is known as Seperation Expense (SE) - When a member is called away on duty or course and is seperated from their family, they receive an allowance (S/E) of $11.50 per day.

    Here's where the formula would vary - To make it equal across the board sort of speak - Every month, no matter how many days is treated as a 30 day month. So the max amount a member can claim for monthly S/E is $345.00.

    Here are a few good examples :

    Case #1: Member starts his course and is entitled to S/E effective the 10th of January (which has 31 days) and does not take any leave, is he entitled to 21 or 22 days? 22 only at the start that we include all days.

    Case #2: Member comes in to pick up his S/E for Feb (which has 28 days) and he has taken 5 days leave for which he was reunited with his family. Should he receive 25 days (30 days less 5 days leave) or 23 days (28 Days less 5 days leave) of S/E? 25

    Case #3: Member comes in to pick up his S/E for December (which has 31 days) and was reunited with his family from the 21st of Dec till sometime in January. The member is entitled to S/E from 1- 20 Dec, is the formula to calculate his entitlement 30 days less 11 days leave for Dec for an entitlement of 19 days of S/E, or is the member simply entitled to 20 days of S/E for the period of 1 - 20 Dec? member gets 20 days
    Case #4: Member comes in to pick up his S/E for Mar (which has 31 days) and tells you that he was on TD (nowhere near his family) and received incidentals from the 27th - 31st Mar. Is he entitled to 26 days of S/E (1st -26th) or 25 days of S/E (30 days less 5 days of incidentals)? and; 26

    Case #5: Member comes in to pick up his S/E for Mar (which has 31 days) and tells you that he was on TD (nowhere near his family) and received incidentals from the 10th - 15th Mar. Is he entitled to 24 days of S/E ( 30 days less 6 days of incidentals) or 25 days of S/E (31 days less 6 days of incidentals).24

    Can you help me with a formula that will determine these amounts based on wheather or not the month has 31 days or not?

    Thank you for any help
    Tanya Long
     
  2. TonyJollans

    TonyJollans

    Joined:
    Jun 24, 2004
    Messages:
    114
    Hi Tanya,

    I don't know how you are going to input your data so I took a stab at it.

    In Column A: The date of the start of entitlement for the month
    In Column B: The date of the end of entitlement for the month
    In Column C: The number of days of non-entitlement between A & B
    In Column D: This formula:

    =IF(AND(A1=DATE(YEAR(A1),MONTH(A1),1),B1=DATE(YEAR(B1),MONTH(B1)+1,0)),30,B1-A1+1)-C1

    This gave me the results you quote for your examples and seems to follow the logic.

    Code:
    01/10/2003    01/31/2003    (blank)    22
    02/01/2003    02/28/2003       5       25
    12/01/2003    12/20/2003    (blank)    20
    03/01/2003    03/26/2003    (blank)    26
    03/01/2003    03/31/2003       6       24
    My big question is whether you will actually enter your data this way
     
  3. t long

    t long Thread Starter

    Joined:
    Jan 19, 2004
    Messages:
    52
    I get the "22" Ok in cell D1 - as shown above. But what formula do I put in cell C1 to get the number of days of non entitlement ?

    Thanks
     
  4. TonyJollans

    TonyJollans

    Joined:
    Jun 24, 2004
    Messages:
    114
    That was my question to you really :)

    What information do you have? Some of your examples quote dates and others just numbers of days. If you always have dates you may be able to do it with two columns for start and end date of non-entitlement - but what if there were two periods of non-entitlement in the same month?

    If you come back with full details of what you may have I can try and put something together.
     
  5. t long

    t long Thread Starter

    Joined:
    Jan 19, 2004
    Messages:
    52
    Can I send you the Excel form that we use to fill out our seperation expense?
     
  6. TonyJollans

    TonyJollans

    Joined:
    Jun 24, 2004
    Messages:
    114
    OK - I don't have a lot of time in the next 2 or 3 hours but I will take a look for you later on

    TonyJollans at VBAExpress dot com
     
  7. t long

    t long Thread Starter

    Joined:
    Jan 19, 2004
    Messages:
    52
    do you have a direct email I can send the attachment to - or can I attach it right here int he thread?
     
  8. TonyJollans

    TonyJollans

    Joined:
    Jun 24, 2004
    Messages:
    114
    My address was in my last post - or you can attach it here.

    Click on the Manage Attachments Button in the Additional Options section below the (Advanced) Input Box.
     
  9. TonyJollans

    TonyJollans

    Joined:
    Jun 24, 2004
    Messages:
    114
    Hi Tanya,

    Sorry for the delay.


    So that all who see this understand (and to be sure that I do):

    • You have a Period Start Date and End Date in Cells B21 and D21 respectively.
    • You have space for up to 8 periods within that to be excluded from the claim (Start Dates in Cells B26 to B29 and corresponding End Dates ib Cells D26 to D29, and also Start Dates in Cells G26 to G29 and corresponding End Dates ib Cells I26 to I29)

    And (my interpretation of) the rules are:

    • First, find the difference between the Period Start and Period End
    • IF the period start is the first of the month AND the period end is the last of the month AND neither the first nor the last of the month is in any period of exclusion then treat the difference (above) as 30 days regardless of how many days are actually in the month
    • Subtract from the above the sum of the all the differences between the Start and corresponding End of each period of exclusion

    I have assumed that all the dates are in the same month - the formula will not work correctly if they are not, and the formula does this:

    • IF B21 is the start of the Month of the Date in B21
    • AND D21 is the end of the Month of the Date in D21
    • AND The earliest date in any of (B26 to B29 and G26 to G29) is NOT the start of the Month of the Date in B21
    • AND The latest date in any of (D26 to D29 and I26 to I29) is NOT the end of the Month of the Date in D21
    • THEN The effective difference is 30 Days
    • ELSE The effective difference is D21-B21+1 (i.e. the real number of Days)
    • NOW If B26 is non-blank add up (D26-B26+1)
    • AND If B27 is non-blank add up (D27-B27+1)
    • AND the same for B28, B29, G26 to G29
    • FINALLy Take away the second total just calculated from the first total

    The Formula for this is:

    =IF(AND(B21=DATE(YEAR(B21),MONTH(B21),1),D21=DATE(YEAR(D21),MONTH(D21)+1,0),MIN(B26:B29,G26:G29)<>DATE(YEAR(B21),MONTH(B21),1),MAX(D26:D29,I26:I29)<>DATE(YEAR(D21),MONTH(D21)+1,0)),30,D21-B21+1)-SUM(D26:D29-B26:B29+(B26:B29<>"")+I26:I29-G26:G29+(G26:G29<>""))

    Which needs to be ARRAY ENTERED (Enter it with CTRL+SHIFT+ENTER)

    I have tested it with all your examples and have sent you back a copy of the Workbook with the formula entered.

    Note that I have made no attempt to validate the dates - they are all assumed to be valid and in the same month and not to include overlapping date ranges.

    Let us know if it does what you want.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/273388

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice