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

[aveeeeeee7en]

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

[tresesco]

NetWorkDays() could be helpful here.

[aveeeeeee7en]

Hi Tresesco Sir could you help me on Script.

[tresesco]

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.

[aveeeeeee7en]

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

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

LEFT JOIN

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

Main_Table:
LOAD *,
'' AS Junk
Resident Table1;
Drop Table Table1;

Also, See this:

[aveeeeeee7en]

Any idea of doing this???