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

This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

t long

Thread Starter
Jan 19, 2004
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!


Mar 8, 2005
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.
Feb 20, 2006
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
Feb 20, 2006
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.
Feb 20, 2006
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.
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online