Calculate Time format with Number

[#1]

Hello everyone,

                   I got the following question: i need to calculate "Time (in hours)"*"Quantity_per_hour(number)". I am importing data from SQL server, where the field for time is in format "time". Field for quantity is from excel file and its in format number. Is there a way to convert time to number format ? I am doing my calculation as expression.

     My idea: for example I got 02:00 hours. I need to convert it to 2.00 decimal so i can multiply it with the quantity.

All ideas will help .

Best regards

Daniel.

[senpradip007]

Try like

Time([Your Field], 'h')

[jagan]

Hi,

Try like this

=Num(SubField('02:30', ':',1))  +  (Num(SubField('02:30', ':',2))/60)

For 2:30 you will get  2.5.

For 2:00 you will get 2.0.

Regards,

Jagan.

[er_mohit]

hi

you might be use replace function

like as above example

num(replace(Timefield,':','.'),'#,#0.00') as Decimal field

here it replace your 02:00 to 2:00

Try this in text object

=num(replace('02:00',':','.'),'#,#0.00')

hope it helps

[jagan]

Hi,

Replace '02:30' with your timefield name.

Regards,

Jagan.

[#6]

Main problem at this point is how to convert TIME format loaded from data base to number:

1. This can happen when data is loading, maybe: num("FIELDNAME") as ........ <- this is not working corrent cause when i give format Number on this data in expression in pivot table it give some different number

2. This can happen in the expression when calculating is done

I got the following code in table field for expression:

=(if([Start time]<=[Break Start Time],if([End Time]>=[Break End Time],[End Time_v2]-[Start time_v2]-([Break End Time_v2]-[Break Start Time_v2]),[End Time_v2]-[Start time_v2]),[End Time_v2]-[Start time_v2])*someNumber (number format or integer)

The output is in format Time at this point and when i convert it to Number from menu it gives wrong data.

Tried to load data like this 2 variants but it didnt help:

Num(time("Starting Time")) as [Start time_v2],

Num(time("Ending Time")) as [End Time_v2],

=====================================

num("Starting Time") as [Start time_v2],

num("Ending Time") as [End Time_v2],

Main goal is to convert that 01:00 to 1.00 number format so i can multiply it with some data in number format and display i correct answer in this field.

[jagan]

Hi,

Try like this in script

LOAD

*,

Num(SubField([Starting Time], ':',1))  +  (Num(SubField([Starting Time], ':',2))/60) AS StartTimeNum,

Num(SubField([End Time], ':',1))  +  (Num(SubField([End Time], ':',2))/60) AS EndTimeNum;

SELECT

*

FROM TableName;

[MarcoWedel]

Interval#(Timefield, 'hh:mm')*24

Hope this helps.

Regards

Marco

[#9]

Actualy tried it already:

Num(SubField("Starting Time", ':',1)) +  (Num(SubField("Starting Time", ':',2))/60) as [Start time_decimal],

Num(SubField("Starting Time", ':',1)) +  (Num(SubField("Starting Time", ':',2))/60) as [End time_decimal],

Num(SubField("Break Start Time", ':',1))  +  (Num(SubField("Break Start Time", ':',2))/60) as  [Break Start Time_decimal],

Num(SubField("Break End Time", ':',1))  +  (Num(SubField("Break End Time", ':',2))/60) as [Break End Time_decimal];

Output in pivot table when i do my calculation as expression:

=(if([Start time]<=[Break Start Time],if([End Time]>=[Break End Time],[End time_decimal]-[Start time_decimal]-([Break End time_decimal]-[Break Start time_decimal]),[End time_decimal]-[Start time_decimal]),[End time_decimal]-[Start time_decimal]))

is "-" for each record.

[jagan]

Hi,

Check the

=(if([Start time]<=[Break Start Time],

          if([End Time]>=[Break End Time],

          [End time_decimal]-[Start time_decimal]-([Break End time_decimal]-[Break Start time_decimal]),

          [End time_decimal]-[Start time_decimal]),

  [End time_decimal]-[Start time_decimal]))

You expression seems to be ok, but check the data.

Are you getting the time values in this fields [Start time_decimal], [End time_decimal] ?

Regards,

Jagan.