How To Coda with hours?
converting days into hours
When people work, often their work time is expressed in hours. Three days of 8 hours makes 24 hours, which is the same as one day, but you’d rather say 24 hrs.
In this blog we explore ways to work with hours, instead of the pre-formatted Coda logic that turns 36 hours into 1 day and 12 hours. We have at our disposal these functions and we only use nbr 2 and 4.
I am not a fan of explaining functions by finding a use case to demonstrate their purpose. I’d rather work the other way around. We have a look at our case and next we check our toolbox.
Vacation
The issue we face is that in the example someone receives a package of hours to consume. For example 48 hours. These 48 hours can be distributed over various days.
One table contains the balance. It starts fresh with 48 hours at Jan 01 and counts down. The second table registers the consumption per request. We simply assume that all people work 8 hours per day and 5 days per week. The holidays are USA. We use the function Workdays() to calculate the net working days and thus the hours. In a previous blog I showed how you can set up your own and more advanced workday logic. Here we keep it simple.
How can we turn 48 into 48 hours?
We face two issues. First the number 48 is a normal number since it is manually typed in. To turn 48 into 48 hours, you can say : Hours(48) but this does not output the right ‘number’. It turns it into two days.
the way it works you see below.
This only works because to two reasons. First the numbers (input) have to be converted into a duration and this is the result of the function Hours() . The second condition is the column data type, this should be Text, not duration. You cannot skip one of them, below how you fail when you do not first turn the integer into a duration.
Making Tables Talk
In our set up we have a form, once filled out, an automation subtracts the days as hours from the balance. May the employee not have be in the list, it is added otherwise the balance is modified. The focus is not here on the automation, the filtering, keeping the balance in line, etc.. It is strictly about the hour logic.
You notice that in the formula I apply the function Sum() and Product() and this is to make the formula work for us. The simple math that would stay — take what we have minus vacation hours — won’t work. The FormatDuration() works on the outcome of the calculation. Often I see fellow makers using the * and the + and while I would advocate that in certain (but limited) contexts this is a good (or even best practice), certainly not here.
One last thing. Below you see how a negative number is expressed. This is not very nice. Ideally we would see — 152 hrs, but that is not how Coda natively operates.
Nevertheless we can bend the rules a bit. Negative means lower than zero. Below how that goes.
This is the kind of trickery that stretches the limits of Coda. If you use this, I would advice to use a column for visual display and one with the Coda data you calculate with. The Coda values you can then use as starting point for further calculations while the display value only displays, it is thus an additional column.
Anyway, Coda should solve this elegantly by providing us display options (ago or — ). If they read with me, they can put this on their list of to do.
Your take away from this blog is: first Hours() and second FormatDuration() The doc showing you the principles applied in the above you find below. As always, this doc is not for sale, nor can you copy it. You learn most when you step in and create your own solutions.
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.
Christiaan — Coda Expert — on: “How To Coda with hours?”
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.
