converting minutes to hours and minutes

[system]

Hi,

I have a measure that produces results in minutes. I need to convert the results to hours and minutes. If I simply divide by 60 I get the hours but the minutes are in a decimal fraction rather than a portion of 60.

I searched but couldn’t find anything on this forum. In excel forums I found a couple of methods but they did not convert to BO. Does anyone have a formula that works in BO?

Depending on the Universe I work in I use different versions of BO, so would prefer something that works in earlier versions if possible.

eg =INT([Visit Time]/60)+":" +Mod([Visit Time];60) would work, but the version of BO that I am using does not have INT available. If I remove that part of the formula I get a result like 7.82:49 for 469 minutes. 469 translates to 7.82 if divided by 60 and 7:49 if converted to hours and minutes. I want a result that returns 7:49.

Thanks
capri

---

capri (BOB member since 2003-06-20)

[system]

Hi,

The formula mentioned in this old topic can help you:

Convert minutes to Weeks/Days/Hours/Minutes

Hey guys, I have a column in the SQL Server database which tells me the number of minutes between 2 dates. Could anyone tell me how to convert that to the format; 3 Years, 2 Months, 1 Week, 2 Days, 13 Hours, 52 Minutes. I get lost very quickly. Cheers, Stuart stuartgmilton (BOB member since 2003-01-30)

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thanks for link Marek,

I tried the formula
=Floor([Visit Time]/60)+":" +Mod([Visit Time];60)
and it works fine with the exception that 7 minutes comes out as 0:7 and 1 hour and 7 minutes comes out as 1:7. Do you know if there is a method to get the formula to return 2 decimal places eg 0:07 or 1:07?

With 7 it is fairly obvious but if the minutes we 5 instead of 7, I can see where some users exporting to Excel might end up with 0.50 or 1.50 instead of 0.05 & 1.05.

capri

I noticed it’s rather warm in Toronto today (my home town) while I am feeling the cold in Australia with no central heating. Hope you enjoy the warm weather.

---

capri (BOB member since 2003-06-20)

[system]

Hi,

Then try this modification of your formula:

=FormatNumber(Floor([Visit Time]/60);"00")+":"+FormatNumber(Mod([Visit Time];60);"00")

If you don’t like hours to be always 2 digits, then this should work:

=FormatNumber(Floor([Visit Time]/60);"0")+":"+FormatNumber(Mod([Visit Time];60);"00")

Yeah, it’s very hot these days (and nights as well) here in Toronto. I even appreciate time spent in the cold office

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thanks heaps Marek.

It works just the way I want. Aprreciate the help.

capri

---

capri (BOB member since 2003-06-20)

[aarancon]

Hi. I used Spanish versión of the program, what is the Floor function in spanish?

Thanks