Calculate Revert Time

S

surevyas1984

New Member
Joined
Nov 17, 2023
Messages
8
Office Version
  1. 365
Platform
  1. 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".

UserDateBkg No.BLNo.TaskLocationReceived Date & TimeSent DATE & TIMETat (In Hours)
Rajesh Wagh06-08-2023VASDXB2300960VASJEACOK010709BLAEJEA06-08-2023 13:2306-08-2023 13:230:00
Krishna Mudaliyar16-08-2023VASDXB2300980VASJEANSA010746BLAEJEA15-08-2023 19:3816-08-2023 09:540:00
Krishna Mudaliyar19-08-2023BALDXB2300037BLTJEAKHI010753BLAEJEA19-08-2023 12:2619-08-2023 12:530:00
Krishna Mudaliyar26-08-2023DXBMT2308169GMAEJEAIXY010787Empty BLAEJEA26-08-2023 09:5926-08-2023 10:310: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
OccasionDateWork start time (in)07:00 AMMon-Fri
Holiday109 January 2023Work end time (Out)05:00 PM
Holiday2
Holiday3Work start time (Sin)08:00 AMSat & Sun
Holiday4Work end time (Sout)12:30 PM

Looking forward for your support :)
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
How about this?:

Calculate Revert Time.xlsx
ABCDEFGHIJKLM
1UserDateBkg No.BLNo.TaskLocationReceived Date & TimeSent DATE & TIMETat (In Hours)Work starting hourWork ending hour
2Rajesh Wagh06/08/2023VASDXB2300960VASJEACOK010709BLAEJEA06/08/2023 dom 12:2507/08/2023 lun 08:001:05Mon-Fri07:0017:00
3Krishna Mudaliyar16/08/2023VASDXB2300980VASJEANSA010746BLAEJEA15/08/2023 mar 19:3816/08/2023 mié 09:542:54Sat-Sun08:0012:30
4Krishna Mudaliyar19/08/2023BALDXB2300037BLTJEAKHI010753BLAEJEA19/08/2023 sáb 12:2619/08/2023 sáb 12:530:04
5Krishna Mudaliyar26/08/2023DXBMT2308169GMAEJEAIXY010787Empty BLAEJEA26/08/2023 sáb 09:5926/08/2023 sáb 10:310:32Holidays
631/12/2023 dom 12:0002/01/2024 mar 09:002:3001/01/2024
731/12/2023 dom 12:0008/01/2024 lun 09:0047:0006/01/2024
Hoja1
Cell Formulas
RangeFormula
I2:I7I2=IFERROR(LET(t,H2-G2, n_min,ROUND(t*24*60,0), m,SEQUENCE(n_min,,G2,TIME(0,1,0)), workingMinute_Holiday,DATE(YEAR(m),MONTH(m),DAY(m))=WORKDAY.INTL(m-1,1,"0000000",Holidays), h,m-INT(m), workingMinute_Hour, IF(WEEKDAY(m,2)<6,(h>=$L$2)*(h<$M$2),(h>=$L$3)*(h<$M$3)), SUM(workingMinute_Holiday*workingMinute_Hour)/24/60 ),0)
Named Ranges
NameRefers ToCells
Holidays=Hoja1!$K$6:$K$22I2:I7
 
Upvote 0
Dear felixstraube,
I would appreciate if you could help me with what name have to defined so that i can check whether it is meeting the requirement. as I dont have an access to XL2BB
 
Upvote 0
Hi.
Just copy with this button:
copybutton.png

and paste into a new sheet in cell A1.


But anyways for the formula in I2 you ranges are here as you can see in the formula. The only named range as you had is "Holidays" in K6:K22.
Calculate Revert Time.xlsx
GHIJKLM
1Received Date & TimeSent DATE & TIMETat (In Hours)Work starting hourWork ending hour
206/08/2023 do 12:2507/08/2023 lu 08:001:05Mon-Fri7:0017:00
315/08/2023 ma 19:3816/08/2023 mi 09:542:54Sat-Sun8:0012:30
419/08/2023 sá 12:2619/08/2023 sá 12:530:04
526/08/2023 sá 09:5926/08/2023 sá 10:310:32Holidays
631/12/2023 do 12:0002/01/2024 ma 09:002:3001/01/2024
731/12/2023 do 12:0008/01/2024 lu 09:0047:0006/01/2024
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Hoja1
Cell Formulas
RangeFormula
I2:I7I2=IFERROR(LET(t,H2-G2, n_min,ROUND(t*24*60,0), m,SEQUENCE(n_min,,G2,TIME(0,1,0)), workingMinute_Holiday,DATE(YEAR(m),MONTH(m),DAY(m))=WORKDAY.INTL(m-1,1,"0000000",Holidays), h,m-INT(m), workingMinute_Hour, IF(WEEKDAY(m,2)<6,(h>=$L$2)*(h<$M$2),(h>=$L$3)*(h<$M$3)), SUM(workingMinute_Holiday*workingMinute_Hour)/24/60 ),0)
Named Ranges
NameRefers ToCells
Holidays=Hoja1!$K$6:$K$22I2:I7
 
Upvote 1
Solution
Dear felixstraube,
Thank you for this formula, I have checked the file and formula is giving me the required result It is calculating Sat and Sun working hours as well with in and out time defined :), Also It is excluding holiday date as well.. that is super.
Once again Thank you

Regards
Suresh Vyas
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Hours functions
Replies
4
Views
569
jkpieterse
jkpieterse
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
S
  • Question Question
Calculate Turn Around Time 7 days working excluding Holiday and in Working Hours
Replies
3
Views
786
surevyas1984
S
R
  • Solved
Adding Hours & Minutes to Dates & Times w/in Working Hours
2
Replies
11
Views
670
Asbestos_Jen
Asbestos_Jen
J
  • Question Question
Formula for calculating business hours between 2 date/times
Replies
1
Views
2K
vladimiratanasiu
V

Forum statistics

Threads
1,224,815
Messages
6,181,136
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top