Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try like
Time([Your Field], 'h')
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.
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
Hi,
Replace '02:30' with your timefield name.
Regards,
Jagan.
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.
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;
Interval#(Timefield, 'hh:mm')*24
Hope this helps.
Regards
Marco
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.
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.