IF statement help needed!


#1

Hello,

I am using air table to keep track of time worked, and would like to keep a running total on how many hours is remaining from a contract. Essentially I’d like to make a field follow the rule: if {completed} is checked, then add the {time worked}

I already have a “completed” column set up as a single checkbox, and a “time worked” as a number. Can anyone help?

Thanks in advance!


#2

What are you wanting to add the Time Worked value to?

IF ( Completed = 1, {Time Worked} + ???, BLANK()

It sounds like you have a case here where you need multiple Time Worked entries linked to a single Contract - is that right?

What is Completed meant to be an indication of? That the Contract is completed, or that an entry of Time Worked is completed?


#3

Hello, let me clarify my goal.
Say I have 3 entries for upcoming shifts at the coffee shop:
Date Time Shift Duration Shift Completed Total Time Worked
5/1/18 3pm-8pm 5 hours
5/2/18 3pm-8pm 5 hours
5/3/18 3pm-8pm 5 hours

After each shift, I can check the {Shift Completed} cell to confirm the shift is completed.
For the {Total Time Worked}, I would like to essentially have a running total of all time worked. So in the example above, the {Total Time Worked} in first row would be 5, the second row would be 10, and the third row would be 15.

My goal with this setup is to allow me to see a running total of time worked, while keeping the shifts that may have been scheduled and not completed (and not add those hours).

Hope this is clear. Thanks!!


#4

Thank you for that clarification - I think I understand a bit better now.
I’m working on something that I think represents what you are looking for - I’ll post when finished.


#5

You mentioned “Contracts” in your OP:

So I stuck with that concept because it makes this whole thing easier.

Here’s an example base you can take a look at that uses rollups on conditional fields to allow you to compare the total Hours of all shifts associated with a contract to the total Hours of all shifts completed on that contract:

The fields have descriptions showing the formulas used - mouse over the (i) by the field names.
Let me know if that isn’t what you’re looking for and we can continue to narrow down the focus towards what it is you need.

EDIT: **Also note, it is not possible to do a “running total” column that automatically totals based on the rows above it, even with a formula. Airtable cannot reference across rows, only across fields. Linking records to another table that performs a Rollup on the linked records is the best way to handle this use case in Airtable.