šŸ†• Date-time Math

General
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
101

This is a simple approach too.

What do your if columns look like?

102

Screen Shot 2021-03-17 at 9.08.48 PM
Screen Shot 2021-03-17 at 9.08.48 PM572Ɨ666 21.3 KB

The one for min/mins is basically identical.

1 Like