Excel - Need Help to Shorten Formula

[Divercem]

My apologies for such a long post, but this is a long formula and I would like to see if anyone can make suggestions to shorten it.

I would post the entire spreadsheet, but I do not have any public space. It is a year-long timesheet consisting of 13 tabs - 12 months (Jan-Dec) & and 1 Months tab (with holiday calculations).

Where I work I am not allowed to use Macros or VBS so I created a spreadsheet using only formulas to perform the calculations to track my time and to accrue/use CompTime & Leave

This cell has 2 purposes,
1) if R33 has a 'U' (for Use Leave) then calculate how much leave to use to reach the maximum hours for the day (determined by $P$1)
A. if total hours worked for the week < 40 hours then determine if hours worked "Today" < $P$1 - Yes? $P$1 - O33 (Week Total Time)
Example: If I worked 4 hours today, I will need 4 hours of leave to get 8 hours ($P$1) for the day
B. if total hours worked for the week > 40 hours then reduce Used Leave hours to make weekly total 40
Example: If I worked 4 hours on Tuesday, I need 4 hours of leave to get 8 hours. But if I worked 9 hours on Monday & Wednesday and 8 hours on Thursday & Friday then the week's total is 42 hours, so Tuesday's leave has to be reduced to 2 hours instead of 4 hours so the result is 40 hours for the week.

2) if > 40 hours have been worked, then accrue Comp Time.

This is Cell S33 - Accrue/Use CompTime

Here is the formula in string format...below the formula I break it out for easier reading

=IF(C33="Sunday",0,IF(AND(C33<>"Saturday",R33="U"),IF(ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+7>IF($C$33="",IF($C$32="",IF($C$31="",30,31),32),33),IF(SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$P$33)<40,IF(40-SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$P$33)-SUMPRODUCT((INDIRECT("R"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$R32="U")*INDIRECT("S"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$S32)>$P$1*24,($P$1*24)-$P33,IF(40-SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$P$33)-SUMPRODUCT((INDIRECT("R"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$R32="U")*INDIRECT("S"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$S32)+P33>$P$1*24,($P$1*24)-$P33,40-SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$P$33)-SUMPRODUCT((INDIRECT("R"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$R32="U")*INDIRECT("S"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$S32))),0),IF(SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+7))<40,IF(40-SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+7))-SUMPRODUCT((INDIRECT("R"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$R32="U")*INDIRECT("S"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$S32)>$P$1*24,($P$1*24)-$P33,40-SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+7))-SUMPRODUCT((INDIRECT("R"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$R32="U")*INDIRECT("S"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$S32)),0)),IF(P33=0,0,IF(SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$P33)>40,SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$P33)-40-SUM(INDIRECT("S"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$S32),0))))

Cell S33 - Accrue/Use CompTime

=
IF(C33="Sunday",		[COLOR=#EF2929]' If it's Sunday, there is no chance for comp time (Comp Time > 40 hours)[/color]
  0,
  IF(AND(C33<>"Saturday",R33="U"),	[COLOR=#EF2929]' You can't USE CompTime on Saturday (but you can accrue CompTime)[/color]
    IF(ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+7>	[COLOR=#EF2929]' If the Row() of the first day of the week (Sunday) is > 33 or 32 or 31 or 30[/color]
     IF($C$33="",				[COLOR=#EF2929]' Row 33 is the 31st day of the month,[/color] 
      IF($C$32="",			[COLOR=#EF2929]' Row 32 is the 30th day of the month[/color]
       IF($C$31="",		[COLOR=#EF2929]' The last day of February is either Row 30 or 31[/color]
        30,
        31
       ),
       32
      ),
      33
     ),
     IF(SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$P$33)<40,	[COLOR=#EF2929]' If less than 40 hours worked[/color]
      IF(40-SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$P$33)-SUMPRODUCT((INDIRECT("R"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$R32="U")*INDIRECT("S"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$S32)>$P$1*24,[COLOR=#EF2929]' if 40 - HrsWork - HrsUsed > DailyMaxHrs[/color]
        ($P$1*24)-$P33,	[COLOR=#EF2929]' calculate DailyMaxHours - TodayHrs[/color]
        IF(40-SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$P$33)-SUMPRODUCT((INDIRECT("R"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$R32="U")*INDIRECT("S"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$S32)+P33>$P$1*24,	[COLOR=#EF2929]' Else if 40 - HrsWork - HrsUsed + TodayHrs > DailyMaxHrs[/color]
          ($P$1*24)-$P33,	[COLOR=#EF2929]' calculate DailyMaxHours - TodayHrs[/color]
          40-SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$P$33)-SUMPRODUCT((INDIRECT("R"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$R32="U")*INDIRECT("S"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$S32)		[COLOR=#EF2929]' Else 40 - HrsWork - HrsUsed[/color] 
        )
       ),
       0	[COLOR=#EF2929]' If >= 40 hours, HrsUsed is = 0[/color]
      ),
      IF(SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+7))<40,	[COLOR=#EF2929]' if HrsWork < 40[/color]        IF(40-SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+7))-SUMPRODUCT((INDIRECT("R"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$R32="U")*INDIRECT("S"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$S32)>$P$1*24,[COLOR=#EF2929]' if 40 - HrsWork - HrsUsed > DailyMaxHrs[/color]
          ($P$1*24)-$P33,[COLOR=#EF2929]' DailyMaxHrs - TodayHrs[/color]
          40-SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+7))-SUMPRODUCT((INDIRECT("R"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$R32="U")*INDIRECT("S"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$S32)[COLOR=#EF2929]' Else 40 - HrsWork - HrsUsed[/color]
        ),
        0				[COLOR=#EF2929]' if >= 40 hours, HrsUsed is = 0[/color]
      )
     ), 			[COLOR=#EF2929]' Else If the Row() of the first day of the week (Sunday) is < 30[/color]
     IF(P33=0,		[COLOR=#EF2929]' If TodayHrs = 0[/color]
       0,		[COLOR=#EF2929]' Then HrsUsed = 0[/color]
       IF(SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$P33)>40,	[COLOR=#EF2929]' Else if HrsWork > 40[/color] 
         SUM(INDIRECT("P"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$P33)-40-SUM(INDIRECT("S"&ROW()-WEEKDAY(DATE($B$1,MONTH(1&$A33),B33))+1):$S32),[COLOR=#EF2929]' HrsWork - 40 - AccruedHrs[/color]
         0
       )
     )
   )
)

 

[SkipVought]

Hi,

I'd suggest using a lookup table as the basis for your logic rather than nested IF() functions. Give a lot of thought to the construction of your lookup table.

First write out your logic in pseudo code. You might also construct a truth table you develop the logic for yes/no conditions and the necessary result calculation.

However I'd declare a STRONG "NO!" To nested ifs.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

You have some data embedded in your formula that is not obvious, like what is significant that the row() is > 30?

I really think that you need to post a sample of the sheet data/structure that you're working with [ignore][pre]...[/pre][/ignore] TGML codes to maintain column spacing (check your results in [highlight #CC0000]Preview[/highlight] before you [highlight #CC0000]Submit Post[/highlight]).

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Here's another tip regarding dates, specifically the last day of the month, that may be of help to you.

[pre]
Month Last Date Formula
1 1/31/2014 =DATE(YEAR(TODAY()),A2+1,0)
2 2/28/2014
3 3/31/2014
4 4/30/2014
5 5/31/2014
6 6/30/2014
7 7/31/2014
8 8/31/2014
9 9/30/2014
10 10/31/2014
11 11/30/2014
12 12/31/2014
[/pre]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Divercem]

Skip,

Thank you for the responses. This code works very well, but it is a nightmare to maintain and if I can figure out tables like you suggested it will help when it comes time to change something.

Answers to your responses:
1st response: I will look into the lookup tables and see if I can get them to replace my bird's nest of if statements.

2nd response: As for the "embedded data", I was attempting to put comments in the code I broke out by making them red but I may have missed one or two. I spent a great deal of time formatting the post (probably hitting the Preview button a couple dozen times). However, the last time I was going to preview it I inadvertently clicked "Submit Post"...it was late and I was ready for bed anyway.

3rd response: Thanks for the tip on dates, the first 27 rows do not have the if statements about row 30 - 33...I randomly picked cell S33 to post. If I had picked one of the first 6 rows you would have seen the references to the previous tab...in hindsight I should have picked a row out of the middle because it isn't concerned with the last day of the month or the week of the previous month. Sorry...I probably just muddied the water...anyway I appreciate your tip about determining the end of the month.

I appreciate your comments. I will research lookup tables like you suggested.

Here is a text example of the spreadsheet. It is too wide for pre.../pre so I used code.../code so you can scroll to see the whole spreadsheet.

|  |    A    | B  |    C    |   D   |  E   |  F  |  G  |  H  |  I  |  J  |  K  |  L  |  M  |  N  |  O  | P  |  Q   |    R   |  S   |  T  |  U  |      V      |
---+---------+----+---------+-------+------------------------+-----------------------------------+-----+----+------+--------+------+-----+-----+-------------+
 1 |CSD      |2014|         |       |     Normal Hours       |             Off Hours             |     |8:00|      |         Comp Time   |Total|             |
---+---------+----+---------+-------+------------------------+-----------------------------------+-----+----+------+--------+------+-----+-----+-------------+
   |         |    |         |Holiday|      |Lunch|Lunch|     |     |     |     |     |     |     |Total|CSD | CSD  |A=Accrue|Accrue|Total|Leave|             |
 2 |Month    |Day | Weekday |Bereave|Start | Out | In  | End |Start| End |Start| End |Start| End |Time |Time|Weekly| U=Use  |or Use|Avail|Avail|Weekly Total |
---+---------+----+---------+-------+------------------------+-----------------------------------+-----+----+------+--------+------+-----+-----+-------------+
 3 |February | 1  |Saturday |       |      |     |     |     |     |     |     |     |     |     |     |0.0 | 47.1 |        |  0.0 | 2.1 |0.000|  0:00:00    |
---+---------+----+---------+-------+------------------------+-----------------------------------+-----+----+------+--------+------+-----+-----+-------------+
 4 |February | 2  |Sunday   |       |      |     |     |     |     |     |     |     |     |     |     |0.0 |  0.0 |        |  0.0 | 2.1 |0.000|             |
---+---------+----+---------+-------+------------------------+-----------------------------------+-----+----+------+--------+------+-----+-----+-------------+
 5 |February | 3  |Monday   |       | 8:00 |11:30|12:30|18:00|     |     |     |     |     |     | 9:00|9.0 |  0.0 |        |  0.0 | 2.1 |0.577|             |
---+---------+----+---------+-------+------------------------+-----------------------------------+-----+----+------+--------+------+-----+-----+-------------+
 6 |February | 4  |Tuesday  |       | 8:00 |     |     |12:00|     |     |     |     |     |     | 4:00|4.0 |  0.0 |    U   |  2.0 | 0.1 |0.833|             |
---+---------+----+---------+-------+------------------------+-----------------------------------+-----+----+------+--------+------+-----+-----+-------------+
 7 |February | 5  |Wednesday|       | 8:00 |11:45|12:45|18:00|     |     |     |     |     |     | 9:00|9.0 |  0.0 |        |  0.0 | 0.1 |1.410|             |
---+---------+----+---------+-------+------------------------+-----------------------------------+-----+----+------+--------+------+-----+-----+-------------+
 8 |February | 6  |Thursday |       | 8:00 |11:45|12:45|17:00|     |     |     |     |     |     | 8:00|8.0 |  0.0 |        |  0.0 | 0.1 |1.923|             |
---+---------+----+---------+-------+------------------------+-----------------------------------+-----+----+------+--------+------+-----+-----+-------------+
 9 |February | 7  |Friday   |       | 8:00 |11:30|12:30|17:00|     |     |     |     |     |     | 8:00|8.0 |  0.0 |        |  0.0 | 0.1 |2.436|             |
---+---------+----+---------+-------+------------------------+-----------------------------------+-----+----+------+--------+------+-----+-----+-------------+
10 |February | 8  |Saturday |       |      |     |     |     |     |     |     |     |     |     |     |0.0 | 40.0 |        |  0.0 | 0.1 |2.436|  38:00:00   |
---+---------+----+---------+-------+------------------------+-----------------------------------+-----+----+------+--------+------+-----+-----+-------------+

Charlie

 

[SkipVought]

The "embedded data" I was referring to is the LITERAL DATA in your formulas. like 30 - 33. Then you are using an indirect (not the function) value, ROW, rather than some actual data value. That adds another level of obscurity to the maintenance/documentation conundrum.

So what is the function of these kinds of sheets: Data entry or data storage. I have a feeling it's both. A structure like this is great for data entry, JUST TO CAPTURE DATA -- NOTHING ELSE. It is an absolutely horrendous structure to store and analyze data, which is the issue that you are obliquely dealing with (that is the analysis—calculation—of data). But it is what it is: a system that both captures and poorly attempts to store data for future reference and analysis. I’ll bet that gathering data for ALL employees or all employees in a certain department, in a month, quarter or year is not a push-a-button-and-get-the-answer kind of effort as it ought to be.

I’d emphasize again that developing a logic table will most likely enable you to reduce the complexity of your formula AND maintain changes to labor logic in a much simpler manner.

Your posted sheet structure may provide the visual that some Tek-Tips member might need to help answer questions that you might have regarding the design of the logic table.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

Looking at your example of Feb 27, it gives more insight than your original post. Unfortunately there are several things that I think would make your live a whole lot easier.
1. Combine all your monthly data into 1 spreadsheet. This will make it a lot easier when trying to deal with weeks that spread across more than 1 month.
2. Combine Cols A & B into a signle DATE field. This allows you to use a lot of the different Date functions in Excel. For example, Col C wouldn't be needed since you can get the day of the week with the WEEKDAY function or automatically have it filled out with =CHOOSE(WEEKDAY(A3), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
3. Please explain the following from your example, CSD Weekly; Total, Leave Avail.
4. Can a worker use Compt time before it's earned (e.g., Worker only works 4 hrs on Monday, but 12 hrs on Friday)
5. Depending on when this spreadsheet is being used, you might want to have a daily running total of hrs worked per week. This is especially true if you want to limit the amount of Compt time that can be earned.
6. In your example above, only 38 hours were worked in the week. How do you want your spreadsheet to flag this. I would think that after Feb 4th, that you should have something indicating that the worker is still 2 hours behind during the week.