Can't figure out IF statements

Hello, I decided to create a calc work schedule for me and my colleagues. Biggest problem for us was making sure we put in the right amount of hours for each of us which I figured out by manually subtracting start of shift time from end of shift time and manually wrote it down:

=(F2-E2)+(F3-E3)+(F4-E4)+(F5-E5)+(F6-E6)+(F7-E7)+(F8-E8)+(F9-E9)+(F10-E10)+(F11-E11)+(F12-E12)+(F13-E13)+(J2+J4+J6+J8+J10+J12)

The problem is that when someone goes on vacation we literally write the word vacation in the same and every time cell for that person (we also have different week hours since only one of us is full time and the rest are part time).

So I am forced to code up an if statement so if a cell is occupied by the word vacation it adds 4 or 6.6 hours to the cell which shows the total ammount of hours for that week for that particular person.
So far i came up with this broken “code”:

IF(C2:C8=“vacation”)
THEN (D2=D2+04:00)

I enabled wild cards and also formatted all cells to time.

Also sorry for the long story, didn’t know how to explain my self as precise as possible otherwise because english isn’t my main language.

Here’s an image just to give u an idea of what i’m trying to do

Which cell will this total be in?
Post an example of the spreadsheet, with the total filled in.


The first modified formula:
=SOMA(F2:F13)-SOMA(E2:E13)+J2+J4+J6+J8+J10+J12

1 Like

Background, causes

When your formula “sums explicitly” (like =A1+A2+A3), it states that every cell is expected to have a numerical value. Any cell containing text will throw an error. With this approach, your suggested IF() construct makes sense. The IF function syntax is slightly different from the IF statement you seem to be employing. See the IF() function description on the help pages.

Some summation functions will discard invalid values without throwing error. =SUM(A1:A3) will add up numbers in the range and ignore the text values. With this, no IF() is needed for your task at hand.

Solution

Use SUM() with ranges.

According to associative law for addition(*), you can rearrange the cell references as you like in your formula. Optimally in spreadsheet context, rearrange so they form contiguous ranges.

Also, you are summing every second cell in the J column, but the cells are merged to pairs, so you can safely also sum this as a contiguous range.

This allows for an adjusted version of the formula already suggested by @schiavinatto :

=SUM(F2:F13;-E2:E13;J2:J13)

Pitfalls

Note that this does not catch missing/bad input (Typically: times entered with invalid characters so they are taken as text). You may want to add such error catching. Applying a “sanity check” to the result will usually reveal the existence of error, but not always.

Also, if you have the possibility of shifts “crossing midnight” (start and end are different dates), you need a slightly more elaborate construct.

(*) The associative law is not valid for subtraction, as you may know. @schiavinatto and I are treating your subtraction as an “addition of negatives” (making sure the minus follows the number). Stretching the rule, but not breaking it. I promise!

2 Likes