435,483 Members | 3,312 Online
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
 IncExcel.zip (95.8 KB, 43 views)
May 24 '12 #1

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.

8 Replies

 Expert Mod 15k+ P: 31,494 Possible outcomes are :"N/A" G3 (StartIncDate) + Q3 (daysInYear) G3 (StartIncDate) + Q3 (daysInYear) + S3 (countLeave) If K3 (newIncrement) is "N/A" then A. ELSE If I3 (ceaseDate) is "CANCELLED" then B. If P3 (dischargeDate) - G3 (StartIncDate) > I3 (ceaseDate) + Q3 (daysInYear) - H3 (commDate) OR P3 has not been set then C. 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

 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     If newIncrement = "N/A" Then         effDate = ""     ElseIf ceaseDate = "" Then             effDate = startIncDate + daysInYear     ElseIf (dischargeDate - startIncDate) > (ceaseDate - commDate + daysInYear) Then         effDate = startIncDate + daysInYear + countLeave     ElseIf dischargeDate = "" Then         effDate = startIncDate + daysInYear + countLeave     ElseIf dischargeDate <> "" And (dischargeDate - startIncDate) <= (ceaseDate - commDate + daysInYear) Then         effDate = ""     End If May 26 '12 #5

 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       If newIncrement <> "N/A" Then         If Nz(ceaseDate, "") = "" Then             effDate = startIncDate + daysInYear         Else             If dischargeDate + 1 - startIncDate > (ceaseDate + 1 - commDate) + daysInYear Or Nz(dischargeDate, "") = "" Then                 effDate = startIncDate + daysInYear + countLeave             Else                 effDate = ""             End If         End If     Else         effDate = ""     End If     Thanks for your help. May 27 '12 #7

 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