5 Replies Latest reply: Oct 6, 2014 2:48 PM by AVIRAL NAG

Calculate Time Interval on the Basis of Logic b/w 2 Timestamps

Hi Community

I am referring below thread to fulfill my requirement:

Re: Calculate hours between two Date/Time strings

But got stuck inbetween my Logic.

Requirement:

1) I have an Excel which has Start Time & End Time against Part Number. So, for these cases I have to  calculateTime Interval on the basis of their defined Window. For some Part Numbers, it will work from Mon-sat or Mon-Fri or All 7 Days  as per the defined window.

Also, need to exclude Holidays.

2) Those cases, which are not in excel calculate their Time Interval on the basis of Flags:

If Flag is CDE than consider Mon-Sat - 9:30 - 18:00

If Flag is AB than consider Mon-Fri - 9:00 - 17:30

Logic:

Desired Output:

Also,see the Attachments.

Regards

Av7eN

• Re: Calculate Time Interval on the Basis of Logic b/w 2 Timestamps

Hi Tresesco Sir could you help me on Script.

• Re: Calculate Time Interval on the Basis of Logic b/w 2 Timestamps

I don't find date in your input data. Your desired output excel sheet is not linked to windowmain sheet too. Only the logic(based on flag) of sat/sun exclusion is clear to me. Try to logically link the sheets and explain, and may be a clue on how you get the date could help a bit.

• Re: Calculate Time Interval on the Basis of Logic b/w 2 Timestamps

Here, Number is the Primary Key between 2 tables WindowMain & Desired Output:

Table1:
CompanyName,
[Part Number],
Flag,
Mon,
Tue,
Wed,
Thu,
Fri,
Sat,
Sun
FROM
[Window and Output.xlsx]
(ooxml, embedded labels, table is WindowMain);

LEFT JOIN

CreationTime,
OutTime
FROM
[Window and Output.xlsx]
(ooxml, embedded labels, table is [Desired Output]);

Main_Table: