LinkBack URL
About LinkBacks
hi team,
i need to caclculate the tat between 2 times condition is.
Supoprt Hrs: 7:00:00 AM 22:00:00 PM
7 days a Week(in should consider Saturday and Sunday also)
regards
Ravi GH
hi team,
i need to caclculate the tat between 2 times condition is.
Supoprt Hrs: 7:00:00 AM 22:00:00 PM
7 days a Week(in should consider Saturday and Sunday also)
regards
Ravi GH
Hi
Try this
=INT(B1-A1)*15+MOD(B1-A1,1)*24
where A1 is the first date and B1 the second date.
hi thanks for ur time but its not excluding the off business hrs
ex. strt time 3/13/2016 6:00:00 PM
end time 3/14/2016 8:00:00 AM
manually if i calculate its 2 hrs but as per your formula its 14 hrs
support hrs is only 7:00 am to 10:00 pm.
Try
Formula:
Please Login or Register to view this content.
Hi Ravi,
Try following links where you such queries are already resolved..
HTML Code:HTML Code:HTML Code:
Thanks,
Anil Dhawan
Click *Add Reputation to say "Thanks" and don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) if you are satisfied.
Don't stop when you are tired. STOP when you are done!
Hi Jose,Originally Posted by José Augusto
Try
Formula:Please Login or Register to view this content.
Will it work when you have "03/13/2016 8:00 AM" in A1 and "03/14/2016 8:00 AM"? Because with the provided solution, it will return 30 instead of 15..
hi IF both dat and time are same it should give 0 but its giving 15
Hi
Sorry for my mistake
try the following (where 9 is the number of hours between 22:00 and 7:00)
=(B1-A1)*24-(MOD(A1,1)>MOD(B1,1))*9*(INT(B1)-INT(A1))
hi
start time: 3/13/2016 11:00:00 PM
end time: 3/14/2016 10:00:00 AM
as per this example the result should be 3 Hrs but as per ur formula its 2hrs
support window is 7:00 am to 10:00 PM
Use
=(B4-A4)*24-(MOD(A4,1)>MOD(B4,1))*9*(INT(B4)-INT(A4))+(OR(MOD(A4,1)>22/24,MOD(A4,1)<7/24)*(MOD(A4,1)-22/24)*24)
to correct the start time in the range 22:00 to 07:00.
End time is possible in that range?
Last edited by José Augusto; 03-17-2016 at 08:21 AM.
sorry still am facing some challenge
3/13/2016 7:00:00 AM
3/13/2016 7:00:00 PM
but ur formula gives -3
also if i give same day
3/13/2016 7:00:00 AM
3/13/2016 10:00:00 PM
it gives 0
hi team can some one help on this...
hi any thanks for this but all those link talk about excluding Saturday and Sunday but my requirement is 7 days a week and support hrs should be 7:00 am to
10:00 pm
How about:
Please Login or Register to view this content.
Quang PT
hi je suit thank you i taught it worked almost but i see few correction need to be done for the below dates its giving 0:00 any idea why..?
these are few sample but i found this for many.
02/29/2016 08:00:00 AM 02/29/2016 01:00:00 PM 0:00
02/29/2016 08:44:04 AM 02/29/2016 05:17:29 PM 0:00
02/29/2016 09:13:37 AM 02/29/2016 04:53:35 PM 0:00
02/29/2016 09:52:48 AM 02/29/2016 05:14:34 PM 0:00
02/29/2016 10:03:25 AM 02/29/2016 06:04:59 PM 0:00
03/01/2016 07:12:12 AM 03/01/2016 01:46:02 PM 0:00
03/01/2016 08:29:08 AM 03/01/2016 05:12:54 PM 0:00
03/01/2016 08:39:03 AM 03/01/2016 03:24:01 PM 0:00
You can use this formula:
=(INT(B1)-INT(A1))*($E$1-$D$1)+MIN(MAX($D$1,MOD(B1,1)),$E$1)-MIN(MAX($D$1,MOD(A1,1)),$E$1)
Cell D1: = 7:00 AM
Cell E1: = 10:00 PM
Last edited by Phuocam; 03-18-2016 at 07:43 PM.
It works for me.
Thanks this works fine,,,
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks