18 Replies Latest reply: Feb 23, 2016 12:43 PM by Kim Goedmakers

# calculating number of minutes between incoming hour and '16:15'

Hello,

I am trying to calculate the number of minutes between an incoming timestamp e.g. '

 31-dec-14 08:05:00

and the fixed hour '16:15'

Defined some calculations in the load script:

TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,

MakeTime(16,15) as Cutoff_time,

INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,

INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as Interval_dynamic,

TIME(INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm'),'mm')) as Interval_dynamic_format

Testresult "interval static" returns what I expected (240 min), but when trying to apply the same logic to my real time in, i am unable to get the correct answer.

Any ideas on how to do this? Thanks for your help and time.

• ###### Re: calculating number of minutes between incoming hour and '16:15'

May be this:

Interval((Time#('16:15', 'hh:mm') - Frac(TimeIn)), 'hh:mm:ss') as Interval

• ###### Re: calculating number of minutes between incoming hour and '16:15'

if your field is like  31-dec-14 08:05:00

interval(maketime(16,15) - frac(date#('31-dec-14 08:05:00', 'DD-MMM-YY hh:mm:ss')))

interval(maketime(16,15) - frac(date#(field, 'DD-MMM-YY hh:mm:ss')))

• ###### Re: calculating number of minutes between incoming hour and '16:15'

Thanks for your suggestion, have modified load script as:

"Sessie - OK zaal in tijdstip"  as Inputfield,

TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,

MakeTime(16,15) as Cutoff_time,

INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,

INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as Interval_dynamic,

TIME(INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm'),'mm')) as Interval_dynamic_format,

interval(maketime(16,15) - frac(date#( "Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss'))) AS DurationInMinutes

However, the DurationInMinutes column does not show any values.

• ###### Re: calculating number of minutes between incoming hour and '16:15'

Hi,

Try like this

*,

(MakeTime(16,15) - Frac(Date#(TimeFieldName, 'DD-MMM-YY hh:mm:ss')))  * 24 * 60 AS DurationInMinutes

FROM DataSource;

Note: Replace TimeFieldName with actual field name.

Regards,

Jagan.

• ###### Re: calculating number of minutes between incoming hour and '16:15'

Thanks for your suggestion but the calculated field does not display any values... i modified the load script:

"Sessie - OK zaal in tijdstip"  as Inputfield,

TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,

MakeTime(16,15) as Cutoff_time,

INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,

INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as Interval_dynamic,

TIME(INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm'),'mm')) as Interval_dynamic_format,

(MakeTime(16,15) - Frac(Date#( "Sessie - OK zaal in tijdstip" , 'DD-MM-YYYY hh:mm:ss')))  * 24 * 60 AS DurationInMinutes

• ###### Re: calculating number of minutes between incoming hour and '16:15'

Not sure you need the DATE#

The field seems to be recognised as date time already.

• ###### Re: calculating number of minutes between incoming hour and '16:15'

Thanks for your response. Blanks are gone but i get a negative and even the absolute value is larger then expected.

• ###### Re: calculating number of minutes between incoming hour and '16:15'

does the FRAC function around maketime(16,15) help?

• ###### Re: calculating number of minutes between incoming hour and '16:15'

Thanks for your reply. Result did change but i am still not able to fully retrieve the same result as in the interval static column (negative and low value now)

• ###### Re: calculating number of minutes between incoming hour and '16:15'

Still believe that the first answers in this thread as well as Andrew's expression should work (first answers given the format code is correct or can be left out if the field is automatically interpreted as timestamp).

The last screenshot looks strange, like you've added a row number or something like this on top of a small value.

• ###### Re: calculating number of minutes between incoming hour and '16:15'

Hello. Thanks for your reply. Here is the load statement where i tried to include all the ideas :

"Sessie - OK zaal in tijdstip"  as Inputfield,

TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,

MakeTime(16,15) as Cutoff_time,

INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,

INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as ownidea,

Interval((Time#('16:15', 'hh:mm') - Frac("Sessie - OK zaal in tijdstip")), 'hh:mm:ss') as anwer1,

interval(maketime(16,15) - frac(date#( "Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss'))) as answer2,

(MakeTime(16,15) - Frac(Date#("Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss')))  * 24 * 60 as answer3,

(MakeTime(16,15) - Frac(Date("Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss')))  * 24 * 60 as answer4,

(FRAC(MAKETIME(16,15)) - FRAC("Sessie - OK zaal in tijdstip")) * 24 * 60 as answer5

The results can be seen on the screenshot. Any suggestions are still most welcome.

• ###### Re: calculating number of minutes between incoming hour and '16:15'

Can you give this a shot:

Interval((Time#('16:15', 'hh:mm') - Frac(Date#("Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss'))), 'hh:mm:ss') as anwer6,

• ###### Re: calculating number of minutes between incoming hour and '16:15'

Answer 1, 4 and 5 look quite promising. I think you may be confused by comparing first line of a list box to first line of another listbox. These lines don't need to come from the same record, I think they are just sorted numeric.

I would suggest selecting a value and then compare the created field values that are really related to each other.

• ###### Re: calculating number of minutes between incoming hour and '16:15'

Your suggestion did work. In debug-mode I uploaded only 1 record and the result does indeed make sense. Many thanks to all for your time and ideas.

• ###### Re: calculating number of minutes between incoming hour and '16:15'

Try like this

*,

(MakeTime(16,15) - (Frac(TimeFieldName)  * 24 * 60) AS DurationInMinutes

FROM DataSource;

• ###### Re: calculating number of minutes between incoming hour and '16:15'

Thanks again for your reply. Modified the script:

MakeTime(16,15) - (Frac( "Sessie - OK zaal in tijdstip" )  * 24 * 60) AS DurationInMinutes

but the results are both negative and of a high absolute value :

• ###### Re: calculating number of minutes between incoming hour and '16:15'

think the brackets might not be right - also not sure MAKETIME(16,15) is returning just a time.

perhaps try:

(FRAC(MAKETIME(16,15)) - FRAC("Sessie - OK zaal in tijdstip")) * 24 * 60

• ###### Re: calculating number of minutes between incoming hour and '16:15'

Thanks for your suggestion, have modified load script as:

"Sessie - OK zaal in tijdstip"  as Inputfield,

TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,

MakeTime(16,15) as Cutoff_time,

INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,

INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as Interval_dynamic,

TIME(INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm'),'mm')) as Interval_dynamic_format,

interval(maketime(16,15) - frac(date#( "Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss'))) AS DurationInMinutes

However, the DurationInMinutes column does not show any values.