Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
NetWorkDays() could be helpful here.
Hi Tresesco Sir could you help me on Script.
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.
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:
Any idea of doing this???