Convert Time to number

[jood_ahmad]

Hello Dear,

how can i convert hours to number(integer) like:

i have this total working hours for my team in one day 29:14:06 H and i need to get the cost for them the normal calculation that i do it in excel is:

total working hours ((29*60)+(14'mm'))/60 so i will get this number 29.233 after that i do 29.233 * 24.5' Price per hour' / 19 '#of Orders'

29.233 * 24.5 / 19 = 37.69 .

so to summarize it : i have to column 1. working hours. 2. # of Orders. and the price is fix 24.5 .

i try to use Interval function and calculate it but does not give me the correct number.

any help pleas how con do this.

[antoniotiman]

Hi,

try

(SubField(YouTime,':',1)+SubField(YourTime,':',2)/60)*Price/NrOrder

Regards,

Antonio

[sunny_talwar]

This might also work:

Floor(Interval#(SubField('29:14:06 H', ' ', 1), 'h:mm:ss'), 1/1440)*24

*

24.5

/

[# of Order]

[awhitfield]

Hi Ahmad,

Please check out the attached:

Script sample:

Data_1:

Load * Inline

[

Work_Hrs, Price, #Orders

29:14:09, 24.5, 19

];

Data_2:

Load

Num(Sum(SubField(Work_Hrs,':',1)* 60 + SubField(Work_Hrs,':',2))/60,'#,##.00') as Decimal_Hours

Resident Data_1;

Chart Expression:

=Sum(Decimal_Hours * Price) / #Orders

[jood_ahmad]

HI Sunny,

can you explain the formula please.

why we use this and what is the 1/1440 ?

[sunny_talwar · Accepted solution]

So interval was used to interpret your time and then I am using Floor is used to Floor your timestamp to the nearest minute. (1/1440 where 1440 is made of 24*60 where 24 is the number of hours and 60 is number of minutes) and finally multiplying with 24 to convert them into hours.

Does it make sense?