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.

Dates are off - Please Help ! - I'm not an expert.

Discussion in 'Business Applications' started by t long, Jan 21, 2007.

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

    t long Thread Starter

    Joined:
    Jan 19, 2004
    Messages:
    52
    My name is Cpl TL Long and I am trying to create a spreadsheet that will calculate the amt of days we are entitled to when we leave Afghanistan.

    I've gone over the formulas and I cant wrap my brain around the reason why sometimes it's off a day with the END DATE

    Here' the explanation of the Leave Calculator I have made and if anyone can help me here is an explanation as to how it works or how it SHOULD work

    Depending on how many days we spend overseas depends on how much leave we get when we return back to Canada.

    So we enter our Arrival date in E9 and our Departure Date in E12 giving me a Total Mission days in cell E15

    If we are here (in Afghanistan) 120 days or more we are entitled to HLTA (home leave) We get to travel home during the tour for a short rest period and them return. This HLTA or SML (special mission leave as we call it can be shown in the first chart (orange on the right) and I have made a separate Tab for it to do a +Lookup (The tab is called Extra) If you earn let's say 18 days and you use 18 days you have none left - If you earn 18 days and you only use 16 you have two left (which will be carried on in cell E21- If you earn 18 and use 21 you have nothing left.

    Departure from KAF (Kandahar Air Field) cell is linked to the Departure Date

    If you are here 120 + days you are entitled to 3 days Decompression (wind down time before going home in Cyprus)

    Depart for Canada
    Arrive Canada - both self explanatory
    Admin Day - the workday following the day after you arrive in Canada

    3 half days - If you are here 30 days or more you are given 3 half days to go into work and pass in your kit, turn in your weapon and ammo etc (if not you do it on your admin day)

    Leave Pass start - this will be the next working day after your 3 half days

    Then the leave entitlements are broken down

    1. Special Relocation (pink) - Seen in the chart ( +lookup in the "Extra" tab worksheet)
    2. SML (Special Mission Leave) this is the leave carried over from above in cell E21
    3. Annual will be manually input - It is leave we have left over from before the tour (we get 25 days per year - so everyone will be different)

    4. Short - 1 day per every 90 days on tour - also +lookup on the "Extra" tab

    5. Stat - will also be input manually for any holidays or extra granted by the CO.

    6. CDS Spec - 1 day for every 60 days to a maximum of 3 days - also +lookup on the "Extra" tab

    So I add up the entitlement at the bottom of 1 thru 6 and add that to the start date (including the start date as the first day - So if the start date was 18 Jan and the total entitlement was 3 then the end date would be 22 Jan 07. Then I want it to give me the weekends (if the start date is lets say 08 Jan then I want it to count the Sat and Sun before it and if it ends on 12 Jan the same thing, counting the Sat and Sunday for a total of 9 days.

    I hope I have explained what I need the spread sheet to do and if you can help feel free to make changes if necessary .

    I've tried to attach a copy of my spreadsheet but I dont know if it worked - Please send me an email and I will fwd it to you. The password to unprotect the sheet is gerald1

    Please Help!
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    T.L. hello and welcome.
    Did you use the "Go Advanced" Menu to "Upload" the Workbook?
    If it too large to meet the Excel upload limits you could Zip it first.
    It would obviously be much easier if we had the workbook to work with.
     
  3. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    OK I have managed to have a look.

    2 areas that I would like to look at.

    Cells E34 to E36 you say are half days, but your days are incremented by 1 day

    So in your example
    E33 = 29th Jan
    E34 = 30th Jan
    E35 = 31st Jan
    E36 = 1st feb

    is that correct bearing in mind you said half days?

    If that is correct then the calculations to that point seem to be OK

    All other calculations after that appear to be OK

    Which then leaves the formula in E45
    =IF((WEEKDAY(E37,2)>5), WORKDAY(E37,I62),WORKDAY(E37,(I62-1)))

    Where I62 = in your example 12

    E37 = 2 Feb 2007 which falls on a Friday
    WEEKDAY(E37,2) ,2 means treat monday as 1, Tuesday as 2 and so on until Sunday 7
    So the If query should return 5


    OK so if we look at your query in full it is If the weekday in E37 is greater than 5, then take the date in E37 and add the working days of I62 to it, otherwise take the date of E37 and add the working days of I62 less 1 day, to it

    So the result should be 2nd Feb plus 11 working days which = 19th Feb which is corrrect in your worksheet..

    So can you confirm that the first issue is OK

    Can you also confirm that the formula in E45 does do what you wanted it to do
     
  4. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Just to update on this.

    Having checked the workbook through, it looks as though it was due to the old problem of formatting to 0 decimal places diguises calculations that result in fractions. t Long needed to do a roundup function on one of the cells,and this appears to have sorted the problem.

    The result of not rounding up a calculation meant that in the right circumstances the final result was a day out.

    t Long was testing out the spreadsheet yesterday and things seem to be giving the correct results.
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Nice one Villan (y)
     
  6. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Thanks OBP :)

    I always think the hardest part of auditing somebody else's workbooks, is understanding exactly what the creator is trying to do and understanding their logic, especially if you can't see much wrong with the calculations. Still its always nice to think you may have helped somebody.
     
  7. 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/537079

  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