BusinessObjects Board

Convert minutes to Weeks/Days/Hours/Minutes

building reports webintelligence legacy
#1

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 :uk: (BOB member since 2003-01-30)

Date display in days:hh:mm format
Formula for separate time from seconds needed
Can i format seconds to dd:hh:mm:ss format
converting minutes to hours and minutes
Calculate Delivery time in DD:HH:MM
Display number in a time format
Convert Seconds to Days, hours and minutes
Display time duratoin in hours over 24
hours or minutes between 2 date objects
#2

Hi,

I would suggest you to create a new universe object that would calculate this for you.

However if you need a report-level solution then the conversion from the number of minutes to days, hours and minutes should use this formula:

=FormatNumber(Floor([number of minutes] /1440) ;"0") + " days, " +
 FormatNumber(Floor(Mod([number of minutes] ;1440)/60) ;"0") + " hours, " +
 FormatNumber(Mod(Mod([number of minutes] ;1440) ;60) ;"0") + " minutes"

It would be more complicated formula (if possible at all) for the conversion that would display also the number of months/years - the reason is the varying number of days in months. So without knowing either the start date or the end date you are not able to say whether 4000 minutes are from February to April or from March to May…

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

#3

Ah ha,

Cheers, I assume ‘Floor’ only takes the ‘whole numbers’ shall we say and then you used ‘Mod’, to get the remainder?

Thanks,

Stuart

stuartgmilton :uk: (BOB member since 2003-01-30)

#4

That’s what is used in the above formula - Floor() and Mod(). :slight_smile:

Or do you mean something else?

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

#5

Sorry, I think I was thinking aloud.

Thanks you’ve made my life easier and the client dead happy!

stuartgmilton :uk: (BOB member since 2003-01-30)

#6

OK, I got what you meant :slight_smile:
Yes, Floor() is for getting the ‘whole’ numbers and Mod() for the remainders.

You are welcome 8)

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

#7

Hi

I am new crystal reports…I have got the same problem…want to convert the days in to days:hours:minutes format.I saw your formula.But i could not understand.Please can you explain me how to use the formula in crystal reports.

Thanks in Advance.

Regards,
Pavan.

pavancrd (BOB member since 2011-10-23)