EVENT LOOKUP WITH TIME CALCULATIONS

[pucha]

I am trying to find the event occurred as per given range of time.

Suppose the time range is 09:14:28 to 09:53:22 want to find the event from the tables and also the time passed in the certain event (minutes and seconds)

I have uploaded the excel file. Pl have time to look.


Thankyou

Pucha

 

[Luke M]

Chandoo has several free tutorials and examples on creating Gantt charts, just takes a little bit of charting magic.
https://chandoo.org/wp/gantt-charts-project-management/

 

[pucha]

Thank you for your response. May be I had drawn the presentation like gantt-chart. but hope it will not be possible with that. As the program that I am looking is with "Minutes and seconds". Hope will you check my Excel file once again.


Thanks Sir,

 

[Luke M]

but hope it will not be possible with that

Sure. Doing anything else would fulfill the requirement of not being a Gantt chart. You can also make the resolution on your chart however fine (seconds, milliseconds, etc.) that you want.

When you figure out what you'd like to display other than a Gantt chart, let us know, and we can try to help.

 

[pucha]

Thank you,
I had not tried Gantt Chart before nor do I know how to do. If you think my Problem can be solved by it then pl help.

 

[pucha]

Thank you.

 

[Peter Bartholomew]

@pucha The attached will be only of limited value to you because neither the Excel version nor the programming style are things you are likely to use. Nevertheless, the expression
(Table1[END TIME]>=start)*(Table1[START TIME]<=end)
that forms part of the formula that lists the events from Table1
= FILTER(Table1[EVENTS], (Table1[END TIME]>=start)*(Table1[START TIME]<=end), "Not found")
provides a criterion for determining whether a record should be included or not. FILTER is not available to you, so you will need to return a record number and then use SMALL to pick out the required records from the FALSE matches.

To calculate the duration you need the later value of the event start and the interval start and subtract it from the earlier value taken from the event end and the interval end.
= IF(Table1[END TIME]<end, Table1[END TIME], end)
- IF(Table1[START TIME]>start, Table1[START TIME], start)

A bar chart with either stacked (start & duration) or overlapping bars (start & end) can be used to illustrate the events.

 

[vletm]

pucha
Modify needed START/END times with T3/U3 -cells and You'll see
... almost 'everything' with this sample

 

[pucha]

THANK YOU FOR THE HELP. WORKING GOOD.