Hello all,
I feel like I’m missing something super simple here. Can someone please help?
I made a quick screen recording to show you my set up - https://youtu.be/gNbCUM4ohlc
Thanks in advance,
Troy
1 Like
I know what’s happening, although I agree it’s counterintuitive.
Coda stores date/time/durations internally as the number of days. Hence your e.g. 6 hours are actually stored as 0.25 and are treated as 0.25 when multiplied by rate.
Simple fix is to multiply duration by rate by 24. Not elegant, but I don’t know of any formulas on top of my head to do the conversion more semantically (e.g. there’s Hours(x) function that converts hours into a fraction of a day, but not vice versa)
P.S. Welcome to the community, and thanks for the illustrative video!
4 Likes
Dude that’s awesome! Works perfectly. Thank you very much.
Here’s what I wrote word for word encase anyone struggles with formulas…
=(Hours worked*24)*hourly rate
Ps. Great to be here. I am building my whole business around Coda it’s awesome!
1 Like
Troy,
Sounds like you’ve got a solution so this is superfluous. But I thought I’d post this in case it’s helpful to anybody else.
Looks like you are using a Date field formatted as ‘Duration’. Date/Duration fields are pretty neat and if you enter data as, say, “3 hrs 30 mins”, then the fix you came up with – multiplying by 24 before multiplying by the rate – works great.
But there are other ways to approach this problem. I want to suggest two, one very simple, one slightly more complex.
The simple approach
First, use a simple number field for hours spent and enter a decimal value into this field. Turn your ‘time spent’ field into a field named (most obviously) ‘Hours’ and make it a simple number field. Set the precision: I prefer one decimal but if you like to enter values like 1.25 then use two decimals. Then enter hours as decimal values: For example, one hour and a half = “1.5”. Now you can multiply that times the hourly rate and get a result without having to adjust anything else. A bonus of this approach is that it’s easy to summarize so you can see (say) how many hours an employee worked, or how many hours everybody worked, etc.
One disadvantage of this approach – at least it might seem a disadvantage at first – is that you have to learn to think of times as decimal values rather than hours and minutes. I’ve been doing it so long that it’s second nature to me. Another disadvantage that for certain situations might be more consequential is that a tenth of an hour = 6 minutes and if you want to be accurate to 1 minute, then this ain’t the best way to do it.
The less simple, more precise approach
Alternatively – and in view of the disadvantages just mentioned – you could calculate hours spent. My own sense is that date/duration fields work best if you’re really interested in tracking time to the minute, and calculating the hours spent gives you the ability to be that precise.
Now you can calculate the duration in a couple of different ways. First, you might have two fields ‘TimeStart’ and ‘TimeEnd’ and then use a formula field ‘SpentCalc’ that subtracts the later time from the earlier to get a result. Note that this result will be a duration value, i.e. by default it’s a value in days. To fix that, do what you did: add the “x24” bit to the SpentCalc field so that it’s a proper number of hours. The alternative is to create two fields: Hours and Minutes. This allows you to quickly enter “2” into Hours and “37” into Minutes. Then write the formula for your HoursCalc field so that it converts the minutes to hours as a decimal and adds it to the value in Hours and Bob’s your uncle!
I’ve attached a screen capture showing all three approaches. The rate in every case is $25 (per hour). The first example (boxed in red) is what you came up with yourself at first: 1 hour and 30 minutes of work generate only $1.56 in income. The second example, in the green box, is my own preference: ‘Hours’ is a simple number field and I entered “1.5” into it. Now Result2 shows the correct value: $37.50. The last example uses TimeStart and TimeEnd fields (both have to be entered). HoursCalc = (TimeEnd-TimeStart) * 24. Result3 is also correct and as you can see, can be precise to the minute.
There’s more than one way to solve almost every problem. Which you select depends on which works best for you and your users.
William
1 Like
I just updated a “Pay Tracker Template” that I had posted quite a while ago. It was one of my first big “aha” moments in Coda working with different schemas. I had different pay rates to track depending on the job being done and I also didn’t want all previous data to change if pay changed, if I got a raise for instance. So this tracks different types of pay over different categories and also different time periods…
1 Like