Skip to main content

Need a formula for duration in 1/10ths of an hour

  • February 26, 2019
  • 3 replies
  • 47 views
B

Im not sure if a nested IF/AND formula is the way to go or if to round it. But when I try to write the formula I get an error message.

Im trying to convert time into 1/10th increments of an hour for billing and account for rounding up or down. For example:

Less than 9 minutes = 0.1 of an hour (6 minutes)
9 minutes to 15 minutes = 0.2 of an hour (12 minutes)
15 minutes to 21 minutes = 0.3 of an hour (18 minutes)
And so on…

I need to write a formula that will read the duration (hh:mm) and round up or down to the nearest 1/10th increment in minutes.

Any help would be greatly appreciated!

3 replies

S

I think i may have figured something out for you, I am not sure if this is what you are looking for or not BUT i spent the last couple hours trying to figure out how to do this and i may have a solution. If you want to check it out, just click the link to the base i have for this below.

You should be able to see the formulas on how i did everything but if you have any questions just ask. I have the minutes converted to 100s instead of 60s because of how one of the formulas worked and used an IF statement after that. The last column (Total Hours Worked) should be the output you need.

If anyone else sees a better way to do this I am open to feedback.

W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • February 28, 2019

Since durations are stored as seconds, you can convert durations to minutes by dividing by 60, and durations to hours by dividing by 3600. Depending on how you want to treat increments of 0.05 hours, you can surround the calculation with ROUND(), ROUNDUP(), or ROUNDDOWN(). The following shows representative values for

ROUND({Duration}/3600,1)
ROUNDUP({Duration}/3600,1)
ROUNDDOWN({Duration}/3600,1)

respectively.

S
B
  • Blair_Martin
  • Author
  • New Participant
  • March 5, 2019

Since durations are stored as seconds, you can convert durations to minutes by dividing by 60, and durations to hours by dividing by 3600. Depending on how you want to treat increments of 0.05 hours, you can surround the calculation with ROUND(), ROUNDUP(), or ROUNDDOWN(). The following shows representative values for

ROUND({Duration}/3600,1)
ROUNDUP({Duration}/3600,1)
ROUNDDOWN({Duration}/3600,1)

respectively.


Thanks for the Replies!

The formula I ended up using was

IF( {Time} <= 540, ‘0:06’, INT( ((ROUND( (({Time}/3600) / 0.1), 0) * 0.1)*3600)/3600 )& ‘:’& IF( MOD( ((ROUND( (({Time}/3600) / 0.1), 0) * 0.1)*3600)/60, 60 )<10, ‘0’ )& ROUND( MOD( ((ROUND( (({Time}/3600) / 0.1), 0) * 0.1)*3600)/60, 60 ),0))

I ended up having to modify it a little bit in the process because we never wanted the result to be “0”.

W
Terms & ConditionsAccessibility statement