How to format integer value as hours and minutes?

Question

I am developing an SSRS 2008 R2 RDL file with a textbox containing an integer field.  Currently this field holds a value with unit = minutes.  For example 65 mins.  I want to display this as:

01:05

instead of as "65".  How do I do this?  I tried selecting several of the Textbox property options, but none of them worked correctly.

---

Ryan D

Answer 1

Hi Ryan,

You would need to write an expression for achieving this in the value property. So

mething like dividing the minutes by 60 and then concatenating quotient + ':' + remainder.

---

HTH, Cheers!! Ashish Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

Answer 2

Hi Ryan,

You would need to write an expression for achieving this in the value property.

Something like - dividing the minutes value by 60 and then concatenating quotient + ':' + remainder.

---

---

HTH,
Cheers!!
Ashish
Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

Answer 3

Hi iron !

You may get the desired output using below expression ;

=Cstr(Cint(Fields!YourColumn.Value / 60)) + " : " + Cstr(Fields!YourColumn.Value Mod 60)

Note : Please put your column name in place of 'YourColumn' in my expression above.

Please let me know if this helps. Hopefully i have answered you correctly.

Thanks, Hasham Niaz

Answer 4

Ashish, this is what I tried in my T-SQL:

	G.duration / 60 as duration_hrs,
	G.duration % 60 as duration_mins,

This returns the data I want.  In my SSRS file my expression is:

=Fields!duration_hrs.Value & ":" & Fields!duration_mins.Value

But when I view this it displays as "1:5" instead of "01:05".

---

Ryan D

Answer 5

I assume I should put this expression in SSRS, correct?  I tried this expression in SSRS:

=Cstr(Cint(Fields!duration.Value/60)) + ":" + Cstr(Fields!duration.Value Mod 60)

In this case the value of duration = 65.  But it still displays as "1:5" in SSRS. 

---

Ryan D

Answer 6

I assume I should put this expression in SSRS, correct?  I tried this expression in SSRS:

=Cstr(Cint(Fields!duration.Value/60)) + ":" + Cstr(Fields!duration.Value Mod 60)

In this case the value of duration = 65.  But it still displays as "1:5" in SSRS. 

---

Ryan D

Hi Iron !

You may get the desired output using below expression in your SSRS ;

= Right("00" + Cstr(Cint(Fields!YourColumn.Value / 60)), 2) + " : " + Right( "00" + Cstr(Fields!YourColumn.Value Mod 60), 2)

Hopefully this will resolve your issue.

Please let me know if this helps. Hopefully i have answered you correctly.

Thanks, Hasham Niaz

Answer 7

No, this is giving me "#Error".  What should the TExbox Property be?  I have "Default" selected.  Not sure why it gives me error though.

---

Ryan D

Answer 8

HI !

I have tested it with Texbox property -> Number -> Default and also tested it with Textbox property -> Number -> Custom -> dd/MM/yyyy and in both cases it worked fine for me.

Lets try to hard code the expression which i use for testing;

= Right("00" + Cstr(Cint(65 / 60)), 2) + " : " + Right( "00" + Cstr(65 Mod 60), 2)

Later if this works you can replace the '65' with your column name.

Please let me know if this helps. Hopefully i have answered you correctly.

Thanks, Hasham Niaz

Answer 9

Thank you!  I don't know why it didn't work before but now it works!

---

Ryan D