By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,483 Members | 3,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,483 IT Pros & Developers. It's quick & easy.

Update as per excel calculations...

P: 83
I inherited a excel spreadsheet and cannot figure out the calculation in column L of main worksheet.

Function code in Column L
Expand|Select|Wrap|Line Numbers
  1. =IF(K3<>"N/A",IF(I3 = "CANCELLED", G3 +Q3, IF(OR(P3+1-G3>(I3+1-H3+Q3,P3=""),G3+Q3+S3,"N/A")),"N/A")
Explaination of the function is as below.

If K3 (newIncrement) is not N/A then

If I3 (ceaseDate) is CANCELLED then use the StartIncDate in G3 plus the number of days in Q (daysInYear - which in this case is 17-Jan-04 plus 366 days)

Else

If P3 (dischargeDate) plus 1 day minus G3 (StartIncDate) is greater than I3 (ceaseDate) plus 1 day minus H3 (commDate) plus the number of days in Q (daysInYear which in this case is plus 366 days)

......OR if P3 (dischargeDate) is blank

then

G3 (StartIncDate) plus the number of days in Q (daysInYear -which in this case is plus 366 days) + plus the number of days in S (countLeave - which in this case is Zero days)

Else N/A

The last NA simply means that the first line “If K3 (newIncrement) is not N/A” was in fact NA so don’t do anything of the calculations in the middle.

I want to replicate this in access. I want this in the Afterupdate_leaveType and the out put is effDate.

The "Or" is confusing me.

Attached are both the spreadsheet and a small database.

In Access the relative columns are.
Expand|Select|Wrap|Line Numbers
  1. G3 = StartIncDate
  2. H3 = commDate
  3. I3 = ceaseDate
  4. K3 = newIncrement
  5. L3 = effDate '<<<<< Want to calculate this 
  6. O3 = leaveType 
  7. P3 = dischargeDate
  8. Q3 = daysInYear
  9. S3 = countLeave
Thanks

Raghu Prabhu
Attached Files
File Type: zip IncExcel.zip (95.8 KB, 43 views)
May 24 '12 #1

✓ answered by NeoPa

Possible outcomes are :
  1. "N/A"
  2. G3 (StartIncDate) + Q3 (daysInYear)
  3. G3 (StartIncDate) + Q3 (daysInYear) + S3 (countLeave)
  1. If K3 (newIncrement) is "N/A" then A.
  2. ELSE If I3 (ceaseDate) is "CANCELLED" then B.
  3. If P3 (dischargeDate) - G3 (StartIncDate) > I3 (ceaseDate) + Q3 (daysInYear) - H3 (commDate)
    OR
    P3 has not been set
    then C.
  4. ELSE (which is equivalent to P3 is set and P3 - G3 <= I3 + Q3 - H3) A.

I hope that helps to clarify it for you.

Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Possible outcomes are :
  1. "N/A"
  2. G3 (StartIncDate) + Q3 (daysInYear)
  3. G3 (StartIncDate) + Q3 (daysInYear) + S3 (countLeave)
  1. If K3 (newIncrement) is "N/A" then A.
  2. ELSE If I3 (ceaseDate) is "CANCELLED" then B.
  3. If P3 (dischargeDate) - G3 (StartIncDate) > I3 (ceaseDate) + Q3 (daysInYear) - H3 (commDate)
    OR
    P3 has not been set
    then C.
  4. ELSE (which is equivalent to P3 is set and P3 - G3 <= I3 + Q3 - H3) A.

I hope that helps to clarify it for you.
May 24 '12 #2

P: 83
Thanks....Helps a lot.
May 24 '12 #3

NeoPa
Expert Mod 15k+
P: 31,494
Pleased to hear it :-)

I was impressed with the efforts obviously taken to give as much clarity to the question as possible.
May 24 '12 #4

P: 83
Hi NeoPa,

I have used the following code and it is not working. Could you please tell me what I am doing incorrectly.

Cheers

Expand|Select|Wrap|Line Numbers
  1.     If newIncrement = "N/A" Then
  2.         effDate = ""
  3.     ElseIf ceaseDate = "" Then
  4.             effDate = startIncDate + daysInYear
  5.     ElseIf (dischargeDate - startIncDate) > (ceaseDate - commDate + daysInYear) Then
  6.         effDate = startIncDate + daysInYear + countLeave
  7.     ElseIf dischargeDate = "" Then
  8.         effDate = startIncDate + daysInYear + countLeave
  9.     ElseIf dischargeDate <> "" And (dischargeDate - startIncDate) <= (ceaseDate - commDate + daysInYear) Then
  10.         effDate = ""
  11.     End If
May 26 '12 #5

NeoPa
Expert Mod 15k+
P: 31,494
If you tell me what you mean by "not working" I'll happily look at it for you. Details are not optional in situations such as these ;-)
May 26 '12 #6

P: 83
NeoPa,

finally got it working..

Expand|Select|Wrap|Line Numbers
  1.  
  2.     If newIncrement <> "N/A" Then
  3.         If Nz(ceaseDate, "") = "" Then
  4.             effDate = startIncDate + daysInYear
  5.         Else
  6.             If dischargeDate + 1 - startIncDate > (ceaseDate + 1 - commDate) + daysInYear Or Nz(dischargeDate, "") = "" Then
  7.                 effDate = startIncDate + daysInYear + countLeave
  8.             Else
  9.                 effDate = ""
  10.             End If
  11.         End If
  12.     Else
  13.         effDate = ""
  14.     End If
  15.  
  16.  
Thanks for your help.
May 27 '12 #7

NeoPa
Expert Mod 15k+
P: 31,494
First: Well done. It's always good if you can work your way through a problem and come up with the correct answer. Good for you.

For the other point please see my PM. It is about your posting habits so is not appropriate to include here.
May 27 '12 #8

P: 83
Thanks for your input...

Cheers
Raghu
May 28 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.