สูตรย้าวยาวใช้หาระยะเวลาทำงานยาวๆ

มาลองคิดกันว่าจะปรับสูตรให้สั้นลงได้อย่างไรบ้าง

หากกำหนดให้งานเสร็จเวลา 16:30 โดยให้ใช้เวลาทำงาน 7 ชั่วโมง และในระหว่างวันมีช่วงเวลากะแบ่งเป็นช่วงๆ ถามว่า ต้องเริ่มงานกี่โมง

ผมสร้างสูตรไว้ 2 รุ่น

สูตรรุ่นที่สอง สั้นลง แต่ก็ยังยาวอยู่

{=PullStop – PullWrkHours – SUM( ( (PullStop – PullWrkHours -SUM( ( (PullStop – PullWrkHours)From)(IF( (PullStop>=From)(PullStop<=To), PullStop, To ) – From)))(PullStop>From)( IF( (PullStop>=From)*(PullStop<=To), PullStop, To) – From) )}

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWODMwdVpmYS1vbVE

======

สูตรรุ่นแรก ยาวมากๆๆๆๆ ตามนี้ครับ

=INDEX(Start,COUNTIF(Total_Hours,”<=”&(INDEX(Total_Hours,MATCH(VLOOKUP(Stop_Time,Start,1),Start,1),1)-INDEX(Hours,MATCH(VLOOKUP(Stop_Time,Start,1),Start,1),1)+((Stop_Time-VLOOKUP(Stop_Time,Start,1))24)-Lead_Time))+1,1)+((INDEX(Total_Hours,MATCH(VLOOKUP(Stop_Time,Start,1),Start,1),1)-INDEX(Hours,MATCH(VLOOKUP(Stop_Time,Start,1),Start,1),1)+((Stop_Time-VLOOKUP(Stop_Time,Start,1))24)-Lead_Time)-(INDEX(Total_Hours,COUNTIF(Total_Hours,”<=”&(INDEX(Total_Hours,MATCH(VLOOKUP(Stop_Time,Start,1),Start,1),1)-INDEX(Hours,MATCH(VLOOKUP(Stop_Time,Start,1),Start,1),1)+((Stop_Time-VLOOKUP(Stop_Time,Start,1))24)-Lead_Time))+1,1)-INDEX(Hours,COUNTIF(Total_Hours,”<=”&(INDEX(Total_Hours,MATCH(VLOOKUP(Stop_Time,Start,1),Start,1),1)-INDEX(Hours,MATCH(VLOOKUP(Stop_Time,Start,1),Start,1),1)+((Stop_Time-VLOOKUP(Stop_Time,Start,1))24)-Lead_Time))+1,1)))/24

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWSl9EQkFzaG1QSGc