Time Difference Calculation

[pramodagroiya]

Hi,

I have excel sheet, wherein we have ticket raise date and time & ticket close date and time. i need to calculate the time difference if the ticket is closed with 4 hrs to TAT or not. also if the ticket is created after 2:30 PM then the time calculation will be till 6:30 for TAT and rest of the TAT will start next day after 9:30 AM. need to exclude saturday and sunday.

sample file is like this:

Ticket Number	Date	Help Topic	Source	Current Status	Last Updated
158635	22-02-2016 09:32	Featured Static Listing	Phone	Closed	22-02-2016 12:32

<tbody>
</tbody>

 

[jorismoerings]

Hi and welcome to MrExcel,

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Ticket Number	Date	Help Topic	Source	Current Status	Last Updated	TAT	On Time		Param
2	158635	22-2-2016 09:32	Featured Static Listing	Phone	Closed	22-2-2016 12:32	22-2-2016 13:32	TRUE		Start	09:30
3										End	18:30
4										TAT	04:00
Sheet1 (5)

Cell Formulas
Range	Formula
G2	=WORKDAY(B2,CEILING((K4+MOD(B2,1)-K2)/(K3-K2),1)-1,holidays)+MOD(B2,1)+K4-CEILING(MOD(B2,1)+K4-K2,K3-K2)+K3-K2
H2	=F2

Named Ranges
Name	Refers To	Cells
'Sheet1 (5)'!holidays	='Sheet1 (5)'!$A$6:$A$12

 

[pramodagroiya]

Hi and welcome to MrExcel,

Try this:

	A	B	C	D	E	F	G	H	I	J	K
1	Ticket Number	Date	Help Topic	Source	Current Status	Last Updated	TAT	On Time		Param
2	158635	22-2-2016 09:32	Featured Static Listing	Phone	Closed	22-2-2016 12:32	22-2-2016 13:32	TRUE		Start	09:30
3										End	18:30
4										TAT	04:00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1 (5)​

Worksheet Formulas Cell Formula G2 =WORKDAY(B2,CEILING((K4+MOD(B2,1)-K2)/(K3-K2),1)-1,holidays)+MOD(B2,1)+K4-CEILING(MOD(B2,1)+K4-K2,K3-K2)+K3-K2 H2 =F2<G2 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Worksheet Defined Names Name Refers To 'Sheet1 (5)'!holidays ='Sheet1 (5)'!$A$6:$A$12 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

HI,

Can you please share the excel

 

[identifyaction]

New forum user, experienced excel user and am quite baffled at the moment with a similar problem. We need to calculate date & time differences, simply subtract end time from beginning time as per below sample data.

My colleagues Excel 2011 (I also have the same on my computer) will not calculate TIME data as per below sample data. It keeps coming up with the VALUE error message. It works fine on mine (also excel 2011) and other PC computers. We have done all the common formatting changes, text to columns (but we need the date too) etc. If she enters a simple time value, i.e. 1:08, 2:05 the calculation works fine. Help....




CONTAINER_No LAST_PALLET_SCAN_DATETIME CONTAINER_SEALED_DATETIME Time Difference
BSIU9591920 24/02/2016 01:08:55.393 24/02/2016 02:05:43.446 0:56 (=C2-B2)

 

[jorismoerings]

@pramodagroiya, here you go https://app.box.com/s/e1nms5okxbbbuh2lryz1f4o9j5uxr5ed

@ identifyaction, Your Microsoft Windows regional settings will influence how your date/time, numeric, and currency data types appear when you apply formatting options. Windows supports a variety of languages, plus currency and date/time formats for the countries/regions that use those languages. In this case (the format of miliseconds in excel - is in fact a decimal of the seconds) it's dependable on how the settings supports decimals. Check your regional settings and customize the time format so it will be in line with the settings.

I've included a sample based on your sample in the same file as i created for pramodagroiya


Hope this helps.

Please be aware that this is an exemption of me answering this question because your question has nothing to do with the original question. If you want valued responses from the forum it's better to create your own thread. With that you help us help you which ultimately creates a better and more valued response.

 

[pramodagroiya]

Hi,

Thanks for support, but in given excel its not calculating properly if ticket is raised before 9:30.

E.g: TAT should be calculate even if ticket is raised before 9:30 and after 6:30. in given formula its calculating if ticket is raised at 9:00AM also, ideally it should calculate from 9:30.

please help is correcting.

Thanks & Regards,
Pramod Kumar

 

[pramodagroiya]

Hi,

Please help in creating this.

Thanks for support, but in given excel its not calculating properly if ticket is raised before 9:30.

E.g: TAT should be calculate even if ticket is raised before 9:30 and after 6:30. in given formula its calculating if ticket is raised at 9:00AM also, ideally it should calculate from 9:30.