time interval

[#1]

Hi all,

I have a problem that is starting to look a little impossible 😞

I have a table box that shows a list of activities by time  :

First name	Last Name	Time	Date	Activity
John	Doe	08:57:27	21/01/2013	Arrived at Work
John	Doe	08:58:57	21/01/2013	Did something 0
John	Doe	08:59:24	21/01/2013	Did something 1
John	Doe	09:01:19	21/01/2013	Did something 2
John	Doe	09:05:31	21/01/2013	Lunch Start
John	Doe	09:49:41	21/01/2013	Did something 4
John	Doe	09:53:44	21/01/2013	Lunch Finish
John	Doe	10:43:45	21/01/2013	Did something 6
John	Doe	10:48:23	21/01/2013	Did something 7
John	Doe	12:23:40	21/01/2013	Did something 8
John	Doe	12:27:07	21/01/2013	Went Home

I need to find how long the person was on lunch.

The interval between Activity 'Lunch Start' and 'Lunch Finish'

It would be great if i could display the time spent on lunch in a seperate text box.

Any Ideas ??

Thanks

Martin

[Nicole-Smith]

Check attached example.

[swuehl]

You want just a text box for John?

Maybe like this:

='Lunch time John spent: ' & interval(sum(aggr(

only({<Activity= {'Lunch Finish'}, [Last Name] = {Doe},[First Name]= {John}>} Time) -

only({<Activity= {'Lunch Start'}, [Last Name] = {Doe},[First Name]= {John}>} Time)

, Date)))

which takes care for multiple lunch breaks on multiple Dates that needs to be summed. (Or just

='Lunch time John spent: ' & interval(

only({<Activity= {'Lunch Finish'}, [Last Name] = {Doe},[First Name]= {John}>} Time) -

only({<Activity= {'Lunch Start'}, [Last Name] = {Doe},[First Name]= {John}>} Time)

)

if you don't need to take care of that).

See also attached.

Regards,

Stefan

[jagan]

Hi,

Try this

=TimeStamp(Timestamp#(max({<Activity={'Lunch Finish'}>} Date) & ' ' &max({<Activity={'Lunch Finish'}>} Time), 'dd/MM/yyyy hh:mm:ss') -

Timestamp#(max({<Activity={'Lunch Start'}>} Date) & ' ' &max({<Activity={'Lunch Start'}>} Time), 'dd/MM/yyyy hh:mm:ss'), 'hh:mm')

Hope this helps you

Regards,

Jagan.

[#5]

Hi,

Thanks for your reply !

It works great appart from when the data has multiple lunch times :

First name	Last Name	Time	Date	Activity
John	Doe	08:57:27	21/01/2013	Arrived at Work
John	Doe	08:58:57	21/01/2013	Did something 0
John	Doe	08:59:24	21/01/2013	Did something 1
John	Doe	09:01:19	21/01/2013	Did something 2
John	Doe	09:05:31	21/01/2013	Lunch Start
John	Doe	09:49:41	21/01/2013	Did something 4
John	Doe	09:53:44	21/01/2013	Lunch Finish
John	Doe	10:43:45	21/01/2013	Did something 6
John	Doe	10:44:00	21/01/2013	Lunch Start
John	Doe	10:48:23	21/01/2013	Did something 7
John	Doe	12:00:00	21/01/2013	Lunch Finish
John	Doe	12:23:40	21/01/2013	Did something 8
John	Doe	12:27:07	21/01/2013	Went Home

Then i just get 00:00:00

If you have any ideas on how to fix this it would be fantastic !!

Thanks for all your help 🙂

Martin

[swuehl]

That's because there is no unambiguous answer for the lunch timestamps when evaluating the suggested only() expression, looking at that given date.

Maybe you can calculate the lunch time intervals already in the script, please check attached sample.