surevyas1984
New Member
- Joined
- Nov 17, 2023
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
Team,
Please help me with the formula, formula is entered in TAT (in hours)
Mon to Fri from 7AM to 5PM and Sat and Sun 8AM to 12:30PM. There is also holiday list. TAT (In H:MM) . Formula should calculate difference between Received Date & Time & Sent Time. It should calculate time of working hours only and should not consider non-working hours.
In case Holiday is updated in Public Holidays & work hours under column B then TAT should not calculate on that day.
Shift time and Holiday is updated in sheet "Public Holidays & work hours".
=SUM((NETWORKDAYS.INTL(H14,I14,1,holid)-1)*(out-in)+IF(NETWORKDAYS.INTL(I14,I14,1,holid),MEDIAN(MOD(I14,1),out,in),out)-MEDIAN(NETWORKDAYS.INTL(H14,H14,1,holid)*MOD(H14,1),out,in),(NETWORKDAYS.INTL(H14,I14,"1111111",holid)-1)*(sout-sin)+IF(NETWORKDAYS.INTL(I14,I14,"1111111",holid),MEDIAN(MOD(I14,1),sout,sin),sout)-MEDIAN(NETWORKDAYS.INTL(H14,H14,"1111111",holid)*MOD(H14,1),sout,sin))
Public Holidays & work hours Sheet
Looking forward for your support
Please help me with the formula, formula is entered in TAT (in hours)
Mon to Fri from 7AM to 5PM and Sat and Sun 8AM to 12:30PM. There is also holiday list. TAT (In H:MM) . Formula should calculate difference between Received Date & Time & Sent Time. It should calculate time of working hours only and should not consider non-working hours.
In case Holiday is updated in Public Holidays & work hours under column B then TAT should not calculate on that day.
Shift time and Holiday is updated in sheet "Public Holidays & work hours".
User | Date | Bkg No. | BLNo. | Task | Location | Received Date & Time | Sent DATE & TIME | Tat (In Hours) |
Rajesh Wagh | 06-08-2023 | VASDXB2300960 | VASJEACOK010709 | BL | AEJEA | 06-08-2023 13:23 | 06-08-2023 13:23 | 0:00 |
Krishna Mudaliyar | 16-08-2023 | VASDXB2300980 | VASJEANSA010746 | BL | AEJEA | 15-08-2023 19:38 | 16-08-2023 09:54 | 0:00 |
Krishna Mudaliyar | 19-08-2023 | BALDXB2300037 | BLTJEAKHI010753 | BL | AEJEA | 19-08-2023 12:26 | 19-08-2023 12:53 | 0:00 |
Krishna Mudaliyar | 26-08-2023 | DXBMT2308169 | GMAEJEAIXY010787 | Empty BL | AEJEA | 26-08-2023 09:59 | 26-08-2023 10:31 | 0:00 |
=SUM((NETWORKDAYS.INTL(H14,I14,1,holid)-1)*(out-in)+IF(NETWORKDAYS.INTL(I14,I14,1,holid),MEDIAN(MOD(I14,1),out,in),out)-MEDIAN(NETWORKDAYS.INTL(H14,H14,1,holid)*MOD(H14,1),out,in),(NETWORKDAYS.INTL(H14,I14,"1111111",holid)-1)*(sout-sin)+IF(NETWORKDAYS.INTL(I14,I14,"1111111",holid),MEDIAN(MOD(I14,1),sout,sin),sout)-MEDIAN(NETWORKDAYS.INTL(H14,H14,"1111111",holid)*MOD(H14,1),sout,sin))
Public Holidays & work hours Sheet
Occasion | Date | Work start time (in) | 07:00 AM | Mon-Fri | |||
Holiday1 | 09 January 2023 | Work end time (Out) | 05:00 PM | ||||
Holiday2 | |||||||
Holiday3 | Work start time (Sin) | 08:00 AM | Sat & Sun | ||||
Holiday4 | Work end time (Sout) | 12:30 PM |
Looking forward for your support
