Bug in hour() formula

Suggestion Box Bugs
1

Seems like there is a bug using the hour() formula on a date and time field. Or am i missing something?

2

hi @yscias , once you set the column to text, it works.

3

Yep, but once you use formulas to round the time to the nearest 30 mins or hour or do something else, it will interfere with the way time is calculated and shown on charts.

4

Hi @yscias :blush: !

Hour() returns the hour from a Date/Time value as a number … which your time field doesn’t seem to know how to interpret :blush: …
At least, this is how I understand the issue here and I could be perfectly wrong :sweat_smile:

I still added 4 fields to your doc so you could see and explore some possibilities (depending on your use case scenario) :blush: :

The 1st one (Hour()) is the exact same formula as yours but within a Number field.

The 2nd one ([Time() - 1]) recreates a Time() value in a Time field using your Date/Time field, Hour() and Minute()

Time(thisRow.[Date and Time].Hour(),thisRow.[Date and Time].Minute() ,0)

The 3rd one (Time() - 2) does something similar to the 2nd one but uses only the Hour() to recreate a Time() value

Time(thisRow.[Date and Time].Hour(),0,0)

And the 4th one (DateTimeTruncate()) DateTimeTruncate() your Date/Time field to the hour in a Time field

thisRow.[Date and Time].DateTimeTruncate("hour")

Hope this helps :blush:

2 Likes
5

Thanks a lot! I didnt know i need to convert a date time value to time first… Learning new things everyday :slight_smile:

6

No problem @yscias :relaxed: !

It really depends on your use case :wink: … and those were just few examples to manipulate your Date/Time as, as always, they are more than one way to get a desired result in Coda :blush: .

But the general “Coda rule” remains : It always works best if you associate oranges with oranges or apples with apples… I.e.: data type X with data type X … And in this case I guess, a time value with a time field or a number value with a number field :blush:

Some fields are more flexible than others when it comes to the data type of your inputted value whether it comes from a formula or not (e.g.: text field) and Coda does a lot of auto-guessing/auto-converting behind the scene but still, keeping an eye on the data types of your values helps avoiding those kind of unexpected results :wink: .

1 Like
7

Thanks so much! You’ve been super helpful!

1 Like
8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.