How to Coda negative hours?
Use Sum() and Product() to get a visual coherent outcome
Once you start dealing with Human Resource Related matters in Coda it easily gets complicated due to the many variations. In the example of today we deal with a balance that keeps track of sick leave. Since you easily get confused in this sort of set-up, I decided to write a blog about it.
When I created a balance with 100 hours on it, it got directly converted into days. See below. However when you want to show a balance in hours, this does not work well. It is a minor issue I could solve by putting the column first on duration and then back to text.
More importantly is that I want to subtract from the balance the hours consumed. These hours we get by counting the sick days and we multiply them by 8 hours. The result is what you see below. When you by mistake start with the end date, we show a message, in case one of the dates is blank, you get a zero and in all other cases we multiply simply by 8 hours. This example does not deal with working days, holidays, vacations etc. It is a simple and straightforward example.
We do not want to show 56 hrs ago, which is what you get when you multiply by minus one. Not because it is technically wrong, it is confusing for any user of this document and thus not a solution. The column consumed hours contains a positive number or a a text, period.
That said, we want to related the consumed hours to the balance. Below how it goes. We take the sum of two values. First the value on the balance as it is and second we bring in the value of the other table and we multiply it by -1 to get a negative number. The filter on currentvalue at the end of some functions, cleans the list we generate.
The outcome is what you see below and to show my next point, I pushed the number below zero.
We don’t want to see 12 hrs ago. In my previous blog on how to coda with hours ⤵️ , I showed a way to format negative numbers and this logic we apply again, but with one extra condition.
The extra condition cocerns the null value. If you don’t intervene, it results in a “-“ and although we can calculate with it perfectly, it is not pleasing for the eye, if not confusing. I added the rule you see underligned and and turned the If statement into an SwitchIf().
The outcome in the table works fine as well because it permits us to calculate.
We have two tables in this conversation and the set up permits for keeping track of your sick leave balance sheet. This is a bit special, because when we apply a comparable logical rule, it fails:
The template that shows how this works, you find below ⤵️:
As always not to copy, you learn most by doing yourself.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.
Coda comes with a set of building blocksーlike pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your docーso anyone can make a doc as powerful as an app (source).
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
If you enjoyed this read and would like to get more Coda related content, please consider a Medium membership. It is it only $5 a month, and you’ll have access to every article ever published on Medium. If you sign up using my referral link, I’ll earn a small commission.
