Calculating time in script

[kris_vliegen]

Hi All,

I'm having a problem with calculating time.

In our planning-tooldatabase I have 2 time fields. (Planned time and the time spend)

Those are numeric for example if you get 16 hours in the database we zee 1600, if you get 2h and a half you se 230 in the database.

I can create a timefield in the script by using those 2 

Interval((left(num(PlannedTime,'0000'),2) * 60 + Mid(num(PlannedTime,'0000'),3,2))/24/60,'hh:mm') as PlannedTimehhmm,
Time((left(num(PlannedTime,'0000'),2) * 60 + Mid(num(PlannedTime,'0000'),3,2))/24/60,'hh:mm') as PlannedTimeTijdhhmmTime

And the same for the SpendTime.

But how can I subtract those fields so I get the time that is available for this order?

Regards,

Kris

[tresesco]

Could you provide a sample data and expected output for that?

[kris_vliegen]

Hi,

attached you can find an example.

Hope this is enough.

[tresesco]

Sorry, I am lost in your sample. Could you tell me what is expected output for two values you mentioned in op, 1600  (16 hours) and 230( 2 hours 30 mins)? 13 hours 30 mins right. 

 You could try like:

=interval(Time#(1600, 'hhmm')-Time#(230, 'hmm'), 'hh:mm')

[kris_vliegen]

In this example the planned time is 8 h ( 08:00)

and the person has spend once 20 min and once 30 min -> together 00:50

So the value I would like to see as difference is 07:10

[tresesco · Accepted solution]

Well, try this:

=interval(PlannedTimehhmm-Sum(Spendtime.URSpendTimehhmm), 'hh:mm')