I inherited this spread sheet. Could someone explain to me what is happening by way of the calculations in column L and column Q? No Clue at all.
Thank you
Raghu PrabhuIncrement.zip
I inherited this spread sheet. Could someone explain to me what is happening by way of the calculations in column L and column Q? No Clue at all.
Thank you
Raghu PrabhuIncrement.zip
Column L
=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")
If K3 (the New Incr) is not N/A then
If I3 (LWOP CEASE) is CANCELLED then use the Start Incr Date in G3 plus the number of days in Q (which in this case is 17-Jan-04 plus 366 days)
Else
If P3 (PTLWOP) plus 1 day minus G3 (Start Incr Date) is greater than I3 (LWOP CEASE) plus 1 day minus H3 (LWOP COMMENCE date) plus the number of days in Q (which in this case is plus 366 days)
......OR if P3 (DISC/PROM DATE) is blank
then
G3 (Start Incr Date) plus the number of days in Q (which in this case is plus 366 days) + plus the number of days in S (which in this case is Zero days)
Else N/A
The last NA simply means that the first line “If K3 (the New Incr) is not N/A” was in fact NA so don’t do anything of the calculations in the middle.
Column Q
=DATE(IF(D3="FLYING",YEAR(G3)+2,YEAR(G3)+1),MONTH(G3),DAY(G3))-G3
If D3 (the Rank) = FLYING then
add two years to the date in G3 (Start Incr Date)
If NOT FLYING add one year to the date in G3 (Start Incr Date)
Then subtract G3 (Start Incr Date) from this date.
Basically it’s counting the number of days in the next two years for Flying Rank or the next one year for all other Ranks, including the extra day for leap year instead of just assuming all years are 365 days.
Thanks Carsto. I am now able to get on with my work.
Cheers
Raghu Prabhu
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks