Good day all! I am new to Excel formulas. I've been asked to create a leave tracking system at work but I am having a minor issue that I cannot figure out. First I must explain the setup. In column A I have "lastname", Column B "firstname", Column C "annual", Column D "sick", Column E "comp", Column F "Hours" (all hrs are 8), column G has the current date, column H has leave type and the rest of the columns continue in the date, then leave type sequence until about Column AK. Example:
Lastname/Firstname/Annual/Sick/Comp/Hours/8/21/11/Leave type/8/22/11/leave type.......
I have 4 tabs at the bottom; "YEAR TO DATE SUMMARY", Aug-Sep 11, Oct 2011, Nov 2011, and Dec 2011. I'm currently working in the Aug-Sep tab. The way this is set up is if I put the amount of hours taken for leave under the current date, and then put "c", "s", or "a" (for comp time, sick leave, or annual leave) in the leave type category, the amount of leave taken will update in the appropriate column (for instance, if I put 6 hrs of leave taken, and put "a" in the leave type column, Column C, which is "annual" will show 6 hrs. If I use an "s" instead of an "a", then sick leave would update to 6 hrs and the other columns would stay at 0). Each time an individual takes leave, the numbers will add up in the appropriate columns. So if I took 6 hrs of annual on 8/23 and 4 hrs of annual on 8/24, the "annual" column (column c) would update to 10. OR if I took 6 hrs of annual and 4 hours of sick, the "annual" column would show 6 and the "sick" column would show 4.
In the YEAR TO DATE SUMMARY tab, this worksheet shows how much leave was taken from each category (sick, annual) and how much leave is left over for each person for the year.
Now that I have explained the setup (whew!) here is my problem..I've put together this formula: =IF(G2<F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2 )*COUNTIFS($I2:$J2,"a", $K2:$L2, "a")<?XML:NAMESPACE PREFIX = F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2 /><F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2 b ?a?)< $K2:$L2, )*COUNTIFS($I2:$J2,?a?,><F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2 b ?a?)< $K2:$L2, )*COUNTIFS($I2:$J2,?a?,><F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2 b ?a?)< $K2:$L2, )*COUNTIFS($I2:$J2,?a?,><F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2 ?a?)< $K2:$L2, )*COUNTIFS($I2:$J2,?a?, p><F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2 ?a?)< $K2:$L2, )*COUNTIFS($I2:$J2,?a?, p>
This is taken from one of the "annual" cells. It works accept for one thing; the hrs will only add up after I've completed the range. For example, I have I2:J2 and K2:L2 as my range under the COUNTIFS formula. I put the hours taken under the current date (cell I2), and then put an "a" in the leave type (cell J2). The "annual" column (column c) will not update with the annual hours taken until I have entered the hours and the letter "a" in K2:L2 also. How do I fix this issue?? My suspicion is that "COUNTIFS" is either the wrong function to use or I need to add something else to this formula. Any help would be greatly appreciated...I hope this explains my issue clearly!!
</F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2>
</F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2>
</F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2>
</F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2>
</F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2>
Lastname/Firstname/Annual/Sick/Comp/Hours/8/21/11/Leave type/8/22/11/leave type.......
I have 4 tabs at the bottom; "YEAR TO DATE SUMMARY", Aug-Sep 11, Oct 2011, Nov 2011, and Dec 2011. I'm currently working in the Aug-Sep tab. The way this is set up is if I put the amount of hours taken for leave under the current date, and then put "c", "s", or "a" (for comp time, sick leave, or annual leave) in the leave type category, the amount of leave taken will update in the appropriate column (for instance, if I put 6 hrs of leave taken, and put "a" in the leave type column, Column C, which is "annual" will show 6 hrs. If I use an "s" instead of an "a", then sick leave would update to 6 hrs and the other columns would stay at 0). Each time an individual takes leave, the numbers will add up in the appropriate columns. So if I took 6 hrs of annual on 8/23 and 4 hrs of annual on 8/24, the "annual" column (column c) would update to 10. OR if I took 6 hrs of annual and 4 hours of sick, the "annual" column would show 6 and the "sick" column would show 4.
In the YEAR TO DATE SUMMARY tab, this worksheet shows how much leave was taken from each category (sick, annual) and how much leave is left over for each person for the year.
Now that I have explained the setup (whew!) here is my problem..I've put together this formula: =IF(G2<F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2 )*COUNTIFS($I2:$J2,"a", $K2:$L2, "a")<?XML:NAMESPACE PREFIX = F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2 /><F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2 b ?a?)< $K2:$L2, )*COUNTIFS($I2:$J2,?a?,><F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2 b ?a?)< $K2:$L2, )*COUNTIFS($I2:$J2,?a?,><F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2 b ?a?)< $K2:$L2, )*COUNTIFS($I2:$J2,?a?,><F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2 ?a?)< $K2:$L2, )*COUNTIFS($I2:$J2,?a?, p><F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2 ?a?)< $K2:$L2, )*COUNTIFS($I2:$J2,?a?, p>
This is taken from one of the "annual" cells. It works accept for one thing; the hrs will only add up after I've completed the range. For example, I have I2:J2 and K2:L2 as my range under the COUNTIFS formula. I put the hours taken under the current date (cell I2), and then put an "a" in the leave type (cell J2). The "annual" column (column c) will not update with the annual hours taken until I have entered the hours and the letter "a" in K2:L2 also. How do I fix this issue?? My suspicion is that "COUNTIFS" is either the wrong function to use or I need to add something else to this formula. Any help would be greatly appreciated...I hope this explains my issue clearly!!
</F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2>
</F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2>
</F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2>
</F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2>
</F2,(G2),(G2-G2))*OR(H2="a")+SUM(I2:AU2>
Last edited:
