🆕 Date-time Math

Announcements
#81

Proposal 2021-01-15 at 8.19.53 PM
Proposal 2021-01-15 at 8.19.53 PM868×560 55.1 KB
Proposal 2021-01-15 at 8.20.35 PM
Proposal 2021-01-15 at 8.20.35 PM612×1096 71.1 KB

on second row is 59 min but is only 58.56
only first row is working good

#82

Maybe I’m confused at what I’m looking at. How is the 58.56 seconds supposed to be affecting the minutes column. I guess what I’m getting at is why is it seconds in a duration so closely matches minutes other than being a highly unlikely coincidence. Or am I just overthinking it and currently the seconds column is really displaying minutes??? Just trying to understand what I’m looking at before trying to figure out why it didn’t round down.

If it makes any of this easier, you do also have hour(), minute(), and second() functions that you can wrap around your duration calculation. Maybe something like hour(date+(now-date)), but I’m not quite sure that that will work how you want.

#83

ok, i fixed formula to:
floor((((now - time in)*24)-floor(((now - time in)*24)))*60)
and is working good now, tnx for helping

2 Likes
#84

so i created nice tab “Time Card” using math column and Now function

1 Like
#85

Hey guys how would I be able to display X number of hours and X number of minutes? (1hr & 5 minutes)

See screenshot…

#86
#87

Thank-you @Jeff_Hager…so now the final question is…
Why is the minute column doing what it is? It should be 5 minutes…?

#88

Can you show the formula in your minutes column?

#89

I used yours form the example…which would you care to see?

#90

#91

Hey, @Jeff_Hager did that help?

#92

Sorry, had to deal with my paying day job.

I was hoping to see the Minutes column, not the duration column, but seeing that does help and I’m assuming you are using the same formula from my other post in the minutes column, I think you may have a problem because you are multiplying you duration by 24. The duration should be the difference between the two date columns with no other math applied. My formulas deal with the raw duration that based on the fact that 1.0 equals 1 day. Multiplying by 24 is throwing it out of whack.

Your duration for 1 hour 5 minutes should be approximately 0.045. Also, my formulas deal with seconds, so if you use the existing formulas, you may end up with slightly different results than you are thinking. For example, your calculated duration of 1.08 divided by 24 is 0.045. 0.045 is equal to 1 hour, 4 minutes and 47 seconds. If you want it to always round up to the next minute, then you may have to change the FLOOR in the Minutes column to a CEILING. Or you may have to change it to a ROUND if you want it to round either up or down. This is assuming that you don’t want to see seconds at all.

Hope that helps.

1 Like
#93

#94

Ok, that looks fine. Just fix the duration column and you should be good.

Just remember that in the current form, it will always round the minutes down. It’s up to you how you want the minutes to round based on the number of seconds beyond a minute.

#95

Okay so I changed things to CEILING but It is 2 minutes off still. I am sorry man, I stink at math.

#96

Can you show the work? It works for me. I want to see the values in your duration, hour, and minute columns, as well as the formulas in each of them.

#97

1 Like
#98

  • Remove the divide by 24 in the duration column. There should be no manipulation to the duration other than the subtraction of start time from end time.

  • Change CEILING back to FLOOR in the hour column. You don’t need to change that one because you have a minutes column to handle the fractions of an hour. Only the minutes column should have a CEILING because you don’t have a seconds column to handle the fractions of a second.

1 Like
#99

Dude!!! You are truly amazing!!!

That was it :slight_smile:
Thank-you so so much!

1 Like
#100

I know you already have a solution from @Jeff_Hager, but thought I’d just chip in show you how I do this (this is something I actually do quite often).

Screen Shot 2021-03-17 at 8.29.14 PM
Screen Shot 2021-03-17 at 8.29.14 PM1814×292 84.8 KB

Running through the columns from left to right:

  • mth-duration-mins: calculates the duration in minutes. (end-start)*1440
  • mth-whole-hours: calculates the whole number of hours from the previous column. FLOOR(dur/60)
  • mth-minutes: calculates the “remaining” minutes component of the duration. MOD(dur,60)
  • ite-hrhrs & ite-minmins: two if-then-else columns that provide correct pluralisation.
  • tp-duration: template column that provides the final result.

NB. The mth-duration-mins is a bit redundant, but I generally keep it to make the whole sequence a bit easier to follow.

6 Likes