Export to Excel - time format

Hi,
I have a field with time in minutes. I have converted it to hh:mm:ss ->
= String.Format("{0:HH:mm:ss}",CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value))
and set format properties to hh:mm:ss
It looks just like it should, but when exporting to excel, the format type
is General.
I need to add these field, so they can not be General.
After double-clicking the field, they are Custom - hh:mm:ss.
I can not ask my users to double-click some 1000 fields each time they take
out the report...
Any advice?
ThanksUse the .ToString method instead of String.Format
=CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value).ToString("HH:mm:ss")
Cheers!
:-)
Ben Sullins
www.kingofthegreens.com
"Sissel" wrote:
> Hi,
> I have a field with time in minutes. I have converted it to hh:mm:ss ->
> => String.Format("{0:HH:mm:ss}",CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value))
> and set format properties to hh:mm:ss
> It looks just like it should, but when exporting to excel, the format type
> is General.
> I need to add these field, so they can not be General.
> After double-clicking the field, they are Custom - hh:mm:ss.
> I can not ask my users to double-click some 1000 fields each time they take
> out the report...
> Any advice?
> Thanks|||Hi Ben,
Thanks for your input, but unfortunately it is the same result.
I have been in contact with Microsoft, and they say I have to get the field
in date-format from the database (Oracle), then set time format in the report.
This is my solution:
to_date(TO_CHAR(TRUNC(sysdate)+ NUMTODSINTERVAL(psa.setup_time,'minute'),
'HH24:MI:SS'), 'HH24:MI:SS')
Sissel
"Ben Sullins" wrote:
> Use the .ToString method instead of String.Format
> =CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value).ToString("HH:mm:ss")
> Cheers!
> :-)
> Ben Sullins
> www.kingofthegreens.com
> "Sissel" wrote:
> > Hi,
> >
> > I have a field with time in minutes. I have converted it to hh:mm:ss ->
> > => > String.Format("{0:HH:mm:ss}",CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value))
> > and set format properties to hh:mm:ss
> >
> > It looks just like it should, but when exporting to excel, the format type
> > is General.
> > I need to add these field, so they can not be General.
> > After double-clicking the field, they are Custom - hh:mm:ss.
> > I can not ask my users to double-click some 1000 fields each time they take
> > out the report...
> >
> > Any advice?
> >
> > Thanks|||Hi again Ben,
I was too quick (it is Monday morning..)
This works
CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value)
and textbox properties Format = HH:mm:ss
It was the CDate function I was looking for (and MS support didn't know
about...)
Thank you very much :-)
"Ben Sullins" wrote:
> Use the .ToString method instead of String.Format
> =CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value).ToString("HH:mm:ss")
> Cheers!
> :-)
> Ben Sullins
> www.kingofthegreens.com
> "Sissel" wrote:
> > Hi,
> >
> > I have a field with time in minutes. I have converted it to hh:mm:ss ->
> > => > String.Format("{0:HH:mm:ss}",CDate("0:0:0").AddMinutes(Fields!SETUP_TIME.Value))
> > and set format properties to hh:mm:ss
> >
> > It looks just like it should, but when exporting to excel, the format type
> > is General.
> > I need to add these field, so they can not be General.
> > After double-clicking the field, they are Custom - hh:mm:ss.
> > I can not ask my users to double-click some 1000 fields each time they take
> > out the report...
> >
> > Any advice?
> >
> > Thanks